The mindset of AS/400 developers is the least of your problems.
AS/400 does not integrate happily over modern interfaces such as ADO.Net. To integrate AS/400 system with other environments, one needs to rely on crude text files or learn to live with the partial functionality that legacy ODBC and legacy ADO interfaces offer. Here are some rules of thumb for using SSIS 2005 to ETL data to and from AS/400 systems:
Dealing with the AS/400 mindset
Configuration management on AS/400 systems has never been good in the environments that I have encountered (I hasten to add that this is a people-problem, not an AS/400 problem since there has always been CVS). One vexating consequence is that library names change between environments causing conflicts and requiring additional configuration steps. The way to deal with this is to embed all your interactions with the AS/400 in SSIS expressions instead of hardcoding names of libraries and table/views in SSIS data components.
Database Driver
There are two possibilities for database drivers:
iSeries ODBC driver. This is a pain to set up and. worst of all, works intermittently with SSIS in my experience. I have foud it impossible to call stored procedures on the AS/400 from SSIS using and ODBC driver.
Native OLEDB/IBM DB2 UDB for iSeries IMBDA400 provider. Your connection string should look something like this:
- Provider=IBMDA400.DataSource.1;Data Source=SERVERU;Persist Security Info=True;Password=**********;User ID=DATMIG;Initial Catalog=SERVERU;Catalog Library List=MFLXDATA
This offers by far the best of all worlds (especially the reliability bit), even when it comes with a few restrictions.
Space padded columns
Data in columns is frequently right-padded with spaces to the column's full width. So, when pulling data from an AS/400 database, trim character strings using the SSIS TRIM()-function, lest you fill all your target systems up with space characters.
Likewise, when loading data to AS/400 implementations that expect fully-padded columns, things can break . Use the SSIS REPLICATE() and SSIS LEN()-functions to pad each column first, to keep the old beastie happy.
Loading data from the AS/400
This may appear to be work by using a simple select or table load in an OLEDB Source component, until you peer into the target table only to discover that no data was actually inserted. Naughty SSIS! You can't even trust the debugging information in SSIS!
To reliably load data from an AS/400 through SSIS, you should:
Selecting ALL the data in the AS/400 table/view
Do not filter any data at this stage. If you know that the siyrce table it large and you do not need all the content, then create a restricting view on the AS/400. In fact, you may even consider creating dedicated ETL views on the AS/400 tables that you intent to migrate from the AS/400. In my expereicen, this can abstract yourself from the all-too common AS/400 configration management issues.
Specify the AS/400 table/view in an SSIS variable
Use a variable that holds the library name and source table/view's name, separated by a variable, in the SSIS's OLEDB Source component, e.g. the variable should look like this: [library].[table]. This may seem bizarre, but is all that works. No, really. Not even a select query in a variable will work.
This also offers the advantage that you can dynamically change the library and table/view name in the variable in order to effectively switch between AS/400 environments.
SSIS Lookups with AS/400
The agricultural method described above for selecting data from AS/400 tables should suggest to you that lookups to AS/400 tables via SSIS will also not work. The solution is to download the entire AS/400 table (via a restricting view if it is big) to your local SSIS server's staging database and to perform the lookup against the local table.
Chances are that you can collect lookup data in advance of the production process and is generally a good ETL practise for performance, especially when interacting with not-so-fleet-of foot systems like AS/400's.