September 19, 2006 at 5:00 am
Hi All,
I am trying to access data from As/400 system from SSIS.
I found that in many of the discussions, talking about creating a ADO.net Connection, then use Data reader source to access data.
I got BI Studio installed on a Windows 2000 professional system (SP 4).
But for creating an ADO.net connection, I am not able to find the driver 'Microsoft® OLE DB Provider for AS/400 and VSAM' in the drop down list.
I tried to create a DSN for this. But I am not able to find a Drive helping for it.
I got following doubts
1) Is this driver (Microsoft® OLE DB Provider for AS/400 and VSAM') available as a downloadable package? or Is it available in any other windows OS?
2) Is this the only way to connect AS/400 system from SSIS?
Can anyone please suggest me a solution for the above?
Regards
Agson Chellakudam
September 19, 2006 at 6:52 am
try using db2 driver. I worked for a while with SQL Server and importing data from an AS400 into SQL Server and I used DB2. Just an idea good luck
September 19, 2006 at 7:44 am
IBM iSeries Access for Windows should install several drivers.
What I've been doing is using the IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider in SSIS when I need to debug a statement, but for production using .Net Providers\ODBC Data Provider with a DSN using iSeries Access ODBC Driver since that worked the best, and using DataReader to pull the data.
Kinda klunky because it's hard to debug or see any tables...
September 19, 2006 at 8:37 am
Thanks a lot Jimmy and Fernado for the replies.
Can you please give me some more information?
I am not able to find db2 driver on my OS (Windows 2000 Sp 4). As well I don't have 'IBM Client Access' installed on my system.
1) Is this 'IBM Client Access for windows' free downloadable?. Can you please give more insight on this?.
2) Or to get Db2 driver, Do I have to install it seperately?.
Regards
Agson Chellakudam
September 20, 2006 at 7:59 am
Scroll down this page and see if this is what you're looking for.
Microsoft OLEDB Provider for DB2
Audience(s): Customer, Developer
I'd assume that those running the AS400 have iSeries Client Access Express and could do a selective install. We have done this so our AS400 issues a remote command to run a batch file on the SQL server to start our ETL process.
On x64 bit, you should to install both drivers. The Development Environment will use the 32 bit driver (which you will be able to see ).
GE
September 20, 2006 at 8:12 am
Hello Greg,
Thanks a lot for your information.
I have downloaded the driver from the link given by you.
Regards
Agson Chellakudam
September 27, 2006 at 12:18 pm
Anyone know of any alternative methods or sources for connecting to DB2 from SSIS?
Terry
May 15, 2007 at 1:25 pm
I am using IBM iSeries Client Access ODBC driver to get data from AS400. I created DSN, provided default userid. Within BI studio created Source connection using .net providers/odbc. It works fine when I run from BI studio. However when I deploy to SQlserver SSIS, I am getting error saying passowrd lenght is 0 etc. There is no option to save password within connection manager. How can I save password within the package? I need to schedule this package. Any help is appreciated.
May 15, 2007 at 1:43 pm
Try saving to MSDB letting it use server security. Default (I think) package is saved with sensitive information encrypted with user key.
How you are scheduling / launching the package also might play a part. If you are using SQL Agent, check the ID it's running under. I would need to have access (i.e. try running SQL Agent as you).
Try looking in Books On Line for Package Security.
Greg E
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply