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.

What does the package do? What's sql agent service startup account?

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

Where is ASN file? Does sql agent service account have permission to access it?

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.

Enable package logging, that will tell you what happened.