I have a script task that transfers some transferred objects 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 through Visual Studio 2012. But when I expand it on the field and start it by right-clicking the package name in SSMS and clicking execute, it is not with this message "Transferring an object using SMO: Error: An error occurred while transferring data. For more information, see Internal Exception. "
I also converted the script to a console application and ran it in a box in which I previously deployed the package and ran it through the terminal, and it was able to successfully pass it so that it does not look like a problem with missing DLL files.
This is the code for this 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"];
I tried different things, but to no avail. Any help would be greatly appreciated.
PS I am running this on SQL Server 2012.
source share