Hi to all We have a SQL server that use for applications in PC side. Our new task is to create a SSIS package to download data from a table in iSeries/400 (AS/400) and schedule it to run once everyday. When I try to create the datasource, i can't find any driver that is useful to talk to the AS/400. we have these 3 providers - .net providers, .net Providers for OleDB and Native OLE DB Within all these 3 providers, I can't see one that is for AS/400. Do i need to install the ODBC driver for IBM Client Access for AS400 or is there any other default drivers I can use. thx, Ted.

Try ADO.Net with ODBC and select the DSN.

hi to all I just have the ODBC driver installed in our SQL server. We use the Client Access CD for iSeries/400 v5r4. Then we can see the AS400 driver under all 3 providers. I just created a test package pulling a table from AS400 and use flat file as data flow destination. The OLE DB provider show in the connection manager is : IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider. thank you for helping out, Ted.

hi Here is an update of my thread here... I tried to use the OLE DB driver in the providers mentioned above. But some how the package will error out. Then I used Client Access ODBC driver instead and the package run without error. I managed to created the SSIS package, deployed as file system package in the SQL server. But encounter an error msg in when scheduled it to run in SQL agent as a job. Here is the err msg from the log..., any suggestion please... *************** Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:48:10 AM Error: 2011-02-22 11:48:10.40 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2011-02-22 11:48:10.40 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error *************** thx, Ted.

You're experiencing a secure-by-default deployment problem. Your credentials stored in the package are encrypted, and unable to be used by the Agent account. See the very first item in the FAQ at the top of this forum. Talk to me now on

Hi, Todd Thank you for help out. Can I changed the security "EncryptSentivewithPassword" and how can i include the password in the command line? any example to follow? thx, Ted.

The "EncryptSensitivewithPassword" option is there on the package level in the drop-down of the protection level property page. The /De option sets the password in DTExec e.g. /De mypassword where the password is case sensitiveArthur My Blog

thx, now i set the password in package level, created and deployed the package. It seems to run on the SQL server Agent job schedule without the error msg with DTS password. But it error out in a connection string to AS400. What's I do not understand is that it run fine by the package itself but have issue with connection string (see error below) when running in server agent job. any suggestion? The connection string is for pulling data from iSeries/400. ************************************************ Date 2/22/2011 3:16:48 PM Log Job History (ssistest3) Step ID 1 Server SQL Job Name ssistest3 Step Name execute_ssistest3_package Duration 00:00:00 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Executed as user: domainA\sql_user. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Argument "iSeries400_query.XYZ" for option "connection" is not valid. The command line parameters are invalid. The step failed. ************************************************

thx, now i set the password in package level, created and deployed the package. It seems to run on the SQL server Agent job schedule without the error msg with DTS password. But it error out in a connection string to AS400. What's I do not understand is that it run fine by the package itself but have issue with connection string (see error below) when running in server agent job. any suggestion? The connection string is for pulling data from iSeries/400. ************************************************ Date 2/22/2011 3:16:48 PM Log Job History (ssistest3) Step ID 1 Server SQL Job Name ssistest3 Step Name execute_ssistest3_package Duration 00:00:00 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Executed as user: domainA\sql_user. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Argument "iSeries400_query.XYZ" for option "connection" is not valid. The command line parameters are invalid. The step failed. ************************************************

Check if you can communicate from the box with the AS400 instance, and if yes Try setting up the conn in the DTExec Arthur My Blog

hi everyone: Thx for helping out. I re-create a new package in this morning. Then, use the commend line dtexec to run the package. It works this way. Then i create a schedule task to run that dtexec command, it also work. This time I create a sql server agent job, the job step type i use is "Operating System (CmdExec)" which run the dtexec command line. The job run without error and completed with records downloaded to a table in sql server as we wanted to. But, when i create another job with job step type "sql server integration service package", it failed again. I really not sure why, but at this point i will use the sql server agent job to run the dtexec command line. Any idea why? Please feel free to let us know. BTW the command line is like this: dtexec /FILE "E:\SSIS packages deployment\ssistest4\deployedpackage\Package.dtsx" /DECRYPT yourpwd /CHECKPOINTING OFF /REPORTING EWCDI thx, Ted.