Results 1 to 6 of 6

Thread: DTS Error MESSAGE - JOB

  1. #1
    Join Date
    Dec 2006
    Posts
    6

    DTS Error MESSAGE - JOB

    Executed as user: company name\admin. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error string: The task reported failure on execution. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task reported failure on execution. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSSendMailTask_1 DTSRun OnFinish: DTSStep_DTSSendMailTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    What does the package do? What's sql agent service startup account?

  3. #3
    Join Date
    Dec 2006
    Posts
    6
    Copy of procedure below - dts basic gets ASN flat file data generated from our Solomon database and formats it using parameters below. Then it places it in an out bound folder to be imported by Gentran 5.0. - job is executed every morning to start the process below.


    CREATE procedure GetEDI856WAL10008
    AS
    select 'numeric value here' as [PARTNER_ID],
    'X' as [STANDARD],
    '005010' as [VERSION],
    '856' as [TRANSACTION_SET],
    'P' as [TEST_PROD],
    'WAL' as [PARTNERCODE],
    --SHP
    a.shipperid as [SHIP_ID],
    a.shipdateact as [SHIP_DATE],
    b.qtyship*0.9 as [GROSS_WT],
    'FDCC' as [SCAC_CODE],
    rtrim(a.shipperid) + rtrim(a.ordnbr) as [BOL_NUMBER],
    a.shipperid as [PRO_NUMBER],
    'CC' as [METHOD_PAYMENT],
    'CTN25' as [PACKING_CD],
    '1' as [LADING_QTY],
    'LB' as [WEIGHT_UOM],
    'BRIGHTSTAR' as [SHIP_FROM],
    a.user1 as [ST_IDCODE],
    a.shipname as [ST_NAME],
    '' as [VOLUME],
    'CF' as [VOLUME_UOM],
    '' as [ASSIGNED_LOAD], -- required in TL
    '' as [APPT_NUMBER], -- required in TL
    --PON
    a.custordnbr as [PONUM],
    a.orddate as [PO_DATE],
    '479811870' as [INTERNAL_VENDORNO],
    a.user2 as [DEPARTMENT_NO],
    'CC' as [STATUS],
    --ITEM
    b.alternateid as [BUYER_ITEMNO],
    b.user2 as [VENDOR_ITEM_NUMBER],
    [LINITEM_QTYSHIP] = case b.alternateid when 'numeric value here' then c.user3*36 else c.user3 end,
    [LINITEM_QTY_ORD] = case b.alternateid when 'numeric value here' then c.user3*36 else c.user3 end,
    --b.qtyship as [LINITEM_QTYSHIP],
    --b.qtyord as [LINITEM_QTY_ORD],
    --c.user3 as [LINITEM_QTYSHIP],
    --c.user3 as [LINITEM_QTY_ORD],
    f.upccode as [UCC_CD],
    '00' + f.upccode as [GTIN_CD],
    'EA' as [SHIPPED_UOM],
    '' as [PACK], -- 1 if shipped_uom = 'CA'
    '' as [INNER_PACK], -- c.user3 if shipped_uom = 'CA'
    --PAC
    '20' + rtrim(f.upccode) as [SCC14],
    --[BOX_QTY] = case b.alternateid when 'numeric value here' then c.user3 else b.qtyship/c.user3 end
    c.user3 as [BOX_QTY]
    --b.qtyship/c.user3 as [BOX_QTY]
    from soshipheader as a with (nolock) inner join soshipline as b with (nolock) on a.shipperid = b.shipperid and a.custid = 'WAL10008' and left(a.shipperid, 2) = 'RT'
    inner join itemxref as c with (nolock) on b.alternateid = c.alternateid and a.custid = c.entityid
    left join sscc128 as d with (nolock) on b.shipperid = d.shipperid and b.invtid = d.invtid
    inner join soaddress as e with (nolock) on a.shiptoid = e.shiptoid
    inner join inventory as f with (nolock) on b.invtid = f.invtid
    --left join soshippack as g with (nolock) on a.shipperid = g.shipperid
    inner join soevent as h with (nolock) on a.shipperid = h.shipperid and h.eventtype = 'USHP'
    --inner join soevent as f on a.shipperid = f.shipperid and f.eventtype = 'ushp'
    --where convert(char(10), f.eventdate, 101) = convert(char(10), getdate(), 101)
    --where CONVERT(CHAR(10), h.eventdate, 101) >= CONVERT(CHAR(10), GETDATE()-7, 101)
    where CONVERT(CHAR(10), h.eventdate, 101) = CONVERT(CHAR(10), GETDATE()-1, 101)
    --where a.invcdate = '10/10/2006'
    ---where a.shipperid in ('alpha numeric here')
    order by a.custordnbr, b.invtid
    GO

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Where is ASN file? Does sql agent service account have permission to access it?

  5. #5
    Join Date
    Dec 2006
    Posts
    6

    DTS Error Message - Job - Reply2

    ASN file is located on another server and yes it should have permissions. The process runs everyday normally accept for some reason at this time and a file is in the location where it pulls from.
    It will error then rerun and pickup.

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Enable package logging, that will tell you what happened.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •