CODEWITHSUNDEEP
sqlsql-serverssisetlexecute-sql-task
Boltz
Boltz

Reputation: 155

SSIS package insert data from CSV file to a staging table and then move from a staging table to a main table with a Site Column

I have a CSV file that successfully moves from Source File to a staging table in a Data Flow Task from a sequence container. There will be a few sequences of this. I then need to move this data from the staging table to the main table that contains an extra column (Site). I am using a SQL task to move from staging to the main table. When I run this, it goes to my staging table but, never hits my main table.

enter image description here

enter image description here

Here is my Code in my Execute SQL Task

USE ENSTRDW
UPDATE
    AxonOrders

SET
 AxonOrders.OrderNbr = AxonOrdersExtractCleanCreated.OrderNbr
       ,AxonOrders.OrderStatus = AxonOrdersExtractCleanCreated.OrderStatus
      ,AxonOrders.OrderEndDate = AxonOrdersExtractCleanCreated.OrderEndDate
      ,AxonOrders.InvoiceDate = AxonOrdersExtractCleanCreated.InvoiceDate
      ,AxonOrders.OrderDate = AxonOrdersExtractCleanCreated.OrderDate
      ,AxonOrders.RevenuePerMile = AxonOrdersExtractCleanCreated.RevenuePerMile
      ,AxonOrders.ReadyToInvoice = AxonOrdersExtractCleanCreated.ReadyToInvoice
      ,AxonOrders.OrderCommodity = AxonOrdersExtractCleanCreated.OrderCommodity
      ,AxonOrders.OrderTractors = AxonOrdersExtractCleanCreated.OrderTractors
      ,AxonOrders.BillableMileage = AxonOrdersExtractCleanCreated.BillableMileage
      ,AxonOrders.Site = 'GT'
      ,AxonOrders.LastModified = AxonOrdersExtractCleanCreated.LastModified
      ,AxonOrders.VoidedOn = AxonOrdersExtractCleanCreated.VoidedOn
      ,AxonOrders.OrderDateTimeEntered = AxonOrdersExtractCleanCreated.OrderDateTimeEntered
  
FROM
    AxonOrdersExtractCleanCreated

Upvotes: 2

Views: 610

Answers (1)

Hadi
Hadi

Reputation: 37358

Why using an UPDATE command to INSERT data?!

You should use an INSERT INTO command rather than UPDATE:

USE ENSTRDW;
INSERT INTO [AxonOrders](OrderNbr,OrderStatus,OrderEndDate,InvoiceDate,OrderDate,RevenuePerMile,ReadyToInvoice,
             OrderCommodity,OrderTractors,BillableMileage,Site,LastModified,VoidedOn,OrderDateTimeEntered)
SELECT           
            OrderNbr,OrderStatus,OrderEndDate,InvoiceDate,OrderDate,RevenuePerMile,ReadyToInvoice,
             OrderCommodity,OrderTractors,BillableMileage,'GT',LastModified,VoidedOn,OrderDateTimeEntered
  
FROM
    AxonOrdersExtractCleanCreated

Upvotes: 1

Related Questions