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"     Description: The requested OLE DB provider IBMDA400.DataSource.1 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".  End Error  Error: 2015-04-28 12:12:55.27     Code: 0xC020801C     Source: Data Flow Task OLE DB Source [53]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "AS400CA.AS400CA.M_NARAYAN" failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2015-04-28 12:12:55.27     Code: 0xC0047017     Source: Data Flow Task SSIS.Pipeline     Description: OLE DB Source failed validation and returned error code 0xC020801C.  End Error  Error: 2015-04-28 12:12:55.27     Code: 0xC004700C     Source: Data Flow Task SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2015-04-28 12:12:55.27     Code: 0xC0024107     Source: Data Flow Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  12:12:55 PM  Finished: 12:12:55 PM  Elapsed:  0.25 seconds.  The package execution failed.  The step failed.
narayanhyAsked:
Who is Participating?
 
enrmmobaCommented:
we have the same system specification,

Personally i do not like the idea of Storing packages on a drive, Package store / SSIS catalog is more centralized and i have also noticed faster to run plus you do not need to worry about setting permissions for the SSIS user on the stored drive.

as for the main issue, it seems to me the SQL server agent / what ever user you are trying to run the job with may not have access to one part of your package data flow, might be because of the missing AS400 component you mentioned.

easiest way to check is:

import the package into MSDB, once done right click and select RUN Package, if it runs manually this way with no error it is definitely a permission issue some where. if it returns error it will be in more details which would help us identify the issue.

either way please note the result here so we can  try to diagnose the cause.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> Description: The requested OLE DB provider IBMDA400.DataSource.1 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode.

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
0
 
enrmmobaCommented:
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
Untitled.png4. Tick use 32 bit runtime
5. 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.
0
 
narayanhyAuthor Commented:
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.
0
 
narayanhyAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.