Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vasu4us
Posting Yak Master
102 Posts |
 Posted - 2007-09-06 : 09:18:30
|
| I am migrating DTS packages to SSIS (recreating all the logic).I have a Data Driven Query task in DTS with Source query - select x,y from table1 (from database db1)Binding - table2 which contains columns which match table1 x,y (fron database db2)Transformation - maping from source table1 x,y to Binding table2 x,yQueries - type update update table2 set x=? where y=?I know that there is no similar task in SSIS,can someone tell me how to replicate this in SSISThanks in Adv |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-09-06 : 10:09:34
|
| Didn't often use the data driven query task in DTS, but from what I remember, an OLEDB Command transform should perform exactly this function. Alternatively, if it's a lot of data, you could write the results of the source query to a staging table, and refer to this in a subsequent Execute SQL task to perform the update. Thinking about it, this would probably have been the more common approach in DTS.Mark |
 |
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2007-09-06 : 13:46:20
|
| As Mark suggested you can try with OLEDB Command, other option could be using lookup transformation.ThanksKarunakaran |
|
|
|
vasu4us
Posting Yak Master
102 Posts |
Posted - 2007-09-06 : 13:59:29
|
| Thanks Mark/Karuna, I was trying the OLEDB command transformation. Its little confusing iam not able to figure out how to use it.I have a OLEDB source which is extracting data from multiple joins, one of the output is X_KEY which iam populationg as null, there is another column X_ID.I am trying to match X_ID from the result of OLEDB source output with another table in a different database and populate X_KEY from that table.If iam using OLE DB command or SCD what should be my Connectionn manager and whaat should be the query in the Component property.I really wasted 1day on this and still no success. |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-09-07 : 04:37:09
|
Agreed, the OLEDB Command is not the most intuitive thing ever! You need to create a new connection manager for this (if you don't already have one pointing at database db2). You then need a query along the lines of: UPDATE table2 SET x=? WHERE x=? AND y=? You then need to map these parameters to relevant columns returned by your source query.Mark |
|
|
|
vasu4us
Posting Yak Master
102 Posts |
Posted - 2007-09-07 : 10:29:54
|
| tell me if iam doing it wrongI have one data flow task which would just extract and load the the table with all the columns - column x is null here and column y is populated with IDI have added another data flow task which would query the table --- select x,y from table1 (from database db1)here i have used a OLEDB Command which would connect to DB2 (to access the table which is loaded in the previous dataflow)the SQL Command in this is for the update --- UPDATE table2 SET x=? WHERE y=?. In the Column Mapping tabl now i get just 2 columns from table2 and 2 parameters mapped to them.and it is population the table with all the IDs |
|
|
|
|
|
|
|
|
Replicate Data driven query task(DTS) in SSIS