How do I apply an insert to table for new records only when using SSIS?

Question

I have this insert script to insert new records only. But I realized that if I put the whole script in OLE DB source. It creates an error because its like declaring the destination table within the script when I put where not exists (select * from SMEC_DW.dbo.DimCustomerContact)

Should I just use an SCD (slowly changing dimension) for this? Or is there a control flow item that I could use to apply the Insert query that I have below.

INITIAL Insert to the DimCustomerContact table

INSERT  INTO BLANK_DW.dbo.DimCustomerContact
        (
          CustomerContactInt ,
          ContactID ,
          SiteID ,
          ClientName ,
          ContactFirstName ,
          ContactLastName ,
          Position ,
          Phone ,
          MobilePhone ,
          EmailAddress ,
          CustomerBaseID ,
          RecordSource ,
          RecordType ,
          SiteURN ,
          SiteDesc ,
          CustomerLink ,
          HomeCurrencyCode ,
          CustomerID ,
          CustomerCurrencyCode ,
          CustomerName ,
          CustomerShortName ,
          Address ,
          City ,
          PostalCode ,
          CountryCode ,
          CountryName ,
          StateCode ,
          StateName ,
          CustomerPhone ,
          Fax ,
          TaxCode ,
          ProspectID ,
          CreateDate ,
          LastUpdateDate ,
          MasterCustomerID ,
          MasterCustomerName ,
          MasterCustomerNotes
        )
        SELECT  *
        FROM    BLANK_ODS.dbo.ODSCustomerContact a
                JOIN BLANK_ODS.dbo.ODSCustomerBase b ON a.ClientName = b.CustomerName
                LEFT OUTER JOIN BLANK_ODS.dbo.ODSMasterCustomer c ON b.CustomerName = c.MasterCustomerName

INSERTING DATA IN THE DimCustomerContact FOR NEW RECORDS ONLY

INSERT  INTO BLANK_DW.dbo.DimCustomerContact
    (
      CustomerContactInt ,
      ContactID ,
      SiteID ,
      ClientName ,
      ContactFirstName ,
      ContactLastName ,
      Position ,
      Phone ,
      MobilePhone ,
      EmailAddress ,
      CustomerBaseID ,
      RecordSource ,
      RecordType ,
      SiteURN ,
      SiteDesc ,
      CustomerLink ,
      HomeCurrencyCode ,
      CustomerID ,
      CustomerCurrencyCode ,
      CustomerName ,
      CustomerShortName ,
      Address ,
      City ,
      PostalCode ,
      CountryCode ,
      CountryName ,
      StateCode ,
      StateName ,
      CustomerPhone ,
      Fax ,
      TaxCode ,
      ProspectID ,
      CreateDate ,
      LastUpdateDate ,
      MasterCustomerID ,
      MasterCustomerName ,
      MasterCustomerNotes
    )
    SELECT  *
    FROM    BLANK_ODS.dbo.ODSCustomerContact a
            JOIN BLANK_ODS.dbo.ODSCustomerBase b ON a.ClientName = b.CustomerName
            LEFT OUTER JOIN BLANK_ODS.dbo.ODSMasterCustomer c ON b.CustomerName = c.MasterCustomerName
    WHERE   NOT EXISTS ( SELECT *
                         FROM   BLANK_DW.dbo.DimCustomerContact )

My goal is to put this Insert new records only using SSIS. What control flow item should I use?

This is the error that I get when I use the preview button in OLE DB Source after placing the script under the sql command text

Can I just put the whole command in a single Execute SQL task or use an SCD? iF yes then how?

Answer 1

Your INSERT/SELECT statement needs to go into an Execute SQL task, not into a data flow. It's not returning records. In fact it doesn't even need to go in to SSIS it can go into an Execute SQL step inside a SQL Agent job.