September 7, 2011 at 10:07 am
Hi All,
Need to import data from fixed width files(Fil1,Fil2 and Fil3) into corresponding tables(Tabl1A ,TablB and TablC ) .
Reference number exists
Tabl1A relation with TablB (one - many)
Tabl1A relation with TablC (one - many)
->First i need to import Fil1 to SSIS object/table
->Loop the table
.Insert 1 row of Fil1 into Tabl1
. Generated ID (identity) will be taken and corresponding Reference rows from FIl2 and Fil3 will be inserted into TablB and TablC
Please suggest me how to do this.
September 7, 2011 at 10:23 am
1. Use the SQL Server import wizard to import all three tables into staging tables. Don't be too fussy about data types.
2. To populate table1, write an INSERT INTO...SELECT ... FROM statement. Include any remaining datatype conversions here. If the old key isn't retained in the new table, include an OUPUT clause, streaming the new key and the old key into a local temporary table.
3. Populate tables 2 and 3, using their respective staging tables (LEFT) JOINed to your shiny new table1 (or the temp table) on their original keys.
Retain the staging tables until you're confident that the migration meets the terms of the spec.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 7, 2011 at 11:24 am
What I'd recommend you do is have three data flow tasks.
In the first data flow task, you load Table1A. Use the Flat File Source as your input, and point it to Fil1. Use a OLE DB Destination as your output, and output the data to Table1A.
This will cause a bulk load to be done against Fil1, and then a bulk insert to be done into Table1A, both very fast operations.
Connect this data flow task to another data flow task. In this one, you're again using a Flat File Source as your input, and pointing it to Fil2. Then connect this to a Lookup. The lookup will be populated from Table1A. Link the rows together using the reference that exists, and grab the identity field from Table1A as your output.
Now, important, make sure that you go to the "Configure Error Output" screen, and select "Ignore Failure" as the action.
Link this lookup to an OLE DB Destination, and output the data to Table2.
Repeat for Table3.
The rows which fail to find a lookup will cause a NULL value to be outputted for the field corresponding to the identity of table1A.
September 7, 2011 at 11:27 am
Thanks kramaswamy...
Could you please give a little bit more info on following statement in-brief
Connect this data flow task to another data flow task. In this one, you're again using a Flat File Source as your input, and pointing it to Fil2. Then connect this to a Lookup. The lookup will be populated from Table1A. Link the rows together using the reference that exists, and grab the identity field from Table1A as your output.
September 7, 2011 at 12:11 pm
The Lookup task basically loads the entire content of a procedure or a table into memory, and then performs lookups against that table for each row that you are passing in to it.
If I understand your requirements correctly, your Table2 and Table3 definitions contain one column which is something like Table1ID.
I assume that you also have some piece of information in Fil2 and Fil3 which lets you connect them to the data contained in Fil1.
For example, in Fil2, you might have a field called "ClientNumber", and in Fil1 you also have a field called "ClientNumber".
For your lookup, you'll have something like: "SELECT ID, ClientNumber FROM Table1"
Then, in the tab where you specify the link between the two, you would attach ClientNumber from Fil2 to ClientNumber from Table1, and choose ID as the column to obtain.
When you output this ID to Table2, it will contain either the ID from Table1, or a NULL value when the lookup failed.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply