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

Improve this question
4
  • 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, 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.
    – user3752281
    Dec 18, 2014 at 9:23
  • The lookup function should do exactly what you need if I'm parsing your question correctly.
    – Zane
    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
    – user3752281
    Dec 21, 2014 at 21:32

1 Answer 1

Reset to default
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
Improve this answer
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.
    – user3752281
    Dec 17, 2014 at 17:44

Your Answer

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

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