April 28, 2008 at 3:25 pm
I am moving a process that was once scripted in Access of all things over to SSIS.
I have successfully created a package that loops through records in my table which contains the raw text file location, flushes the holding table, imports the data, and then pushes the data into the live table.
However the raw data format is a bit of a pain in that there is one large char field that can contain multiple values that I would like to seperate out. The data is stored in pairs of Label:Value with semi colon delimiters, but the labels can be different from record to record. I just had a function in Access to interpret the data and decide which of the real columns in my destination table the value should go into.
What is the best way to replicate this in SSIS? I don't mind particulary if I have to import all the data and then loop round it (with recordset type operation?) and do the chopping up. Obviously if you can interpret the data on the way in then all the better. Speed isn't as important as flexibility / accuracy though as the process will run in the early hours.
Any advice greatly appreciated.
EDIT : Have been doing some reading on the script component - does this sound like the best way of doing it? Presume I would need to create multiple transformation outputs for each possible column that I might want to populate from chopping up the original string?
Chris
April 29, 2008 at 6:32 am
No problem - I discovered the delights of the Script Component after being crafty and searching inside one of the Wrox books on Amazon!!
Have ordered the Pro and Expert books after seeing recommendations on this forum as already I can see solving one problem just opens up another world of possibilities. I can see myself recoding all my existing SSIS packages now!
Thanks anyone who looked.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply