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

  • To restate, you need to populate a column based on whether a reference record was found? If exists, then 1, else 2
    – billinkc
    Dec 17 '14 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. Dec 18 '14 at 9:23
  • The lookup function should do exactly what you need if I'm parsing your question correctly.
    – Zane
    Dec 19 '14 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 Dec 21 '14 at 21:32

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
  • 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. Dec 17 '14 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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