sql server dts: Connecting via ODBC -- sql server dts

Connecting via ODBC

Kayda
1/28/2007 4:40:23 AM

sql server dts:

Hi:

I'm finding with SSIS 2005 that the most basic functionality--being
able to connect to datasources--is very difficult. I've tried to
connect to Sybase via ODBC and OLEDB and had different problems each
way. As a test I tried an ODBC connection to Access and it also had
problems. I've seen other posts but nothing that really helps.

Here is what I've tried:
1. I understand that now if you want to use an ODBC source, you create
an ODBC connection manager and then use a "DataReader Source" that
uses that connection manager. I tried this via ODBC to my Sybase
database, but got this error:

"Error at pump MyPump [DataReader Source xxxx]: Cannot acquire a
managed connection from the run-time connection manager"

This connection manager tested fine, and I have no problem querying
via an ODBC testing tool.

2. I tried an ODBC connection to MS Access, and got the same problem,
I think this must be a problem with SSIS. I found this link:

http://blogs.sqlxml.org/bryantlikes/archive/2004/07/28/876.aspx

but I'm not sure what he means by "Change Qualifier". Where exactly do
I put that extra text?

3. I tried an OLEDB connection with Sybase. I had setup a Sybase OLEDB
source in the Sybase OLEDB tool, and then I setup a connection manager
in SSIS, which tested fine. I created an OLEDB source in SSIS, and
used this connection manager.

When I am setting up my OLEDB source, when I click on the "Name of the
table or view" drop down list, it thinks and then gives me my list of
Sybase tables. Then when I click on anything else, like Preview or try
to go to the "columns" tab, I get an error:

Error at pump mytable[OLE DB Source[xxxx]]: Opening a rowset for
""dbo."."mytable"" failed. Check that the object exists in the
database.

Strange, since it gave me that table in the dropdown-of course it
exists!

I am happy to connect either via OLEDB or ODBC. Any advice on either
would be appreciated-this is frustrating because connecting to
datasources is the most basic thing in an ETL tool--it shouldn't be
this difficult.

Thanks,
Kayda

RE: Connecting via ODBC

Nigel Rivett
1/29/2007 4:51:00 PM

I'm connecting to sysbase via odbc and oledb - mostly oledb.
I think everything uses a query to get the data though so that might be
worth a try.

Which version of sybase?

I did have a problem that the default user enetered when creating the
datasource is the only one usable - seems like ssis doesn't pass through the
user only the password - I can't remember what this was for but probably ASE
oledb.

[quoted text, click to view]

RE: Connecting via ODBC

Nigel Rivett
1/29/2007 4:53:01 PM

And I can't get any connections to work on a 64 bit server - apparently you
have to buy a 3rd party driver. I don't think that's your problem though
because it fails when you try to test the connection in ssis.

[quoted text, click to view]

Re: Connecting via ODBC

bene.ries NO[at]SPAM web.de
1/30/2007 3:00:26 AM

Hello Kayda!
I=B4ve tried to connect to another DB via ODBC or OLE-DB for 7 weeks. In=20
my case it was an Informix 7.31 DB. Sry but f*** the ODBC Source=20
Adapter. This component is a crappy piece of software. MS themselves=20
admitted that they don=B4t fully support ODBC anymore. We had an support=20
incident and the couldn't solve this issue. Finally we figured it=20
ourselves.

So here=B4s my solution:
1=2E Create your ODBC Connection in the Data Connections of your system=20
properties.
2=2E Create a linked Server in SQL Server management studio.
3=2E Set up a OLE-DB Source Adapter in SSIS with your Target SQL Server.=20
(Connection Manager with the "Native OLE-DB/SQL Native Client"-
Driver)
4=2E Then choose "SQL Statement" as data access mode and do this kind of=20
query:

SELECT row1, row2, row3 FROM openquery
(<linkedserver>,'SELECT row1, row2, row3 FROM table WHERE condition')
WHERE condition

Re: Connecting via ODBC

Kayda
1/30/2007 10:24:05 AM

[quoted text, click to view]

Yeah, I found that using a query instead of selecting the table from=20
the drop down works with OLEDB. I also needed to update my drivers.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.