2

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
Hadi
  • 36,233
  • 13
  • 65
  • 124
Boltz
  • 135
  • 1
  • 1
  • 5
  • Does your package show any errors when it tries to hit the Execute SQL Task? Have you checked SQL server logs for access issues to that table? There are numerous ways you could load that data to AxonOrders, have you tried any other methods (i.e. DELETE then INSERT INTO, OLE DB Source > Derived Column (Site = GT) > OLE DB Dest?, etc). – svenGUTT Feb 14 '22 at 18:27

1 Answers1

1

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
Hadi
  • 36,233
  • 13
  • 65
  • 124