Loading text file using SSIS

Question

I am a SQL Developer.I was assigned to load a txt file using SSIS.

The text file has records like :

INS*Y*~DMG*20141211~DTP*348*20150401~DTP*349*20150430~INS*Y*~DMG*19611116~DTP*348*20150401~DTP*349*20150130~

The row delimeter is INS, the column delimiter is ~ (tilde).

The destination table has to be like this:

STATUS   DOB       STATING_DATE ENDING_DATE
Y       20141211   20150401     20150430
Y       19611116   20150401     20150130

I created a destination table in SSMS with the four columns. Then created a SSIS package with flat file source and SQL server destination.

But the result I am getting is only one column. I couldn't seperate the columns.

Can anyone please help me to solve this issue?

have you used advanced ColumnDelimiter option in SQL Server Import wizard? — pedram
I see only column (column 0)in the advanced column delimeter option. Do I need to set column delimeter for each column. — soba
Hi rajeshmpanchal, Thanks for your reply. yes I added three more columns and set the delimeters for each column. But now my result looks like this Y DMG*20141211 DTP*349*20150430 .How do i remove the characters before *. — soba
@rajeshmpanchal Do I need to load the flat file into staging table before processing them into destination table. — soba
yes, for that you can use staging table. and then using in built function like LTrim, RTrim or Replace function you can achieve this. btw if answer is helpful then please flag it as answer. thanks — pedram

pedram pedram · Accepted Answer · 2015-10-09T04:58:23

1

votes

Have your tried something like this,