SQL 2008 R2 SSIS package fail using SQL Agent

Tony303
Tony303 used Ask the Experts™
on
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\odbcad32.exe (which I think is a 32bit driver).
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.

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Are you using dtexec EXE for executing the package through scheduled job. If no then you could try out this also.

http://support.microsoft.com/kb/918760
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 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?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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
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. :-)

Commented:
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.
Top Expert 2012

Commented:
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.
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 solved it, after being around the moon a couple of times.

I looked here...
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/e13c137c-1535-4475-8c2f-c7e6e7d125fc/

I worked through this below, and lo and behold, I am in business.

Besides you can import your SSIS package into your SQL Server MSDB database via SQL Server Management Studio (connecting to Integration Services). When you import the package to MSDB, you need to set the ProtectionLevel to "Rely on server storage and roles for access control". After that, add your SQL Server Agent Services account to be a user of MSDB with the db_ssisadmin role. Then connect to your SSIS in SSMS again, right click your SSIS package, click Package Roles, and set both the ReaderRole and Writer Role to db_ssisadmin.

I was wrongly thinking that because my SQL Agent account login was being used as Package Owner and as sysadmin SQL user etc that it would have "ALL THE RIGHTS IN THE WORLD"

Thanks for your inputs. Not sure what the point situation should be??
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.
Went around the moon looking at package encryption, passwords, deployment.
The solution above worked.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial