asked
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
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.
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.
