We help IT Professionals succeed at work.

SSIS Package SQL 2008 - If Exists

SeTech
SeTech asked
on
I have two tables A & B. Table A contains updated data that I want to move to B. However if there is no data in table A then I do not want to leave the existing data in table B.

I have this set up in a SSIS package and want to use a SQL Task to first find out if there exists data in Table A. I have tried a few statements but continue to get errors.

Logically I want to:
1. Check to see if Table A has data (If exists)
2. If 1 is yes, then Truncate Table B and insert new data
3. If 1 is no, then do nothing

I have tried a number of statements but only get errors.
Any assistance would be appreciated.

Comment
Watch Question

Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
IF EXISTS(SELECT TOP (1) * FROM TableA)
BEGIN
    TRUNCATE TABLE TableB
    INSERT INTO TableB (...)
    SELECT ...
    FROM TableA
END --IF
SThayaTechnical MAnager

Commented:
Can you try the below Steps :

1.Create a vriable name Like Count
2. select a ExecuteSQLtask in the control flow and set the below properties
   i) ResultType =SingleRow
  ii)Sql Statement- Select count(*) as Cnt from TableA
  iii)select the resultset tab and --->click ADD--->select the variable name (Count) in the dropdown .set the varaiable name as 0


3.connct the   Execute SQLtask  from the Source SQLtask and edit the procedance constarint property

   --> Select Evaluvation opreation as "Expression
-Set Expression ----> @Coun>0 ( if the count is >0 then it will delete the Table B)

here you are deleting the Table A and after that conbnct with dataflow task will peroform the data transfer