I have a SSIS package that works fine when run inside Visual Studio.
It runs fine when run using the DTEXEC command in a cmd window.
It runs fine even when run in a powershell window using DTEXEC

It will NOT run as a job in SQL Server agent. I get this mess shown below.
As you can see I get ODBC errors and validation errors. None of those show
up anywhere else.

Can someone give me a hint here. I have been fooling around with this for
over a week trying to get this package to run in SQL server agent job mode.
I have tried running it as an Integration package job and also as a
operating system job. They both end up as shown below.
The ODBC errors come from a third party proprietery ODBC driver that
accesses a proprietary database. But it works everywhere except as an SQL
agent job.

Thanks for any suggestions
Dennis


Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 2:00:15 PM
Info: 2008-07-03 14:00:19.00
Code: 0x4004300A
Source: COPY BOX DATA DTS.Pipeline
Description: Validation phase is beginning.
End Info
Error: 2008-07-03 14:00:19.14
Code: 0xC0047062
Source: COPY BOX DATA COREDIR BOX DATA [1]
Description: System.Data.Odbc.OdbcException: ERROR [S1000] [TOD][ODBC
Driver]Invalid connect syntax (uid/pwd/datasource)
ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support
the version of ODBC behavior that the application requested (see
SQLSetEnvAttr).
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHa ndle hrHandle,
RetCode retcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcCo nnection connection,
OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
at
System.Data.Odbc.OdbcConnectionFactory.CreateConne ction(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.Creat eNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at
Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetM anagedConnection(String
assemblyQualifiedName, String connStr, Object transaction)
at
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnec tionManager90.AcquireConnection(Object
pTransaction)
at
Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceA dapter.AcquireConnections(Object
transaction)
at
Microsoft.SqlServer.Dts.Pipeline.ManagedComponentH ost.HostAcquireConnections(IDTSManagedComponentWra pper90
wrapper, Object transaction)
End Error
Error: 2008-07-03 14:00:19.14
Code: 0xC0047017
Source: COPY BOX DATA DTS.Pipeline
Description: component "COREDIR BOX DATA" (1) failed validation and
returned error code 0x80131937.
End Error
Error: 2008-07-03 14:00:19.14
Code: 0xC004700C
Source: COPY BOX DATA DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2008-07-03 14:00:19.14
Code: 0xC0024107
Source: COPY BOX DATA
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 2:00:15 PM
Finished: 2:00:19 PM
Elapsed: 3.25 seconds

Hi Dennis,

90% of all "SSIS using SQL Agent" issues are caused by the SQL Agent
service account not having the correct permissions. SSIS packages run
using the credentials of the account that starts the package - in all
the other examples you've given (cmd line, powershell, running from
SSMS, etc) this will be using your credentials. When you SQL Agent
runs the job it will be using the SQL Agent service account.

That said, error message is a bit obscure ... however this is not
unusual for SSIS, and essentially what it's saying is it couldn't
connect. I'd say check the SQL Agent account permissions, and if you
still get this problem check the Package Protection Level (google) ..
this probably accounts for 70% of other connection issues

Good luck!
J

Hi Dennis,

90% of all "SSIS using SQL Agent" issues are caused by the SQL Agent
service account not having the correct permissions. SSIS packages run
using the credentials of the account that starts the package - in all
the other examples you've given (cmd line, powershell, running from
SSMS, etc) this will be using your credentials. When you SQL Agent
runs the job it will be using the SQL Agent service account.

That said, error message is a bit obscure ... however this is not
unusual for SSIS, and essentially what it's saying is it couldn't
connect. I'd say check the SQL Agent account permissions, and if you
still get this problem check the Package Protection Level (google) ..
this probably accounts for 70% of other connection issues

Good luck!
J

Hi Dennis,

90% of all "SSIS using SQL Agent" issues are caused by the SQL Agent
service account not having the correct permissions. SSIS packages run
using the credentials of the account that starts the package - in all
the other examples you've given (cmd line, powershell, running from
SSMS, etc) this will be using your credentials. When you SQL Agent
runs the job it will be using the SQL Agent service account.

