What edition of sql server do you have in your production server? Verify if you have the enterprise edition.

You may download DB2OLEDB packages here (not sure if these are the latest for each sql versions though)

DB2OLEDB (sql2005)

http://www.microsoft.com/en-us/download/details.aspx?id=15748

DB2OLEDB (sql2008)

http://www.microsoft.com/en-us/download/details.aspx?id=16978

I only use linked server so I can run distributed queries against as400 from my SSMS.

Once you have the ibm client access installed, you'll have "IBMDA400" available as your provider (In SSMS, expand Server Objects->Linked Servers->Providers) and you can use it to execute your distributed query.

--add IBMDA400 linked server

sp_addlinkedserver

@server=N'MY_AS400_SRV',

@srvproduct=N'IBM AS400 OLEDB Provider',

@provider=N'IBMDA400',

@datasrc=N'MY_AS400_SRV',

@provstr=N'Initial Catalog=MY_AS400_SRV;Default Collection=MY_LIBRARY_LIBF;Force Translate=65535;Transport Product=Client Access;Convert Date Time To Char=FALSE;Block Size=1024;'

go

sp_addlinkedsrvlogin @rmtsrvname=N'MY_LIBRARY_LIBF',

@useself='false',

@rmtuser=N'MYUSERNAME',

@rmtpassword='MYPASSWORD'

go

--Here's how you can add a linked server using DB2OLEDB:

sp_addlinkedserver

@server=N'MY_AS400_SRV',

@srvproduct=N'Microsoft OLE DB Provider for DB2',

@provider=N'DB2OLEDB',

@datasrc=N'MY_AS400_SRV',

@provstr='NetLib=TCPIP;NetAddr=MY_AS400_SRV;NetPort=446;RemoteLU=GENESIS;LocalLU=LOCAL;ModeName=QPCSUPP;User ID=MYUSERNAME;Password=MYPASSWORD;InitCat=MY_AS400_SRV;Default Schema=MY_LIBRARY_LIBF;PkgCol=MY_LIBRARY_LIBF;TPName=;Commit=YES;IsoLvl=NC;AccMode=;CCSID=37;PCCodePage=1252;BinAsChar=NO;Data Source=MY_AS400_SRV',

@catalog='MY_AS400_SRV'

GO

--to drop the linked server

sp_droplinkedsrvlogin @rmtsrvname=N'MY_AS400_SRV',@locallogin=Null

go

sp_dropserver @server=N'MY_AS400_SRV'

go

--Note: You may need to enable “Allow inprocess” for IBMDA400 provider. In management studio, go to Linked Servers->Providers->IBMDA400->Right click->Properties

Once you have your linked server in place you can run your four part name queries,..

For example:

SELECT * FROM MY_AS400_SRV.MY_AS400_SRV.MY_LIBRARY_LIBF.MYAS400FILE

Or use OPENROWSET...

--Using IBMDA400 in openrowset:

SELECT * FROM OPENROWSET

('IBMDA400','Data Source=MY_AS400_SRV;Initial Catalog=MY_AS400_SRV;USER ID=MYUSERNAME;Password=MYPASSWORD;Default Collection=MY_LIBRARY_LIBF;Force Translate=65535;Transport Product=Client Access;Convert Date Time To Char=FALSE;Block Size=1024;',

'

SELECT * FROM MY_LIBRARY_LIBF.MYAS400FILE WHERE FIELD1 = ''TEST''

'

)

--Or using DB2OLEDB in openrowset:

SELECT * FROM OPENROWSET

('DB2OLEDB','User ID=MYUSERNAME;Password=MYPASSWORD;Initial Catalog=MY_AS400_SRV;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=MY_AS400_SRV;Network Port=446;Package Collection=MY_LIBRARY_LIBF;Default Schema=MY_LIBRARY_LIBF;Process Binary as Character=False;Units of Work=RUW;Default Qualifier=MY_LIBRARY_LIBF;DBMS Platform=DB2/AS400;Defer Prepare=False;Persist Security Info=False;Connection Pooling=False;Derive Parameters=False;',

'

SELECT * FROM MY_LIBRARY_LIBF.MYAS400FILE WHERE FIELD1 = ''TEST''

'

)

When you design or execute your ssis packages, you should have either of the two providers installed on the machine. When you create a new OLEDB connection, you should see something like "IBMDA400 OLE DB Provider" or "Microsoft OLE DB Provider for DB2" depending on which package is installed.

Hope this helps.