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

Scheduled Jobs

Its not possible to schedule jobs via Express is it? Like I have a process that connects to an Oracle system grabs the data... plays with it some and then inserts it into the MSSQL 2005 server... We need this process to run say every 2 hours... Express cant do that cant it?SQL Express has no SQL Agent job scheduling service. You can execute the appropiate jobs using SQLCMD and the AT or any other scheduling service on your machine.

See the feature comparison here: http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

hi,

in addition to Jens answer, you can perhaps have a look at "third party" solutions, both commercial and free, like http://www.valesoftware.com/products-express-agent.php and http://www.codeproject.com/useritems/SQLAgent.asp to implement scheduled features..

regards

|||another possible solution (thats free) is to create a small batch script that calls sqlcmd and does your sql processing. this batch file could then be scheduled using windows scheduling to be called every 2 hours.