How to deploy to SSIS package to DB server, encountering many errors

(Note: this is my first post please bear with any mistakes in my presentation)

My issue is that I have a very simple SSIS package using the Microsoft Oracle Connector (created by Attunity) which executes fine within Visual Studio 2015 on my laptop, but throws numerous errors when deployed to either SQL Server, or when attempting to run from the command line. I've tried various links I've found here and elsewhere but I still cannot get this to work.

I am a complete newbie on these tools, I am coming from an Oracle and .NET background, so I may be missing some basics here.

Laptop setup is: Visual Studio 2015, ODP.NET 64 bit, 64-bit Oracle Client, MS Oracle Connector by Attunity 5.0 64-bit, Sql Server Data Tools for VS 2015, Sql Server Management Studio 2016 - 13.0.16106.4.

On the database server: SQL Server version is 2016 (SP1) - 13.0.4001.0 (X64) Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 , Oracle 64-bit client, SSIS, and MS Oracle Connector by Attunity 5.0 64-bit version.

I'm attempting to populate 3 SQL Server tables from 3 corresponding Oracle tables.

I have a very simple integration services project in VS 2015: 1 control flow item, with 3 data flows - all 3 run a simple SELECT against the same Oracle database with a connection manager to that DB, each one reads a different table, and populates a different SQL Server table (using the OLE Destination object), all 3 Oracle tables are in the same database instance, and SQL Server tables are in the same database.

The project runs fine from my laptop within the VS IDE, all 3 feeds run in parallel and populate the corresponding SQL server tables as expected.

However, I'm getting numerous error messages attempting to execute the package using other methods. Googling around has not helped to clarify things - I think I basically understand the error messages, but I'm unclear on how to resolve them.

  • DTEXEC - I copied my .dtsx file for the above project to the C:\TEMP folder server where SQL Server resides, and I ran the 64-bit dtexec utility as follows:

    F:\Program Files\Microsoft SQL Server\130\DTS\Binn>dtexec /file c:\temp\package.dtsx > c:\temp\dtexec_errors.txt

Please see images below for errors I'm receiving.

Errors - Part 1

Errors - Part 2

  • SSIS DB catalog - Created the SSISDB catalog under 'integration services catalogs' folder in SSMS. Within Visual Studio I right clicked the package and selected Deploy . After deployment, right clicked the package in SSMS, picked 'Execute'. Received several similar errors to what is shown in the screen shots.

Thanks in advance for any advice, pointers, assistance. Also note that from Oracle to SQL server I plan to do little to no transformation, plus this solution will eventually be used for possibly upwards of 200+ tables, if there is a better or easier way that can be automated and gets similar high performance I'm open to hearing it.

more ▼

asked Nov 29 at 03:29 PM in Default

avatar image


Verify your Oracle client is working properly on your server and configured.

6 days ago Shawn_Melton
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Please check weather you have multiple version of SQL Servers are installed .

If Yes, Please run .exe from correct version folder. As per error there are some version mismatch.

more ▼

answered Dec 04 at 01:19 PM

avatar image

59 4 6 9

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 29 at 03:29 PM

Seen: 29 times

Last Updated: 6 days ago

Copyright 2017 Redgate Software. Privacy Policy