Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS and ODBC - Is there a way to use ODBC Connections in SQL 2K5 Expand / Collapse
Author
Message
Posted Wednesday, December 12, 2007 6:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 6, 2008 11:19 AM
Points: 1, 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.
Post #432264
Posted Thursday, December 13, 2007 9:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 2:21 PM
Points: 55, Visits: 168
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.
Post #432916
Posted Friday, December 14, 2007 9:06 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 26, 2009 3:02 PM
Points: 515, 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
Post #433428
Posted Thursday, May 29, 2008 5:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 4, 2011 8:41 AM
Points: 22, 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!
Post #508307
Posted Tuesday, June 3, 2008 8:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, June 26, 2014 6:11 AM
Points: 1,330, Visits: 815
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.
Post #510677
Posted Monday, July 20, 2009 1:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 20, 2009 1:15 PM
Points: 1, 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.
Post #756136
Posted Wednesday, July 14, 2010 5:41 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 3, 2013 7:45 AM
Points: 6, Visits: 84
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
Post #952209
Posted Wednesday, August 25, 2010 6:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
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
Post #974776
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse