SSIS ODBC Data Extract

Question

SSIS ODBC Data Extract

Dave Repton

SSC Eights!

Points: 811
More actions
April 16, 2008 at 5:32 am

#181408

Hi all,
stuck again !!
The situation is that I am trying to extract information via ODBC from a couple of systems we have.
I have the drivers installed on my machine and have added DSNs for each system. In SSIS I have added them in the server explorer and can view all the details through this.
To extract the information I have added the DSN as .Net Providers\Odbc Data Providers which when tested via the Test Connection Button test successfully.
I have then added a Data Flow Task within which I have added a DataReader Source. Here is where my problems begin...
I set Connection Manager in the Connection Managers tab and the enter a simple SELECT * FROM in the sqlCommand custom property.
I can now see the columns mapping, but when I try to okay this I get a message to the effect...
Component is already connected. The component needs to be disconnected before attempting to connect it.
and I am unable to ok the box to complete adding the connection.
I have tried the same with S2K and it all appears to work fine, so I am sure I am missing something in SSIS...
Any help or advice greatly appreciated...
D ;o)

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Connection Managers are not connections, they serve connections. So, by default they will connect to a data source more than once. You may have an ODBC source that is not happy with this.

Try setting the "RetainSameConnection" property on your connection manager to True.

Dave Repton SSC Eights! Points: 811 More actions · Answer 2

Thanks for the sharp reply Michael,

I have tried amending the connection manager properties but this didn;t have any effect....I have also tried adding the DSN as a data source in the solution explorer and referencing it this way, but again with no joy...

Any other suggestions or work arounds much appreciated...

D 🙁

Paul Williams SSCommitted Points: 1910 More actions · Answer 3

What data source type is your ODBC connection being made too , Oracle, DB2 ?

Are you using 32-bit or 64-bit servers ?

There is a white paper written called 'SSISConnectivity.doc' which may be of help. It certainly provides in-depth detail of connectivity in SSIS including ODBC.

Paul R Williams.

Dave Repton SSC Eights! Points: 811 More actions · Answer 4

Thanks for that Paul,

all 32bit, extracting from a system designed by Kerridge, called Kestrel, using the ODBC provided by them - I've taken a read of the white paper but couldn't see anything obvious that I've missed.

I also have a need to pull from Sybase via ODBC which I have got up and running fine - so have a feeling that the driver I have for the Kerridge product may need updating and have a call in to them to investigate.

Dave

Tab Alleman SSCommitted Points: 1590 More actions · Answer 5

I don't know if this will help, but I had the same error on an OLEDB Source component, and I just fixed it by deleting the component and creating a new one, including creating a NEW Connection Manager for the component (trying to use the existing one just brought me back to the same error).

Hope this helps.

Dave Repton SSC Eights! Points: 811 More actions · Answer 6

Thanks for the thought - have tried starting again from scratch a couple of time - including removing the driver and reinstalling that !!!

But to no avail...

kasi viswanadham Valued Member Points: 61 More actions · Answer 7

Hi All,

Can any one help me out how to add .NET Providers\Odbc Data Provider to my SSIS Connection Manger's list.

Thanks in advance

Viswanadh.

Dave Repton SSC Eights! Points: 811 More actions · Answer 8

Hi Viswanadh,

probably best to create a new thread instead of posting on the back of an old one...much more likely to get a response that way...:P

YuriyB Valued Member Points: 69 More actions · Answer 9

Dave,

I am a bit late commenting on this forum, but i ran into a problem bringing data from Sybase and i saw your comment that you were successful. Please help...

I am using ODBC connection and have no issues bringing one table at a time, however i need 100+ tables to bring over. How can i avoid recreating the same process 100+ times and change the SQL to a new table.

Thank you,

Yuriy.

vvidkish Valued Member Points: 65 More actions · Answer 10

Old Hand, you can download Kerridge ODBC at this website

http://www.kcml.com/download/download.html

But your license file should contain entries enabling you to use the ODBC KISAM driver

jianying.li SSC Rookie Points: 35 More actions · Answer 11

Hi D,

I have exactly the same problem as you described below. But I could not find the solution. How did you address it?

Thanks.

Jaying

Hi all,

stuck again !!

The situation is that I am trying to extract information via ODBC from a couple of systems we have.

I have the drivers installed on my machine and have added DSNs for each system. In SSIS I have added them in the server explorer and can view all the details through this.

To extract the information I have added the DSN as .Net Providers\Odbc Data Providers which when tested via the Test Connection Button test successfully.

I have then added a Data Flow Task within which I have added a DataReader Source. Here is where my problems begin...

I set Connection Manager in the Connection Managers tab and the enter a simple SELECT * FROM in the sqlCommand custom property.

I can now see the columns mapping, but when I try to okay this I get a message to the effect...

Component is already connected. The component needs to be disconnected before attempting to connect it.

and I am unable to ok the box to complete adding the connection.

I have tried the same with S2K and it all appears to work fine, so I am sure I am missing something in SSIS...

Any help or advice greatly appreciated...

david.repton SSC Enthusiast Points: 118 More actions · Answer 12

Hi ya - in the end I had to continue to use DTS packages on the new box - the only other option that worked was to set up Kestrel as a linked server and then query with the OPENROWSET command...not ideal...

The final solution stored the simple extracts as DTS to populate a staging area, but then used SSIS for the control flow and transforms required.

D

faisalmsc_cs SSC Rookie Points: 37 More actions · Answer 13

Hi all,

if any one needs details about Kerridge ODBC connectivity in SSIS contact me. faisalmsc_cs@yahoo.com

jeff122877 Mr or Mrs. 500 Points: 525 More actions · Answer 14

This error popped up for me just a minute ago as well. All I did to fix it was change the DelayValidation to True on the connection manager AND the data source adapter.

Note, I'm using SQL SSIS 2008 with an ADO NET Source

Jeff.