Scheduling an SSIS that connects to AS400

I am facing a SQL 2005 SSIS problem.

I have an SSIS package that downloads some data from an AS400 and places the data into a SQL 2005 table.

I use Client Access ODBC connection as my Source.

When I run the package from Business Intelligence - Visual Studio, it works fine.

However when I schedule the SSIS package as a job, it falls over with the following error:

Description: System.Data.Odbc.OdbcException: ERROR [08S01] [IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=11004 - CWBCO1011 - Remote port could not be resolved

Please can someone help me......

Ric

By the way this is the full error message below:

Error: 2007-08-08 14:00:47.85
Code: 0xC0047062
Source: Data Flow Task DataReader Source [893]
Description: System.Data.Odbc.OdbcException: ERROR [08S01] [IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=11004 - CWBCO1011 - Remote port could not be resolved
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.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.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)
End Error
Error: 2007-08-08 14:00:47.87
Code: 0xC0047017
Source: Data Flow Task DTS.Pipeline
Description: component "DataReader Source" (893) failed validation and returned error code 0x80131937.
End Error

Hi,

If you run your package from Management Studio or dtexec, do you get the same error?

Do you have any other job succesfuly running packages pulling from other sources?

It could be a security issue rather than a data provider issue.

Philippe

|||

Moving data from AS400 with SQL Server Agent Job have worked since SQL Server 7.0 Microsoft have changed a few things but it still works. All you need is in the thread below.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1963603&SiteID=1