0

In a SSIS package , I need some sort of condition that include two connections.

The simplified problem is: A simple CSV import to SQL Server, DB1, table 1( col1, col2 ). I need to populate table 1 > col2 = '1' if col1 is found in another database , DB2 > table 2 ; db2 is specified as a different connection in package.

and table 1 > col2 = '2' if col1 is not found in the lookup database.

any guidance is appreciated, ta

4
  • To restate, you need to populate a column based on whether a reference record was found? If exists, then 1, else 2
    – billinkc
    Commented Dec 17, 2014 at 18:47
  • yes, that is the point, just that the reffernce column is located in another db, reffernced by a connection other than the connection I need to do the update. Commented Dec 18, 2014 at 9:23
  • The lookup function should do exactly what you need if I'm parsing your question correctly.
    – Zane
    Commented Dec 19, 2014 at 19:11
  • yes, it did exactly what I needed in terms of results; in terms of performance it is a little bit exaustive, I think, I find it exaustive to lookup after every record in the data set, but it works. ta Commented Dec 21, 2014 at 21:32

1 Answer 1

0

Put the CSV import in one data flow, then in a dependent data flow use an OLE DB Source that contains the results you need as the output of a SQL query. Feed that into an OLEDB destination.

Your query would look similar to:

SELECT t1.col1, 
CASE WHEN EXISTS (SELECT null FROM db2.dbo.table2 t2 WHERE t2.col1 = t1.col1) THEN '2'
ELSE '1' END as [col2]
FROM db1.dbo.table1 t1
1
  • thank you, I was not sure how to use both db1 and db2, in the same query, to which two databases I reffer with two separate connections; I managed to resolve it using a Lookup Transformation. Commented Dec 17, 2014 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.