export comma delimited data from one source file to two destination tables

Question

Post reply

export comma delimited data from one source file to two destination tables

bmr270

Default port

Points: 1477
More actions
September 4, 2008 at 12:05 pm

#382522

I have to export comma delimited data from one source file to two destination tables.
Example
Data in source file
Male,mike,nj,usa(4 columns)
Female,nansy,8(3 columns)
Table structures
Destination 1) tblMale
Sex char(4)
Name varchar(20)
State char(2)
Country char(3)
Destination 2) tblFemale
Sex char(4)
Name varchar(20)
Age bigint
Output in table “tblMale”
Male,mike,nj,usa
Output in table “tblFemale”
Female,nansy,8
Thanks
BMR

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Will Riley Ten Centuries Points: 1030 More actions · Answer 1

As with most things SSIS, there's more than one way (probably) to skin this cat 😉

Here's something that worked for me though:

1. Flat File Source

The key here is to set the format in the data source to "Ragged Right" - this will effectively deal with the fact that some rows have 3 cols and some 4

Effectively, the output from this will be one column rows as per the source file, having got this we can go to work

2. From the output, add a Conditional Split

Set up the Male Output formula to be SUBSTRING([Column 0],1,4) == "Male" (from your post, the rest by default must be Female

So now you have two datasets, one male & one female.

What I did next was to split the single column of both datasets in to 3 and 4 column datasets as per your description

i.e. for the males

3.Add a Derived Column Transformation

Add four new columns to this with the following formulas to split the string

Sex .... formula is SUBSTRING([Column 0],1,FINDSTRING([Column 0],",",1) - 1)

Name.....SUBSTRING([Column 0],FINDSTRING([Column 0],",",1) + 1,(FINDSTRING([Column 0],",",2) - FINDSTRING([Column 0],",",1) - 1))

State....SUBSTRING([Column 0],FINDSTRING([Column 0],",",2) + 1,(FINDSTRING([Column 0],",",3) - FINDSTRING([Column 0],",",2)) - 1)

Country...SUBSTRING([Column 0],FINDSTRING([Column 0],",",3) + 1,LEN([Column 0]))

Do the same sort of thing for females... then all you probably have to do is to do some unicode to non unicode data conversions and you're away....

Probably more elegant solutions, and you could code it in a script, but this does work and illustrates a few of the data flow components.... 🙂

Kind Regards, Will

bmr270 Default port Points: 1477 More actions · Answer 2

Thanks for reply.

I am doing this in DTS. Will it work in DTS?

Thanks again

BMR

Will Riley Ten Centuries Points: 1030 More actions · Answer 3

Hmmm... Well this forum is for SQL 2005.... Since when is DTS part of SQL 2005 Business Intelligence ? Try posting in a DTS forum 😉

Kind Regards, Will