narayanhy
asked on
SSIS package does not run in SQL agent job
We have Visual Studio 2012 and SQL server 2012 standard edition.
I have a package which reads data from IBM i-series and populates a table in SQL server. Works fine from SSDT. When I configure this in SQL agent job, I get the following error message.
I have tried to deploy it in SSIS catalog and still get the same error.
Executed as user: YMA\sql2012sa. Microsoft (R) SQL Server Execute Package Utility Version 11.0.5058.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 12:12:55 PM Error: 2015-04-28 12:12:55.27 Code: 0xC0209303 Source: Package1 Connection manager "AS400CA.AS400CA.M_NARAYAN
I have a package which reads data from IBM i-series and populates a table in SQL server. Works fine from SSDT. When I configure this in SQL agent job, I get the following error message.
I have tried to deploy it in SSIS catalog and still get the same error.
Executed as user: YMA\sql2012sa. Microsoft (R) SQL Server Execute Package Utility Version 11.0.5058.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 12:12:55 PM Error: 2015-04-28 12:12:55.27 Code: 0xC0209303 Source: Package1 Connection manager "AS400CA.AS400CA.M_NARAYAN
One cause is exactly what the error message states(Run in 32 bit), here is a step by step guide on how to resolve:
1. in the Job properties select steps
2. select the step in which the package is setup to execute ( i Assumed you have already created this) and press edit
3. select the execution tab
4. Tick use 32 bit runtime
5. press ok and save
I have had a similar issue with the exact same error and running the package in 32 bit did not resolve anything,
in my case the cause was with user permissions. (me being stupid it took me a few days to find the cause)
when you run a package in SSDT it uses your windows current session credentials to run the package, running it within SQL however you need to select a user with permissions on running SSIS packages.
Now you can either use Proxy account or the SQL Server Agent Service Account.
here is my setup:
1. I have already imported my SSIS package into the Integration Services Package store
2. in Job step properties the package is set to run as SQL Server Agent Service Account
3. Package source is set to SSIS Package store
4. Use windows credentials
5. and finally the package path within the Store under MSDB
again no matter which method you use in either case make sure the job runs with an account that has full permission on SSIS executions.
Note that if you would like to use windows credentials instead of SQL server agent you need to create a proxy account.
1. in the Job properties select steps
2. select the step in which the package is setup to execute ( i Assumed you have already created this) and press edit
3. select the execution tab
4. Tick use 32 bit runtime5. press ok and save
HOWEVER
:I have had a similar issue with the exact same error and running the package in 32 bit did not resolve anything,
in my case the cause was with user permissions. (me being stupid it took me a few days to find the cause)
when you run a package in SSDT it uses your windows current session credentials to run the package, running it within SQL however you need to select a user with permissions on running SSIS packages.
Now you can either use Proxy account or the SQL Server Agent Service Account.
here is my setup:
1. I have already imported my SSIS package into the Integration Services Package store
2. in Job step properties the package is set to run as SQL Server Agent Service Account
3. Package source is set to SSIS Package store
4. Use windows credentials
5. and finally the package path within the Store under MSDB
again no matter which method you use in either case make sure the job runs with an account that has full permission on SSIS executions.
Note that if you would like to use windows credentials instead of SQL server agent you need to create a proxy account.
ASKER
Thanks a lot for your responses.
I should have mentioned that I already tried the 32 bit option and still the same issue.
The SQL agent account has administrator rights on the server. The SSIS service is owned by another account. Is this OK or should I make the service account as the owner of SSIS service also ?
One of the team members also mentioned about a component for connecting to AS400 not right in the SQL server. He is installing it tonight. I will test it tomorrow and update the status.
Also, what is the best practice to store packages if I plan lot of SQL agent jobs to be scheduled overnight ?
Should I store the packages in a drive on the server or in MSDB package store or in SSIS catalog ?,
Another thing I noticed is the project level connection managers give trouble when I schedule a job using SQL agent. Is this a real problem or I am missing something ?
We have SQL server 2012 standard edition and I am using VS2012/SSDT.
Thanks Again.
I should have mentioned that I already tried the 32 bit option and still the same issue.
The SQL agent account has administrator rights on the server. The SSIS service is owned by another account. Is this OK or should I make the service account as the owner of SSIS service also ?
One of the team members also mentioned about a component for connecting to AS400 not right in the SQL server. He is installing it tonight. I will test it tomorrow and update the status.
Also, what is the best practice to store packages if I plan lot of SQL agent jobs to be scheduled overnight ?
Should I store the packages in a drive on the server or in MSDB package store or in SSIS catalog ?,
Another thing I noticed is the project level connection managers give trouble when I schedule a job using SQL agent. Is this a real problem or I am missing something ?
We have SQL server 2012 standard edition and I am using VS2012/SSDT.
Thanks Again.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot experts. After the component was installed, The issue was solved.
But, my immediate task was changed to something else and I will post a different question for that.
Kind Regards
But, my immediate task was changed to something else and I will post a different question for that.
Kind Regards
indeed, the SSDT runs in 32bit mode, the SQL agent does run in 64bit mode.
as indicated, you can configure the package to indeed run in 32 bit mode, a bit of work though:
https://technet.microsoft.com/en-us/library/ms138023%28v=sql.105%29.aspx