That said, error message is a bit obscure ... however this is not
unusual for SSIS, and essentially what it's saying is it couldn't
connect. I'd say check the SQL Agent account permissions, and if you
still get this problem check the Package Protection Level (google) ..
this probably accounts for 70% of other connection issues

Good luck!
J

Hi Dennis,

90% of all "SSIS using SQL Agent" issues are caused by the SQL Agent
service account not having the correct permissions. SSIS packages run
using the credentials of the account that starts the package - in all
the other examples you've given (cmd line, powershell, running from
SSMS, etc) this will be using your credentials. When you SQL Agent
runs the job it will be using the SQL Agent service account.

That said, error message is a bit obscure ... however this is not
unusual for SSIS, and essentially what it's saying is it couldn't
connect. I'd say check the SQL Agent account permissions, and if you
still get this problem check the Package Protection Level (google) ..
this probably accounts for 70% of other connection issues

Good luck!
J

Hi Dennis,

90% of all "SSIS using SQL Agent" issues are caused by the SQL Agent
service account not having the correct permissions. SSIS packages run
using the credentials of the account that starts the package - in all
the other examples you've given (cmd line, powershell, running from
SSMS, etc) this will be using your credentials. When you SQL Agent
runs the job it will be using the SQL Agent service account.

That said, error message is a bit obscure ... however this is not
unusual for SSIS, and essentially what it's saying is it couldn't
connect. I'd say check the SQL Agent account permissions, and if you
still get this problem check the Package Protection Level (google) ..
this probably accounts for 70% of other connection issues

Good luck!
J

Hi Dennis,

90% of all "SSIS using SQL Agent" issues are caused by the SQL Agent
service account not having the correct permissions. SSIS packages run
using the credentials of the account that starts the package - in all
the other examples you've given (cmd line, powershell, running from
SSMS, etc) this will be using your credentials. When you SQL Agent
runs the job it will be using the SQL Agent service account.

That said, error message is a bit obscure ... however this is not
unusual for SSIS, and essentially what it's saying is it couldn't
connect. I'd say check the SQL Agent account permissions, and if you
still get this problem check the Package Protection Level (google) ..
this probably accounts for 70% of other connection issues

Good luck!
J

Hi Dennis,

90% of all "SSIS using SQL Agent" issues are caused by the SQL Agent
service account not having the correct permissions. SSIS packages run
using the credentials of the account that starts the package - in all
the other examples you've given (cmd line, powershell, running from
SSMS, etc) this will be using your credentials. When you SQL Agent
runs the job it will be using the SQL Agent service account.

That said, error message is a bit obscure ... however this is not
unusual for SSIS, and essentially what it's saying is it couldn't
connect. I'd say check the SQL Agent account permissions, and if you
still get this problem check the Package Protection Level (google) ..
this probably accounts for 70% of other connection issues

Good luck!
J

Hi Dennis,

90% of all "SSIS using SQL Agent" issues are caused by the SQL Agent
service account not having the correct permissions. SSIS packages run
using the credentials of the account that starts the package - in all
the other examples you've given (cmd line, powershell, running from
SSMS, etc) this will be using your credentials. When you SQL Agent
runs the job it will be using the SQL Agent service account.

That said, error message is a bit obscure ... however this is not
unusual for SSIS, and essentially what it's saying is it couldn't
connect. I'd say check the SQL Agent account permissions, and if you
still get this problem check the Package Protection Level (google) ..
this probably accounts for 70% of other connection issues

Good luck!
J

Yes, permissions were my first thought too. But I changed the SQL agent
account to run under the very same account that I use to run the package in
VS and Powershell, etc.
So everything is using the very same account. So I am having a hard time
seeing how it could be a permissions issue now.

Unless there is something "different" about the account when running a
"service" vs the same account running an interactive environment????

Dennis


<jhofmeyr (AT) googlemail (DOT) com> wrote