Data Flow Task question

Hi there. I'm trying to learn SSIS, please, help me. I have 2 questions:

1)
There are 2 databases on 2 different servers. I need to get data from Table1(database1) and put it to Table2(database2). But I have to insert rows, which ID is not exists in Table2. How Can I do necessary filter?

2)
In the OLE DB DataSource Component I have used SQL Command(it's simplified):

declare @.TmpTable TABLE (WorkCode int not null);

INSERT INTO @.TmpTable (WorkCode)
select WorkCode
from Table1

SELECT WorkCode
FROM @.TmpTable

SSIS Package works without any exception. But there is no any inserted record in destination table. If I try similar query without temporary table - it works good. Why?

1 - Create OLE DB source to 1st (source) database.
- Add a lookup transformation to select key from 2nd database on 2nd server. In that lookup join the key coming from the 1st database to the key in the 2nd
- Hook the error output (red arrow) from the lookup to an OLE DB destination which points to the 2nd database. This will insert records not found in the 2nd database.

2 - Try setting the RetainSameConnection property of the connection manager to true and see what happens.|||The first task works! Thanks! But the second is not. Any other ideas?
|||

Aliaksander Hmyrak wrote:

The first task works! Thanks! But the second is not. Any other ideas?

Good deal.

As for number 2, why are you using temp tables? 2 things - you can just write the query that inserts into the temp table as the source for the data flow. OR you could use yet another, initial, data flow to populate a SQL server table with the results you need in the 2nd data flow (the one you've already got written). After the two data flows, you could write an Execute SQL task in the control flow to truncate that "temporary" table.|||

At the start of your SQL statement add

Code Snippet

SET NOCOUNT ON

|||Thanks a lot !! Both methods work!