December 12, 2007 at 6:16 am
I am new to SQL 2K5, and I am trying to transfer data from an AS/400 to SQL Server 2K5. In SQL 2K I was able to build a DTS package that looped through all 28 AS/400 boxes and grab the data that I needed using ODBC connections. I migrated the DTS package into SQL Server 2K5 using the wizard available in the Business Intel Studio. The package doesn't work, and there doesn't seem to be any option to connect to other sources using ODBC except for ADO.NET. Is there a way other than ADO.NET to use an ODBC connection? If not, how does ADO.NET work and can someone provide an example of how to use it. Any help would be greatly appreciated.
December 13, 2007 at 9:01 am
Should be able to use ODBC, although I've never tried....
1. Set up ODBC data source in windows control panel
2. Inside SSIS/visual Studio package, right click inside connection manager area, choose "New Connection..."
4. Choose ODBC, then "Add", then "New"
5. Pull your drop down down, and add the connection you setup in windows from step 1
6. Now your connection is setup.
December 14, 2007 at 9:06 am
Evan,
We too have an AS/400 and SQL 2005 environment.
You have two options, depending on what version of SQL Server 2005 you have.
SQL Server 2005 Standard Edition:
2 step connection process ...
We access our AS/400 data through a combination of a DSN that uses the IBM iSeries Access for Windows ODBC drivers, then we access the DSN that uses the Provider: Microsoft OLE DB Provider for ODBC Drivers, which gives us access to the iSeries with OPENQUERY.
SQL Server 2005 Enterprise/Developer Edition
1 step connection process ...
There is a more efficient Microsoft provided driver for use with DB2 databases. (I have read reports of the data flowing upto 75% faster.)
Provider: Microsoft OLE DB Provider for DB2
The driver is part of the Feature Pack for SQL Server 2005. It needs to be downloaded and installed from Microsoft:
Feature Pack for Microsoft SQL Server 2005 - February 2007
Scroll down the list for:
Microsoft OLEDB Provider for DB2
Package (DB2OLEDB.exe) - 8276 KB
--
This Microsoft KB article may be of use as well, it seems that documentation for communicating between MS and IBM is scarce:
INF: Configuring Data Sources for the Microsoft OLE DB Provider for DB2
http://support.microsoft.com/kb/218590
--
Once you get everything configured with the proper drivers and/or DSN entries, you should then be able to use ADO connections.
Let me know if you need more details or would like my notes on how we have things configured.
Happy T-SQL'ing.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
May 29, 2008 at 5:25 am
This is fantastic bit of information and really useful.
Could I ask an additional question.
I have a SSIS package that currently exports from DB2 AS400 into SQL 2005 - all fine.
I now need to feed data back into the DB2 AS400 (ideally using SQL statements) I know the datareader destination requires a script to be written for the destination component, but instead can I use this new
OLEDB Provider for DB2 from Microsoft instead?
for writing to DB2?
hope this makes sense!
June 3, 2008 at 8:05 am
My experience with this says yes, you can use the same provider to write back. One gotcha to watch out for though. When I first started doing this, I was getting an error during the process of writing back to the 400 and our 400 people didn't know what was going on since I had all of the necessary rights. After scouring the Internet for days, I ran across a small hidden reference to the error I was getting. Turns out, in order to write back to the 400, they had to enable logging on that particular file before the security system would allow me to write. Once they set that up, all worked as expected.
July 20, 2009 at 1:21 pm
I have DNS setup for AS/400, but when I tried to create a OLD DB connection, I could not find the "MS OLE DB Provider for ODBC". I tested my server. it does have the OLD DB provider for ODBC, but not showing up in BI. My server is 2005 standard. any idea? thanks.
July 14, 2010 at 5:41 am
Only for history reasons:
The best idea is to use the ME OLE DB Provider For DB2, BUT this work only with the dev or Ent edition of SQL.
Dimos Thanasoulas
Business Intelligence Consultant
Dynamic Integrated Solutions S.A
http://www.dissoft.eu
August 25, 2010 at 6:03 am
1. Create a ODBC connection in Control Panel.
2. Create a ODBC connection from connection manager is SSIS and map it to the custom connection created..
3. Implement a Script Task to manipulate with data stream from ODBC provider.
OR
You may get the necessary drivers/providers installed which are compatibkle with latest SSIS release.
Raunak J
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply