1

So far,

My control flow looks like:

Execute SQL Task - This executes a statement that creates a staging table. If one exists, it is dropped so everytime the package is run, the staging table will be able to track the updates that have occurred. My code for this is:

IF OBJECT_ID('CDC_Staging', 'U') IS NOT NULL
   DROP TABLE  CDC_Staging;

CREATE TABLE CDC_Staging
(
    [Employee_ID] [int] PRIMARY KEY NOT NULL,
    [FirstName] [nvarchar](255) NULL,
    [LastName] [nvarchar](255) NULL,
    [Education] [nvarchar](255) NULL,
    [Occupation] [nvarchar](255) NULL,
    [YearlyIncome] [float] NULL,
    [Sales] [float] NULL
);

Data Flow Task - The OLE DB Source is the Source Database. The Lookup uses the results of this query:

SELECT [Employee_ID] FROM [CDC_Target]

Then I use a conditional split that puts inserts in the Target Database and Updates to Staging enter image description here

My Final control flow task is an execute SQL Task. I used this query to update the target database.

UPDATE [dbo].[CDC_Target]
   SET [FirstName] = Staging.[FirstName]
      ,[LastName] = Staging.[LastName]
      ,[Education] = Staging.[Education]
      ,[Occupation] = Staging.[Occupation]
      ,[YearlyIncome] = Staging.[YearlyIncome]
      ,[Sales] = Staging.[Sales]
FROM [CDC_Target]
   INNER JOIN
     [CDC_Staging] AS Staging 
     ON [CDC_Target].Employee_ID = Staging.Employee_ID

I just inserted 4 more records into my source table. After I ran the package, the Source Table and Target table had the correct data but the staging table did not contain the 4 inserts that I committed.

Am I doing something wrong here? Any suggestions on how to better do this?

PS. I followed a tutorial explaining how to do this, I'm a Jr. DBA but my team lead is wanting me to learn SSIS.

  • 1
    Welcome to StackOverflow! First, you are a DBA and your team lead wants you to learn SSIS. This is great! Now from your diagram, I am struggling to find when you insert the data into the staging table. From an ETL perspective, we assume you need the staging table to run transformations (clean and format the data). When does this happen? – clifton_h Mar 29 at 19:31
  • It happens after the conditional split I think? The 2nd OLE DB Destination is to the staging table if that helps. – Patrick Mar 29 at 20:48
  • What is the condition in the Conditional Split? And if you run this in SSDT from the Data Flow Task view, are any rows sent to the staging table? – userfl89 Apr 1 at 14:06
  • Condition in conditional split is - ISNULL(LKOP_Employee_ID) – Patrick Apr 1 at 16:02
0

If I've understood correctly your OLE DB Destination is to the 'target' table and your OLE DB Destination 1 is to the 'staging' table? That would explain why your new rows aren't appearing in the 'staging' table as you're only sending them to the 'target' table.

If you want all rows (updated or inserted) to appear in the 'staging' table, I would suggest you remove the Lookup and Conditional Split and simply insert all rows to the 'staging' table. Then use a Execute SQL task to fire a MERGE statement to update or insert as appropriate to the 'target' table. In my experience this is much more performant that using the lookup to decide whether to insert or update

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.