HELLO FRIENDS, I HAVE AN ISSUE I have a flatfile data which to be loaded into table. I am using ssis. The flatfile does't have any commas . they have empty spaces between them. like Belleville MIMICHIGAN 60010 20080109 when i am trying to load its coming as only 1 column. HOW CAN I LOAD THIS KIND OF DATA INTO MY TABLE USING SSIS. Please help me out with this. thanks

Your file should be fixed width. In that case use the Ragged right format in the source and save it into one column and then use derived column to parse tha file and load the data into table.

do check this link. http://www.bimonkey.com/2009/05/the-flat-file-connection-manager-and-source/

Options: 1) Create a custom source with a Script Component (like "SQL Insane" mentioned) 2) Use a derived column to split 1 column into several new columns with a findstring and a substring expression 3) Use a Script Component (type: transformation) to split the columns with vb.net or C# (String.Split()) 4) And if using only once: open in notepad(++) and replace a spaces by a comma/(semi)column/pipe/etc. (or ask your file provider to do it for you) Answer was indeed simpler than expected: Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

No scripts necessary, and I don't think fixed width will work for you. Change your column delimiter to a space in the Flat File Connection Manager. Set up your connection manager as normal. On the second page (Columns), go to the Column Delimiter property, and enter a space. Press Refresh. Talk to me now on

@ TODD McDermid thanks for your post. It was helpful .