How to import in special character delimited text file by using SSIS ?

Hi,

I would like to know how to import in the custom delimited text file by using SSIS.

For example, instead by using tab or comma delimited, I use this character : '?'

The reason is the delimited format that SSIS provided is too common such as colon, semi colon, tab, comma and pipeline.

I have the data that the user also key in the pipeline there. So I am thinking to separate the field by using this special character, but cannot see if there is anyway to import in by using SSIS.

Please help to share the solution on this :

A?B?C
1?2?3

thanks

best regards,

Tanipar

You can import this as a single column and then use a script transform to cycle through your row and break it down into the appropriate columns. There are various examples of doing this relating to uneven / unbalanced / dynamic number of columns...

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/07/handling-flat-files-with-varying-numbers-of-columns.aspx

|||I am a fan of the approach above , but you can also just use a flat file connection manager. Go to the Columns page in the editor, and put the symbol into the Column Delimiter field. That works fine for me. The approach above is usually only necessary when dealing with flat files with missing columns or delimiters.|||Learn something new every day... I figured since it was a drop down that you could only use the values present, I didn't realize you could type there... Let's just say this approach is MUCH easier :-)

How to import in special character delimited text file by using SSIS ?

Hi,

I would like to know how to import in the custom delimited text file by using SSIS.

For example, instead by using tab or comma delimited, I use this character : '?'

The reason is the delimited format that SSIS provided is too common such as colon, semi colon, tab, comma and pipeline.

I have the data that the user also key in the pipeline there. So I am thinking to separate the field by using this special character, but cannot see if there is anyway to import in by using SSIS.

Please help to share the solution on this :

A?B?C
1?2?3

thanks

best regards,

Tanipar

You can import this as a single column and then use a script transform to cycle through your row and break it down into the appropriate columns. There are various examples of doing this relating to uneven / unbalanced / dynamic number of columns...

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/07/handling-flat-files-with-varying-numbers-of-columns.aspx

|||I am a fan of the approach above , but you can also just use a flat file connection manager. Go to the Columns page in the editor, and put the symbol into the Column Delimiter field. That works fine for me. The approach above is usually only necessary when dealing with flat files with missing columns or delimiters.|||Learn something new every day... I figured since it was a drop down that you could only use the values present, I didn't realize you could type there... Let's just say this approach is MUCH easier :-)

How to import a text file with transac-sql

Hi allI am looking for examples of scripts that will help me doing these things:

- import a text file delimited with the character "*", representing a new month of data, for example data from march 2007

- create a new table with the structure of an existing one to import the data, for example Data_March_2007

- alter an existing totals table adding a new column for the new moth imported, adding a new colum for the month of March 2007.

This looks more like a job for DTS (SQL2000) or SSIS (SQL2005) than a pure TSQL job.