CODEWITHSUNDEEP
sql-servert-sqlssis
DenStudent
DenStudent

Reputation: 928

SSIS - truncate table when other table contains data with certain value

I have 2 tables on 2 different databases.

When Table A from Database A contains data and a certain value is written in a specific column, then I need to truncate table B on database B and copy data from table A to table B.

So, When table A looks like:

ID | column2
1  | null
2  | null
3  | null

then we don't do anything.

If table A looks like 

ID | column2
1  | value
2  | value
3  | value

then Table B gets truncated and the data from table A gets copied to table B.

I tried this with a Execute SQL task, but that only works for when all tables are from the same database.

Any sugestions?

Upvotes: 3

Views: 945

Answers (1)

iamdave
iamdave

Reputation: 12243

If you are trying to do this within SSIS, run an Execute SQL Task in your Control Flow that does a count of the records in Table A that meet your criteria and sets a variable to either true if there are or false if there aren't any.

Create a connection from your Execute SQL Task to however you want to execute your truncate and insert statements. Right click on the arrow and select Edit... to set the Condition that if the variable you just set is True or False to either follow that path or not.

Create a connection to the next step in your Control Flow if there are any, and change the LogicalAnd property of this connection to false. You should see the arrow change to demostrate the different precedence behaviour. Create another connection from your original Execute SQL Task to this next step in your control flow. This arrow should also look different.

What will now happen when you run this package is that the variable will be set with either true or false on whether or not to truncate and insert and then follow the correct path in your Control Flow. Once either of these paths hits the next steps (Of which only one should ever execute), your package will continue as normal.

Upvotes: 3

Related Questions