SSIS Package runs fine in Visual Studio but fails when run manually on the deployed box

Question

I have a script task that transfers certain passed in object from one server to another. This is the code

public void Main()
        {
            try
            {
            string schemaName = Dts.Variables["$Package::SchemaName"].Value.ToString();
            string objectName = Dts.Variables["$Package::ObjectName"].Value.ToString();

            //object rawETLConnection = Dts.Connections["etl"].AcquireConnection(Dts.Transaction);
            //Server etlServer = (Server)rawETLConnection;

            Server etlServer = new Server("ciesqldeva04");
            Database etlDB;
            etlDB = etlServer.Databases["sell_side_content"];




            //object rawReportingConnection = Dts.Connections["reporting"].AcquireConnection(Dts.Transaction);
            //Server reportingServer = (Server)rawReportingConnection;
            Server reportingServer = new Server("ciesqldeva05");





                Transfer xfr;
                xfr = new Transfer(etlDB);
                xfr.DestinationServer = reportingServer.Name;
                xfr.DestinationDatabase = "sell_side_content";
                xfr.DropDestinationObjectsFirst = true;
                xfr.CopyAllObjects = false;
                xfr.CopyData = true;
                xfr.CopySchema = true;
                xfr.Options.DriAll = true;


                xfr.ObjectList.Add(etlDB.Tables[objectName, schemaName]);

                xfr.TransferData();
            }
            catch (SmoException smoex)
            {
                Dts.Events.FireError(120, " SMO - TransferObjects.dtsx", smoex.Message, "", 0);

            }
            catch (Exception ex)
            {
                Dts.Events.FireError(120,"Non SMO - TransferObjects.dtsx",ex.Message,"",0);

            } 
            Dts.TaskResult = (int)ScriptResults.Success;
        }

It works fine when I run it via Visual Studio 2012. But when I deploy it onto the box and run it via right clicking the package name in SSMS and hitting execute , it fail with this message "Transfer Object Using SMO:Error: An error occurred while transferring data. See the inner exception for details."

I also converted the script to a console application and running it on the box which I previously deployed the package to and running it via terminal and it was able to transfer successfully so it doesn't look like an issue of missing DLL's.

This is the code for that console application

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Management.Smo;
using System.Collections.Specialized;
using System.Collections;
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Server etlServer = new Server("ciesqldeva04");
            Database etlDB;
            etlDB = etlServer.Databases["sell_side_content"];




            //object rawReportingConnection = Dts.Connections["reporting"].AcquireConnection(Dts.Transaction);
            //Server reportingServer = (Server)rawReportingConnection;
            Server reportingServer = new Server("ciesqldeva05");




            try
            {
                Transfer xfr;
                xfr = new Transfer(etlDB);
                xfr.DestinationServer = reportingServer.Name;
                xfr.DestinationDatabase = "sell_side_content";
                xfr.DropDestinationObjectsFirst = true;
                xfr.CopyAllObjects = false;
                xfr.CopyData = true;
                xfr.CopySchema = true;
                xfr.Options.DriAll = true;


                xfr.ObjectList.Add(etlDB.Tables["award_sector", "people"]);

                xfr.TransferData();
            }
            catch (SmoException smoex)
            {
                Console.WriteLine("This is an SMO Exception");
                //Display the SMO exception message. 
                Console.WriteLine(smoex.Message);
                //Display the sequence of non-SMO exceptions that caused the SMO exception. 
            }
        }
    }
}

I have tried various things but to no success. Any help would be much appreciated.

P.S. I am running this on SQL Server 2012.

If you change your log message to include smoex.Inner, dots it provide any insight as to the error? When you run this package on the remote server, how are you invoking it? Is this using the project deployment model (new) or the package deployment model? — billinkc, Apr 11 '14 at 11:46
I am using the project deployment model. And I invoke it by logging into that server using SSMS, then navigating to the project under integration services catalogs->ssisdb->... and then right clicking on the package and hitting execute. — bootkick, Apr 11 '14 at 15:30
Also doing inner odes not help as the exception that is logged is not the one I throw. I get the error list using this select message_type, message from ssisdb.internal.operation_messages where operation_id = 74404 order by message_time asc — bootkick, Apr 11 '14 at 15:30

score 0 · Answer 1 · answered Feb 01 '15 at 06:26

0

Try installing the 64bit SMO libary:

http://go.microsoft.com/fwlink/?LinkID=239659&clcid=0x409

answered Feb 01 '15 at 06:26

Jason Horner

3,630
3
23
29

SSIS Package runs fine in Visual Studio but fails when run manually on the deployed box

1 Answers1