x

SSIS Packages and SQL Server Jobs

I am running a SQL Server Job through an SSIS package to pick up data from a spreadsheet and import into database. When the SQL Server Job runs, it doesnt produce and fail messages yet it doesnt pick up the most recent spreadsheet. However when I run the package manually through SSIS it works fine and popluated all data.

Any ideas? Thanks
more ▼

asked Jul 25, 2011 at 01:32 AM in Default

tommyh17 gravatar image

tommyh17
13 1 1 1

Does the account running the job have access to the file location?
Jul 25, 2011 at 01:36 AM WilliamD
I thinks so. How can i double check?
Jul 25, 2011 at 01:41 AM tommyh17
You check which windows account is running the job (normally the SQL Agent account). Then you check the permissions on the folder where the spreadsheet is stored. I am guessing that your windows account has permissions, whilst the SQL Agent Account doesn't. You should be listed with at least read permissions, the Agent Account may not be listed at all. Add the necessary permissions for that user and give it another go.
Jul 25, 2011 at 01:45 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

There can be several possibilities.

When running manually, do you execute it under the same credentials as when it runs automatically? Eg. if you are executing the SSIS from within an Agent, then the SSIS package is also executed under the Agent credential.

When you run it manually then depends how you have executed it. It can run under your credentials, on your machine instead of server etc.

In that case, check, that the Agent account have enough rights and have access to the excel file you would like to import. Also note, that if you have a path to the file specified in the package, the path have to be in form available to the account and server on which it is executed. (As it can differ from path available during development on dev. machine).

Also you need to consider the x32 vs x64 version of SQL Server. X64 version of SQL server dos not like excel and then you need to execute the package using x32 runtime of dtexec. When executing it manually you may be executing it using the x32 runtime vs x64 runtime when executed automatically.
more ▼

answered Jul 25, 2011 at 01:43 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x946
x27

asked: Jul 25, 2011 at 01:32 AM

Seen: 872 times

Last Updated: Jul 25, 2011 at 01:32 AM