New post
Question
· Jun 4, 2018

Cache Database and SSMS Integration Services Catalogs (SSIS)

#Object Data Model #ODBC #SQL #Caché

Hi Everyone,

I am trying to built a SSIS package thru BIDS Visual Studio 2013. My Datasource is a InterSystems Cache Database, I wanted to Import Tables records from the Datasource to MS SQL Server 2014.

As a Sanity check. I only created one Package to import one Table to MSSQL Server to try out. The connection to the InterSystems Cache Database was successful. The DSN for the InterSystems Cache Database is created in the System for 32 and 64 Bit.

Here is where my Problem lies. I can successfully execute the SSIS Package in the BIDS Visual Studio 2013 BUT when i deploy the SSIS Package into the Integration Services Catalogs in the SSMS (SQL Server Management Studio) i get the following errors:

I tried so many Options but it just doesnt fix the Problem. It seems that the SSMS Integration Services Catalogs cannot recognize my Datasource BUT why?

Thanks for any tips and help. Thanks a lot in advanced!

Discussion (6)0
Log in or sign up to continue
Mark Anthony Manalo · Jun 12, 2018

Hi Robert!

Thank you so much for your reply and insights. But I cannot delete the 32-Bit ODBC Driver because some other System is using this DSN. I desperately need some help on this issue. It's just very frustrating why it works in visual studio 2013 but when i deploy it in the SQL Server Management Studio Integration Services Catalogs, it just doesnt work!

Any help and tips are much appreciated. Thanks in advanced!

Mark

0 0
Joost van Bruggen · Nov 8, 2018

Hi Mark Anthony,

My input is a bit late but decided to share my experience anyway. 

I was having similar issues. In my situation, I am developing SSIS packages (Visual Studio 2017) which import data  from a Cache database via ODBC. The destination is a SQL Server 2016 database. While developing in Visual Studio, the SSIS source points to a 32 bit ODBC connection and that works fine. However , after deploying to the SSIS Catalog the package was supposed to use the ODBC 64 version (which is installed on the server on which the packages are deployed) but I got errors.

However, I noticed my Visual Studio project was targeted at a SQL Server 2017 version (these values can be set in the properties of your Visual Studio project) After changing the SSIS project to a target platform of SQL Server 2016 and deploying again to the SSIS Catalog, the deployed package was able to use to 64 ODBC driver and the package executed without issues.

I know you were using different versions of VS and SQL Server than me, but you also might have target version issues.

Regards,

Joost

0 0