SQL 2008 R2 SSIS package fail using SQL Agent
Hi Experts,
I have a package, works fine when I use the Execute Package Utility in SSMS.
The problem arises when I make a SQL Job for it.
Before I schedule a SQL Job I test it by manually starting the job.
My problem is.
I have a 3rd party ODBC driver "EasySoft", which is failing to recognise the fact there is a DSN setup for it.
I have tried run the package using a Proxy account and the SQL Server Agent account. Both of which are sysadmins to the SQL server.
I have tried the use 32 bit runtime and the 64 bit runtime.
My DSN has been setup using C:\WINDOWS\SysWOW64\odbcad
My ODBC Driver is not available when I use the Administrative Tools, Data Sources ODBC options. I can only see the SQL Server Drivers (which I assume is 64 Bit).
Why can Execute Package Ulitity see my DSN whereas the SQL job cannot?
What else can I try?
Thanks
Tony
I have a package, works fine when I use the Execute Package Utility in SSMS.
The problem arises when I make a SQL Job for it.
Before I schedule a SQL Job I test it by manually starting the job.
My problem is.
I have a 3rd party ODBC driver "EasySoft", which is failing to recognise the fact there is a DSN setup for it.
I have tried run the package using a Proxy account and the SQL Server Agent account. Both of which are sysadmins to the SQL server.
I have tried the use 32 bit runtime and the 64 bit runtime.
My DSN has been setup using C:\WINDOWS\SysWOW64\odbcad
My ODBC Driver is not available when I use the Administrative Tools, Data Sources ODBC options. I can only see the SQL Server Drivers (which I assume is 64 Bit).
Why can Execute Package Ulitity see my DSN whereas the SQL job cannot?
What else can I try?
Thanks
Tony
Executed as user: SQL AGENT NT ACCOUNT. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 1:40:01 p.m. Error: 2011-05-17 13:40:09.02 Code: 0xC0047062 Source: MY DATAFLOW TASK ADO NET Source Easysoft MY DSN[1670] Description: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions) at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.Odbc.OdbcConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction) End Error Error: 2011-05-17 13:40:09.02 Code: 0xC0047017 Source: MY DATAFLOW TASK SSIS.Pipeline Description: component "ADO NET Source Easysoft MY DSN" (1670) failed validation and returned error code 0x80131937. End Error Error: 2011-05-17 13:40:09.02 Code: 0xC004700C Source: MY DATAFLOW TASK SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2011-05-17 13:40:09.02 Code: 0xC0024107 Source: MY DATAFLOW TASK Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:40:01 p.m. Finished: 1:40:40 p.m. Elapsed: 38.891 seconds. The package execution failed. The step failed.ASKER
Hi sachitjain,
I am using SQL Agent Job scheduler, specifically at the Step I am using is the Type: SQL Server Integration Service Package. So yes I am using the dtexec. My package is in the SSIS Package Store.
OK so let me give my opinion of what user account does what.
PLEASE correct me if I am wrong.
The SQL job gets executed by the SQL Agent user, in my case it is DOMAIN\sql02
My package creator is DOMAIN\sql02
My SQL Job owner is DOMAIN\sql02
WIthin the package there is a DataFlow task, this uses a 3rd party ODBC driver. I have made a DSN for it. The DSN test works, the UID "SYSDBA" and DSN "MYDSN" name are listed in the package connection manager with a ************ as the password.
When I look at the setup of the SQL Job Step I see the package has my connection string for this task written as uid=SYSDBA;DSN=MYDSN.
There is no password listed.
This I believe is the problem, SQL Agent cannot decipher the password when it wants to run the package.
Can you suggest what package protection setting I need to use in the BIDS.
Then what to set the package protection when I am importing it into SSIS Package Store?
Thanks
Tony
I am using SQL Agent Job scheduler, specifically at the Step I am using is the Type: SQL Server Integration Service Package. So yes I am using the dtexec. My package is in the SSIS Package Store.
OK so let me give my opinion of what user account does what.
PLEASE correct me if I am wrong.
The SQL job gets executed by the SQL Agent user, in my case it is DOMAIN\sql02
My package creator is DOMAIN\sql02
My SQL Job owner is DOMAIN\sql02
WIthin the package there is a DataFlow task, this uses a 3rd party ODBC driver. I have made a DSN for it. The DSN test works, the UID "SYSDBA" and DSN "MYDSN" name are listed in the package connection manager with a ************ as the password.
When I look at the setup of the SQL Job Step I see the package has my connection string for this task written as uid=SYSDBA;DSN=MYDSN.
There is no password listed.
This I believe is the problem, SQL Agent cannot decipher the password when it wants to run the package.
Can you suggest what package protection setting I need to use in the BIDS.
Then what to set the package protection when I am importing it into SSIS Package Store?
Thanks
Tony
I am not very sure about DSNs. But one thing I could guess is that the account under which your SQL Agent service is running should have access on desired DSN. I think you would definitely have some option in your DSN properties where you could give access/control over that DSN to a particular Windows account. Just try doing it for the account under which your SQL Agent service is running.
Moreover is your 3rd party ODBC driver connecting to an Oracle db or some other data source?
Moreover is your 3rd party ODBC driver connecting to an Oracle db or some other data source?
ASKER
I can log into the server as the same account as my SQL Agent. I can go to the 32 bit ODBC setup screen and configure my DSN, the test connection works. There is no windows account associated with the DSN, there is no place for it either. The username and password for the DSN in standard Interbase. USER is SYSDBA and PASSWORD is masterkey.
Bear in mind, running this manually from DTExec works fine. I can't get what is different.
It's driving me mad.
T
Bear in mind, running this manually from DTExec works fine. I can't get what is different.
It's driving me mad.
T
Then you could try one more thing. Create a stored procedure with RUNAS some account, try running the package under that stored procedure through dtexec. If it runs through stored proc successfully then you create an Execute SQL task within your job to execute that stored procedure instead of package directly. It looks like your system is playing tricks with you so you could also try one. :-)
The difference is the permissions. When you run it from DTExec you are executing it with your own credentials. When you run it from the SQL Agent job it is executing under the credentials of the SQL Agent service account. You said you logged in to the server as the SQL Agent service account. Did you try running the package with DTExec while you were logged in as the service? I would expect that it would fail just like it is doing from the job.
Since you have a sensitive password in the package that you want retained, I would suggest using Encrypt sensitive data with password when importing into the SSIS package store. That alone may solve the issue.
Since you have a sensitive password in the package that you want retained, I would suggest using Encrypt sensitive data with password when importing into the SSIS package store. That alone may solve the issue.
In addition to the previous comments, whey you run the SSIS job under SQL Server Agent, you are running it as a service which is not the same as running it logged on as the same user.
ASKER
Gentlemen,
I have come back to this with a more refined approach.
I feel this is to do with the Package Encryption.
I found this...
1. Connect to your Integration Services instance on the same server where the package is located.
2. Right click on the MSDB folder and click on the option to "import package".
3. The package location will be file system, browse to the package name. It should have a dtsx extension. Click on the elipise button next to protection level. Select encrypt sensitive data with password. Type in your password for the package. Click ok. Enter your password for the package once more.
4. Under SQL Server Agent right click the jobs folder and select new job. Create a new job step. The Type will be SQL Server Integration Services Package. The package source will be SSIS Package Store. Select the correct server name. Select the the elipise button next to the Package: option and select the package in step #3 under the MSDB folder.
I have a problem still, and I think it is to do with step 3.
I am asked to enter a password I have 2 fields in which to type. Password and Retype Password.
I entered it twice, once each in the 2 fields.
Then pressed OK.
I have not been asked to enter it again.
Item 3 states. Type in your password for the package. Click ok.
I cannot press OK, it is greyed out with only 1 of the 2 password fields populated.
Any ideas?
I have stuggled and been infinitely confused with various reading, config files, deployment utility, 64 bit and 32 bit runtime.
I have imported this item 30 + times all with the same failure point. DSN not being recognised when run under SQL Agent.
Thanks
T
I have come back to this with a more refined approach.
I feel this is to do with the Package Encryption.
I found this...
1. Connect to your Integration Services instance on the same server where the package is located.
2. Right click on the MSDB folder and click on the option to "import package".
3. The package location will be file system, browse to the package name. It should have a dtsx extension. Click on the elipise button next to protection level. Select encrypt sensitive data with password. Type in your password for the package. Click ok. Enter your password for the package once more.
4. Under SQL Server Agent right click the jobs folder and select new job. Create a new job step. The Type will be SQL Server Integration Services Package. The package source will be SSIS Package Store. Select the correct server name. Select the the elipise button next to the Package: option and select the package in step #3 under the MSDB folder.
I have a problem still, and I think it is to do with step 3.
I am asked to enter a password I have 2 fields in which to type. Password and Retype Password.
I entered it twice, once each in the 2 fields.
Then pressed OK.
I have not been asked to enter it again.
Item 3 states. Type in your password for the package. Click ok.
I cannot press OK, it is greyed out with only 1 of the 2 password fields populated.
Any ideas?
I have stuggled and been infinitely confused with various reading, config files, deployment utility, 64 bit and 32 bit runtime.
I have imported this item 30 + times all with the same failure point. DSN not being recognised when run under SQL Agent.
Thanks
T
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
correction to above. I think the fact I imported the package into SQL Server rather than SSIS Package Store or File System is the key.
ASKER
Went around the moon looking at package encryption, passwords, deployment.
The solution above worked.
The solution above worked.
http://support.microsoft.com/kb/918760