The Love between IBM iSeries Access and SQL 2012

Ok, so let’s start with my first blog.
Couple of days ago, I’ve been given a task to migrate some tables from AS400 to SQL.
So first of all, you would need a connector (provider) to IBM AS400 structure. So that connector comes with IBM iSeries Access software. After quick and “select All option” installation, I thought this is it.
I started SQL Import and Export Wizard to test things out. But the error appeared:
image

After couple of minutes, I found that the .NET config file had been corrupted. Searching through .NET config, I found the problem. In file “C:WindowsMicrosoft.NETFrameworkv4.0.30319Configmachine.config” there was a unclosed line
<system.data>
<DbProviderFactories>
<add name=”IBM DB2 for i5/OS .NET Provider” invariant=”IBM.Data.DB2.iSeries” description=”.NET Framework Data Provider for i5/OS” type=”IBM.Data.DB2.iSeries.iDB2Factory, IBM.Data.DB2.iSeries, Version=12.0.0.0, Culture=neutral, PublicKeyToken=9cdb2ebfb1f93a26″ />
</DbProviderFactories>
   <DbProviderFactories/>  —-delete  this line
</system.data>
Of course you’ll probably have problems of saving that protected file. So, my advice, save that file on desktop, edit it, and then copy it back to original location.

After that, the Import Export worked as a charm. Now, next step is to create a DTX package, and schedule it to transfer data. SQL 2012 does not have Business Intelligence, but SQL Server Data Tools (further in text SSDT).

Start SSDT, create a project, create Control Flow, add Connection Managers to SQL server and to AS400.

But, I’ve stumbled on a problem here.
When working and testing DTX package from SSDT, everything works, but for some reason when you import that package into Integration Services, and start it, the password for IBM AS400 connector is missing.
Solution to this problem is:

Start SSDT with “Run as Administrator” option, design package, enter username and password into AS400 connection Manager , and select “Allow saving password”.
image

Save package. When importing to Integration 
Services you must select option “Rely on server storage and roles for access control”!

image

And that about covers the problem I’ve encountered working with these two products.

Enjoy migrating!
Smile