July 7, 2010 at 1:17 pm
Hi All,
I recently completed my first C# app that builds an SSIS package programmatically. This was done using Visual C# 2010 Express for SQL Server 2008 (.NET 4.0).
I attempted to use this app in a SQL 2005 environment and it failed horribly. I recompiled the code with the SQL 2005 DLL files and classes and used .NET v3.5. So for example, I used IDTSComponentMetaData90 instead of IDTSComponentMetaData100. The code was able to compile but when I executed it in the SQL 2005 environment, I found that I can't use DtsConvert.GetExtendedInterface(). Instead, I think I need to use DtsConvert.ToConnectionManager90().
When I use DtsConvert.ToConnectionManager90() I get the following exception when trying to run:
System.Runtime.InteropServices.COMException (0xC020801C): Exception from HRESULT: 0xC020801C at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.AcquireConnections(Object pTransaction)...
Here is a snippet of the code I am using:
...
string sourceString = @"Provider=SQLNCLI10.1;Data Source=SourceInstance;Initial Catalog=SourceDatabase;Integrated Security=SSPI;Auto Translate=False;";
ConnectionManager sourceConMgr = p.Connections.Add("OLEDB");
sourceConMgr.ConnectionString = sourceString.ToString();
...
IDTSComponentMetaData90 source = dataFlowTask.ComponentMetaDataCollection.New();
source.ComponentClassID = "DTSAdapter.OleDbSource";
CManagedComponentWrapper srcDesignTime = source.Instantiate();
srcDesignTime.ProvideComponentProperties();
srcDesignTime.SetComponentProperty("AccessMode", 2);
srcDesignTime.SetComponentProperty("SqlCommand", sqlCommand);
source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(sourceConMgr);
//source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(sourceConMgr);
source.RuntimeConnectionCollection[0].ConnectionManagerID = sourceConMgr.ID;
try
{
srcDesignTime.AcquireConnections(null);
srcDesignTime.ReinitializeMetaData();
srcDesignTime.ReleaseConnections();
}
catch (Exception Z)
{
Console.WriteLine("There is a fatal problem with the source connection string. The actual error is as follows: {0}", Z);
return;
}
...
Like I said, the version of code for SQL 2008 works great - it's just the code for 2005 that is causing the trouble after compilation. I'm not sure what I'm missing here. Does anyone have any pointers or suggestions I can try? Any help is hugely appreciated!
July 8, 2010 at 5:09 pm
Turns out that this was a typo on my part in the connection string. This is why I got the error. To see this, I simply opened the package and saw the issue after attempting to test the connection. My apologies for the lame post.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply