SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS and ODBC - Is there a way to use ODBC Connections in SQL 2K5


SSIS and ODBC - Is there a way to use ODBC Connections in SQL 2K5

Author
Message
Evan Sharpley
Evan Sharpley
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 9
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.
scb
scb
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 175
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.
Key DBA
Key DBA
SSC Eights!
SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)

Group: General Forum Members
Points: 871 Visits: 655
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

http://www.microsoft.com/downloads/details.aspx?FamilyID=50B97994-8453-4998-8226-FA42EC403D17&displaylang=en

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
Aubergine Blue
Aubergine Blue
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 75
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!
jim.powers
jim.powers
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1738 Visits: 858
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.
chneon
chneon
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 0
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.
dimos.thanasoulas
dimos.thanasoulas
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 88
Only for history reasons:

http://connect.microsoft.com/SQLServer/feedback/details/356575/ssis-no-longer-supports-ole-db-provider-for-odbc-import-export-wizard-etc

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
Raunak Jhawar
Raunak Jhawar
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1687 Visits: 1944
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.

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search