Using DataDirect ODBC connections in an SSIS Script Task

Posted by john_thurgood on 06-Aug-2014 00:23

Hello,

I'm looking to use an SSIS Script Task to dynamically build an external SSIS package using Data Flows with DataDirect ODBC source and destination connections. What references and syntax need to be applied (non-working e.g. below)?

Dim package As New Package()
        package.Name = "OdbcSourceTest"
        package.PackageType = DTSPackageType.DTSDesigner100

 Dim cmSource As ConnectionManager = package.Connections.Add("Odbc")
       cmSource.ConnectionString = "DSN=DataDirect_ODBCsource;DB=DBsource;UID=user;pwd=password"
       cmSource.Name = "OdbcSource"

DataDirect Connect - Forum

All Replies

Posted by Sumit Sarkar on 06-Aug-2014 06:35

Hi John, you can pass the ODBC connection string as follows: for any DataDirect ODBC driver:

cmSource.ConnectionString = "DSN=DataDirect_ODBCsource;UID=user;PWD=password"

What driver(s) are you looking to use?

Are you running into errors with above aporoach?

Posted by john_thurgood on 06-Aug-2014 22:06

Hi Sumit, I have Sybase ASE server source and a SQL Server 2012 destination. I'm wanting to bulk copy 90GB of data from 300 tables using the DataDirect ODBC drivers for Sybase and SQL server to complete the task. Using the drivers via the native ODBC provider in SSIS is extremely fast, however building the packages is not because the SSIS Import/Export Wizard doesn't seem to expose the provider, and a Data Flow Task can't be re-used dynamically. Is there an easier way to achieve this (and maintain performance) rather than creating SSIS Data Flow Task source/destinations for each table?

Posted by Sumit Sarkar on 07-Aug-2014 11:35

You should be able to specify the DataDirect ODBC provider in the Import/Export Wizard as".Net Framework Data Provider for Odbc"

technet.microsoft.com/.../cc879312(v=sql.105).aspx

Will that work for you?

This thread is closed