SSIS: Loading in a flat data file, parsing, sending data to SQL tables

To apply changes to existing data, you need to use derived column transformations along with the data flow task. More info here: http://msdn.microsoft.com/en-us/library/ms137630.aspx and here : http://www.ssistalk.com/2007/01/23/derived-column/

Another useful article at EE that uses the script component for transformation : https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_2886-How-to-use-Script-Component-as-Asynchronous-Transformation.html

You can use script in link 1 to parse out data and then all you would need to do is map it to destination columns in OLE DB destination


http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx
https://www.experts-exchange.com/questions/26428492/SSIS-Importing-CSV-file.html?cid=1131&anchorAnswerId=33522969#a33522969

I generally import the data into a staging table that has only VarChar columns.  That lets me deal with squirrelly data as well as creating INSERTs to move the data where I please after it is scrubbed.

when you want to import dynamic data into sql server, BCP is one of the best options. look at samples of BCP here:
http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

vdr1620: the link you posted about pulling in csv files with varying columns was exactly what I was looking for. At this point now that the data is loading, I'm trying to figure out how to push subsets of that data into a sql table.

For example, a row may contain a customer number, an item ID and then data pertaining to hourly use of that item, so I'd be splitting that row into 3 tables. Its at this point I'm not sure how to process that data, do I use tools within SSIS to do this or program it all in a script task.

wppiexperts,
Are you loading the data into a staging table or are you trying to do this on the fly, so to speak?
If you are using a staging table, then all you have to do is figure out waht columns go in which tables and create queries (or stored procs) to accomplish that.  You can then include those, via Execute SQL Tasks, in your SSIS package.
If you are doing it on the fly, then you are probably using a FOR EACH loop and handling the data RBAR (Row By Agonizing Row), which is absolutely THE worst way to do it, and you would still be using Execute SQL Tasks to accomplich the actual work.

So, According to what you have said.. You have a Flat File Source and Script task connected... You will need a MultiCast Transformation and then 3 OLE DB destinations one for each tables..Connect the green Arrow from Multicast to each of the destinations and map the required column accordingly

came across this article which expands a bit on the link provided by vdr1620:
http://sqlblog.com/blogs/andy_leonard/archive/2010/05/18/ssis-design-pattern-loading-variable-length-rows.aspx

so, now on to multicast!

Thats a nice Reference and might be suitable to what you are trying to do by avoiding multi task transformation as i suggested before..you can just create Multiple Outputs using Script task and map them to OLE DB Destinations directly

OK - so I'm at the point where I have transformed the flat file data and I can load it into the sql tables. My next task is to take anwhere from 24-50 columns from the flat file and pivot them into rows of data. What tool in SSIS would be best suited for that....again...anyone have examples they can point me to?