December 15, 2011 at 5:55 pm
I have a ssis package, in it I first import data from a flat file source, then convert some columns using derived columns, I want to use the derived columns, plus another column from another table B in SQL server database to poulate the target table A
Of course these two tables have the same key, but what is the best approach to do it in next step, using what task? The columns in the other table may have empty values, I also want to import all the rows in the flat file to table A.
December 16, 2011 at 12:18 am
There are multiple options:
* you can use the MERGE JOIN to join the two datasets together. The input needs to be sorted, so if your flat file has a lot of rows this can become problematic
* you can use the LOOKUP component to get the columns from the table. This would be an easy solution as you mentioned both tables have the same key. It will also have good performance if you use the full cache option. (if your table isn't too big)
* you can stage the data from the flat file in a SQL Server staging table and do the join in SQL Server using (INNER) JOIN.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 16, 2011 at 9:17 am
Koen Verbeeck (12/16/2011)
There are multiple options:* you can use the MERGE JOIN to join the two datasets together. The input needs to be sorted, so if your flat file has a lot of rows this can become problematic
* you can use the LOOKUP component to get the columns from the table. This would be an easy solution as you mentioned both tables have the same key. It will also have good performance if you use the full cache option. (if your table isn't too big)
* you can stage the data from the flat file in a SQL Server staging table and do the join in SQL Server using (INNER) JOIN.
Thank you, these are all good solutions. For number 2 - Lookup, can I still use it in following case,
the table have the same keys, but the additional column I want in the other table may have blank values, and I want all the row from my flat file rows to be in the target table.
But I see if I use lookup , it has two cases, one is lookup match rows, the other is lookup no match rows
December 18, 2011 at 11:31 pm
sqlfriends (12/16/2011)
Koen Verbeeck (12/16/2011)
There are multiple options:* you can use the MERGE JOIN to join the two datasets together. The input needs to be sorted, so if your flat file has a lot of rows this can become problematic
* you can use the LOOKUP component to get the columns from the table. This would be an easy solution as you mentioned both tables have the same key. It will also have good performance if you use the full cache option. (if your table isn't too big)
* you can stage the data from the flat file in a SQL Server staging table and do the join in SQL Server using (INNER) JOIN.
Thank you, these are all good solutions. For number 2 - Lookup, can I still use it in following case,
the table have the same keys, but the additional column I want in the other table may have blank values, and I want all the row from my flat file rows to be in the target table.
But I see if I use lookup , it has two cases, one is lookup match rows, the other is lookup no match rows
So you have 3 tables, right?
One source, the flat file.
One reference table, which has one column you need but it may contain blank values.
One destination/target table.
You want all the rows of the flat file to end up in the target.
The dataflow should look like this:
one flat file source connected to a lookup component. The lookup component is configured with a SQL query that retrieves the key column and the column that you need. You match on the key column and retrieve the other column.
Connect the No Match Output to a derived column transformation and have an expression for the column you need that says "Value Not Found". Link this with a Union All to the Match Output and write everything to the OLE DB Destination.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 21, 2011 at 10:28 am
Thank you very much, this is very helpful.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply