June 17, 2008 at 9:19 am
Hello all,
I'm looking to import data from a flat file and not all columns are going to the same table within the db. How can I split the columns and send them to the appropriate tables in the db. I'm still pretty new to SQL 2K5 and the tools available to SSIS however, I know there is an extreme amount of possibilities with SSIS.
Any help would be much appreciated
Thanks,
Z
June 17, 2008 at 9:37 am
That's a pretty general question. Some detail would help.
In SSIS, you can use a Multicast component to duplicate your data flow if you need some data to go to another destination. You can also use an OLEDB Command component in a data flow to insert a record and then continue down-stream to a destination component. Also a script component can have several inputs and outputs.
You also have the option of staging your data and using T-SQL to complete the process of cutting it into the appropriate pieces and inserting into the destination tables.
It is going to depend on exactly what you are doing, if you have identity or GUID columns and foreign key relationships, and how much you need to transform the data.
June 17, 2008 at 9:47 am
Gotcha, well I have a flat file that has ten columns in it when connected by a connection manager. Let's say columns 0, 1, 2, 5, and 8 are going to one table. Then Columns 3 and 4 are going to another table. Columns 6 and 7 to another table and so on. I have a flat file connection to the source. What I need to do is split these columns out and insert them or update them to their appropriate db tables. Forgive me if I'm restating the same thing was trying to add the detail. The script component will allow me to split columns out? Will I need multiple OLE DB connections to the appropriate tables? I keep tinkering with it too.
Thanks for the input and help
Sincerely,
Z
June 17, 2008 at 9:53 am
Ok, so it sounds like you have one source (flat file) and three destination tables. For each row in the source, you want one row in each destination table. The destination tables do not have identity columns being used to foreign key to each other.
In this case, use a multicast in your data flow. This will essentially make as many copies of the source data as you need. From there you can connect the multi-cast to each of your OLEDB destination components for the inserts. This will work fine as long as the order the records are inserted does not matter (meaning you do not care if the record in TableA is inserted after the record in TableB).
June 17, 2008 at 10:08 am
10004 |Howard |Leah |DC |Washington D.C. |2806 |GS |lmhoward |leah.howard@bakerd.com |lmhoward |\\Dcfsps1\Vol3\Home\lmhoward\myscans
Here is one row from the flat file source. This entire row has information that goes to different tables within the database. There are three flat file sources. This sample is from one and is the one with multiple destinations within the db. Just making sure we are on the same page. The Multi-cast will allow me to redirect the pieces of the rows I want to insert or update into their respective tables? If the record exists then I want to update the record if doesn't exist I need to insert the record. I believe there are FK reltions among two or three of the four tables for this particular flat file.
June 17, 2008 at 10:26 am
If you have FK relationships between the destination tbales, you will probably have a lot of trouble with a single data flow and a multicast. The problem will be that you have no control over the order of the inserts so the child record could be inserted before the parent. Also, if you used identity columns on the tables, you have no way to retrieve the identity value from one stream and use it in another without using subsequent merge joins - this may be a bit advanced for you.
I would suggest you load it into a staging table and write a stored procedure to handle the inserts and updates in T-SQL.
June 17, 2008 at 10:36 am
Okay sounds good. How might I go about taking the staging table route? Never had this many flat files to import and to multiple tables.
Thanks for all the input
Z
February 23, 2009 at 1:56 am
I have a similar problem
I got 1 flat file source which columns need to be split out over diffrent table & then referenced to each other, these tables don't have fk on them but i would like to avoid multicast since it already goes over an enormousness amount of records
March 17, 2011 at 2:20 pm
Import it into a staging table, use call a SP to distribute the data out and link up fks, drop or truncate staging table.
March 17, 2011 at 2:28 pm
Txn for an answer but very late, already switched jobs twice since then.
March 17, 2011 at 2:30 pm
good call, always a good idea to check the date before posting.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply