How to set up a ODBC data source in SSIS to extract from Intersystems Cache database?

Question

How to set up a ODBC data source in SSIS to extract from Intersystems Cache database?

bigclick

Default port

Points: 1458
More actions
February 27, 2009 at 12:54 pm

#197564

I'm running SQL Server 2005 SSIS and trying to extract data from an Intersystems Cache database. My connection is through ODBC. I can successfully setup a Connection Manager connection by clicking New Connection, choosing ODBC in the drop down and it tests successfully.
However, in Data Flow Sources there isn't an option for an ODBC data flow. I tryed using OLE DB for the Data Flow source. In OLE DB Connection Manager drop down my ODBC Connection doesn't appear, presumably because it is ODBC and OLE DB. I created a New Connection using the drop down but the only choices I see are under Native OLE DB. When I choose OLE DB Provider for Microsoft Directory Services, it recognizes my ODBC driver name and tests successfully. However, when I attempt to use the Data Access Mode to retrieve data by using my own SQL, Build Query, Table Names or Views I get error messages which indicate tables are not found.
Should I be using a different Data Flow Source than OLE DB? My only choices are DataReader, Excel, Flat file, Raw file or XML.

Viewing 11 posts - 1 through 10 (of 10 total)

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

bigclick Default port Points: 1458 More actions · Answer 1

I found the answer. Use the DataReader Data Flow Source. Ensure the ODBC connection on your pc or server is set up correctly to access the source data.

In the SSIS Connection Manager use the ADO.NET connection which should give you a drop down for ODBC. You'll need to do this in the Connection Manager box at the lower part of the screen. I wasn't able to set up this connection directly in the DataReader itself. Once the Connection Manager is set up, go into your DataReader Data Flow Source and click on Connection Manager. Again, you can't setup the connection here, but once it is set up you'll see your ODBC connection appear on a drop down within this Data Flow Source.

Then go into Component Properties and near the bottom you'll see SQL Command. Click on the box over to the right and click on the small elipsis (spell?) to enter your SQL Command to retrieve the data.

SSIS doesn't make it easy like DTS does to access data through an ODBC connection. Maybe there's an easier way but this method works.

Bragi1 SSC Journeyman Points: 83 More actions · Answer 2

Thanks, I am trying to do the exact same thing.

Read data from Cache' in to an SSIS package.

svbarve SSC Veteran Points: 231 More actions · Answer 3

I am using SSIS 2005 with Intersystems Cache database through ODBC. I am finding the process of extraction being very slow almost taking 2 and half hour to extract 0.8 million records from cache database. Any tips on tuning?

bigclick Default port Points: 1458 More actions · Answer 4

I would first suggest finding out the indexed column(s) on the source table. Try to use these column(s) in your WHERE clause to filter the data.

You could also check your versions of SQL Server, Query Management Studio, and .net to ensure you have the most current software versions installed.

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 5

I created a connection manager using ADO.Net connection. When I use the ADO.Net source to read the data, it does not show anything.

In the source I am able to view the cache tables but not able to map it to the destination SQL Database as it errors out. Can anyone point to the DataReader if I am missing it?

Thanks!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

bigclick Default port Points: 1458 More actions · Answer 6

Are you using ODBC? ODBC will need to be configured before using SSIS.

Do you see the ODBC connection in the Connection Manager? If so, when you right click on the connection do you see the data source in the data source specification?

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 7

Thanks for the reply. I was able to connect to the Cache as well as destination database. I had to use the ADO.Net source task to view my source data. Right now I am stuck with the import of data as I do not have a query for it and the add table method I am using is pretty complex as the new system has columns spread into multiple tables with different data types.

I guess I need to work on the mapping part of it for the data to be successfully imported..let me know if you have any better ideas..appreciate your response.

Thanks!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

bigclick Default port Points: 1458 More actions · Answer 8

If your situation was similar to ours, we only had a table name in the source cache database with no other information. We did a simple query: SELECT * FROM TABLE in the Component Properties box - SQL Command line.

Then went to the Input and Output Properties box and looked at the Datareader output External Columns. This gave us the list of columns. If you click on a column it gives the datatype.

Once we had the column names we modified our sql to filter what we needed. Kind of a pain but it worked.

tom.holden SSC Enthusiast Points: 127 More actions · Answer 9

Similar - but different:

I can connect OK to Intersystems Cache with Integration Services and with Reporting Services.

However, with SSRS "Report Server Model Project" in Visual Studio, I'm trying to build a "Data Source View" from my Cache "Data Source"

Cache cannot handle the SQL syntax generated by the "Data Source View" when adding tables from the Cache "Data Source" into the "Data Source View"

In the Cache System Management Portal [Home] > [Configuration] > [SQL Settings] > [General SQL Settings]

"Support Delimited Identifiers" is set to Yes

"Identifier Translation - From:" is set to ~ `!@#$%^&*()_+-=[]\{}|;':,./?"<>

This is the Error from Visual Studio

TITLE: CacheODBC35.DLL

------------------------------

ERROR [42000] [Cache ODBC][State : 42000][Native Code 1]

[C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe]

[SQLCODE: <-1>:<Invalid SQL statement>]

[Location: <Prepare>]

[%msg: < IDENTIFIER expected, [ found^SELECT * FROM [>]

Any ideas? Frustrating. I can write reports directly against the data source, but I want to put the tables into a "Data Source View", so I can create and deploy a "Report Server Model" that contains friendly names for the fields etc...

Thanks for any ideas,

Tom

barnaby.self SSC Enthusiast Points: 171 More actions · Answer 10

Just to add an update to this, SSIS 2016 now has an ODBC Source which is found in the "Common" grouping in the SSIS toolbox 🙂 Don't forget that SSDDT is running in 32 bit in the designer and 64 bit during runtime unless you turn the "Run in 64bit" in the project properties. If you create the ODBC driver with the same name in 32bit and 64bit under the same name you will not encounter this error.