SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Programmatically Creating SSIS Package in C# Exception from HRESULT: 0xC020801C...


Programmatically Creating SSIS Package in C# Exception from HRESULT: 0xC020801C System.Runtime.InteropServices.COMException

Author
Message
arc9917
arc9917
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 6


I amworking on a C# Console Application in Visual Studio 2017 to dynamically createpackages to copy data from thousands of AS400 (DB2) tables to SQL Server 2016.Right now I’m just trying to get it to build one package and running into aproblem getting the source OLEDB connection to pull the metadata. Theconnection string and query works greatif I manually create the package in SSDT. I can also comment out thehighlighted code below and import the created package, then using SSDT I can open the source and it pullsin the metadata. Lastly, if I switch outthe connection string and query for SQL server infomationit works just fine.




Error Thrown:



System.Runtime.InteropServices.COMException


HResult=0xC020801C


Message=Exception from HRESULT: 0xC020801C


Source=AS400DynamicLoad


StackTrace:


atMicrosoft.SqlServer.Dts.Pipeline.Wrapper.IDTSDesigntimeComponent100.AcquireConnections(ObjectpTransaction)


at AS400DynamicLoad.Program.Main(String[]args) in H:\VS 2017 Projects\AS400DynamicLoad\AS400DynamicLoad\Program.cs:line123






//Variables
String AS400_connStr = "Data Source=SYSTEMNAME;User ID=user;Password=PASSWORD;Initial Catalog=CATALOGNAME;Provider=IBMDA400;";

Console.WriteLine("Building Package...");

//Create a new SSIS Package

Package package = newPackage();

package.Name= "Test";

//Add a Connection Manager to the Package, of type, OLE DB AS400

ConnectionManager connMgrAS400 = package.Connections.Add("OLEDB");

connMgrAS400.Name= "AS400 ConnMgr " + AS400;
connMgrAS400.Description= "AS400 ConnMgr " + AS400;
connMgrAS400.ConnectionString= AS400_connStr;

//Add a Data Flow Task to the Package
package.Executables.Add("STOCKTongueipelineTask");

//Get the task host wrapper, and the Data Flow task
TaskHost taskHost = package.Executables[0] as TaskHost;
MainPipe dataFlowTask = (MainPipe)taskHost.InnerObject;
taskHost.Name= "DataFlowTask";

Application app = newApplication();

//Add an OLE DB source to the data flow.

IDTSComponentMetaData100 componentSource = dataFlowTask.ComponentMetaDataCollection.New();
componentSource.ComponentClassID= "DTSAdapter.OleDbSource.5";

//Get the design time instance of the component.
CManagedComponentWrapper instance = componentSource.Instantiate();

//Initialize the component
instance.ProvideComponentProperties();

//Set source connection
componentSource.RuntimeConnectionCollection[0].ConnectionManagerID = connMgrAS400.ID;
componentSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connMgrAS400);

//Set the custom properties.
instance.SetComponentProperty("AccessMode", 2);
instance.SetComponentProperty("SqlCommand","Select * From lib.table");

//Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

//SetConnection Name
componentSource.Name= "OLEDBSource AS400";

Stringdtsxfile = "Local_File_Path";
Console.WriteLine("Saving Package...");

app.SaveToXml(dtsxfile,package, null);








leehbi
leehbi
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1732 Visits: 680
Have you considered using BIML for this task?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search