Post reply

Setting SSIS Job on SQL Agent

  • January 30, 2012 at 7:20 am

    #255999

    Hi,

    I'm trying to set up a SQL Agent job to run a SSIS package which imports excel file into a sql database. The package works fine in BIDS however when I run the sql agent job the job fails with the error message below.

    Dtexec: Could not set \Package.Variable[ConnectionString].Value value to S:\Extract.xls

    How can I fix this issue?

    Thanks

  • January 30, 2012 at 7:30 am

    #1439982

    Does the SQL Server agent service account have permissions on the .xls file? Also, is S a mapped drive? The service account won't be able to see it because it's only mapped in the context of your user. It's safest to use UNC notation instead of drive letter.

    John

  • January 30, 2012 at 7:31 am

    #1439983

    Does the SQL Server agent service account have permissions on the .xls file? Also, is S a mapped drive? The service account won't be able to see it because it's only mapped in the context of your user. It's safest to use UNC notation instead of drive letter.

    John

  • January 30, 2012 at 7:33 am

    #1439986

    Hi,

    The SQL Server agent service account has permissions on the .xls and also I have used the UNC notation and that also failed withthe same error.

  • January 30, 2012 at 10:21 am

    #1440136

    I set up my Excel connections using OLE DB for MS Office Access 12.0 and set Extended Properties to Excel 12.0

    We found out that the Excel connection is too flakey for us.

    Hope this helps!

  • January 30, 2012 at 10:24 am

    #1440139

    Thanks...I'll give that a go and see what happens.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply