How to create an SSIS package correctly by programming?

Recently I try to create a package completely by c# code,not the ETL tool.
The package is designed to transfer data from source table to destination table.
And I wrote the following codes by referring to the msdn library,but unfortunately,it doesn't work.

//this is my code
///////////////////////////////////////////////////////////////////////
Package package = new Package();
MainPipe dataFlow = ((TaskHost)package.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe;

//add connectionmanagers to package.
ConnectionManager srcCM = package.Connections.Add("OLEDB");
srcCM.Name = "OLEDBSource";
srcCM.ConnectionString = "Data Source=srcServerName;Initial Catalog=srcDBName;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Auto Translate=False;";

ConnectionManager destCM = package.Connections.Add("OLEDB");
destCM.Name = "OLEDBDestination";
destCM.ConnectionString = "Data Source=destServerName;Initial Catalog=destDBName;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Auto Translate=False;";

//create the source component

IDTSComponentMetaData90 srcComponent = dataFlow.ComponentMetaDataCollection.New();
srcComponent.Name = "ComponetOLEDBSource";
srcComponent.ComponentClassID = "DTSAdapter.OLEDBSource.1";

// Get the design time instance of the component and initialize the component
CManagedComponentWrapper srcDesignTime = srcComponent.Instantiate();
srcDesignTime.ProvideComponentProperties();

// Specify the connection manager.
if (srcComponent.RuntimeConnectionCollection.Count > 0)
{
srcComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
}
// Set the custom properties.
srcDesignTime.SetComponentProperty("AccessMode", 0);
srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[srcTableName]");

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

// Create the destination component.
IDTSComponentMetaData90 destComponent = dataFlow.ComponentMetaDataCollection.New();
destComponent.Name = "ComponetOLEDBDestination";
destComponent.ComponentClassID = "DTSAdapter.OLEDBDestination.1";

CManagedComponentWrapper destDesignTime = destComponent.Instantiate();
destDesignTime.ProvideComponentProperties();
// Specify the connection manager.
if (destComponent.RuntimeConnectionCollection.Count > 0)
{
destComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);
}

// Set the custom properties.
destDesignTime.SetComponentProperty("AccessMode", 0);
destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[destTableName]");

// Create the path.
IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], destComponent.InputCollection[0]);

// Iterate through the inputs of the component.
foreach (IDTSInput90 input in destComponent.InputCollection)
{
// Get the virtual input column collection for the input.
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the virtual column collection.
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time instance of the component.
destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
}

DataFlowEvent dataFlowEvent = new DataFlowEvent();
package.Validate(null, null, dataFlowEvent, null);
package.Execute(null, null, dataFlowEvent, null, null);

//DataFlowEvent is derived from class DefaultEvent
///////////////////////////////////////////////////////////////////////////

and this is the error message in console

An Error occurs:
errorCode:-1073446884
subComponent:DTS.Pipeline
description:The "runtime connection "OleDbConnection" (10)" in the connection manager collection, Connections, of "component "OLE DB Source" (1)" does not have a value for the ID property. Verify that the ConnectionManagerID property of the runtime connection object has been set for the component.

It seems that the ConnectionManagerID is not assigned,but as I know it is read-only.
Or maybe there is something wrong with my code.
Is there anybody know why?
Thanks.

The error is telling you exactly what your problem is. The connection manager ID in the runtime connection isn't set. You need to set it. Your knowledge about ConnectionManagerID in IDTSRuntimeConnection90 is not accurate. It is not read-only.

if (srcComponent.RuntimeConnectionCollection.Count > 0)
{
==> srcComponent.RuntimeConnectionCollection[0].ConnectionManagerID = srcCM.ID
srcComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(srcCM);
}

HTH,
Matt|||

Thank you Matt.
But there still be an error during the validation.

An Error occurs:
errorCode:-1073450901
subComponent:DTS.Pipeline
description:"component "OLE DB Destination" (43)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

In order to fix this bug,I connect to the destination and get the metadata before
// Iterate through the inputs of the component.
,so I modify code as:

destDesignTime.AcquireConnections(null);
destDesignTime.ReinitializeMetaData();
destDesignTime.ReleaseConnections();
(I think this is not necessary)
To my disappointment,it still doesn't work.

Would you please check my code again.It is my first time to do such a thing,I am not very familiar with SSIS programming.

Thanks again.

|||Let me preface with the disclaimer that I'm speculating here.

According to the API documentation, the integer ID property of an IDTSRuntimeConnection90 object is read/write (although the ID of a ConnectionManager is not). This should be set at creation when the data flow's AutoGenerateIDForNewObjects is true (which it is by default), but must be set by the developer otherwise.

Perhaps the default value of AutoGenerateIDForNewObjects doesn't apply when one is building a package programmatically? Perhaps it's only the IDE that sets its default value to true?

In any case, it appears (as Matt first replied) that the error message you received is probably accurate and that, as it states, you need to assign IDs to your connections.|||Thanks DouglasL.
I have thoroughly resolved this problem by adding following codes:

//map external metadata to the inputcolumn
int index = 0;
foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)
{
IDTSExternalMetadataColumn90 exMetaColumn = (IDTSExternalMetadataColumn90)input.ExternalMetadataColumnCollection[index++];
inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
}
and now my code-generated SSIS works well.|||Could you please give the complete code that works for you. I think I am doing something like what you do. But my metadata collection for the input doesnt seem to get populated. Althought I seem to be doing exactly like how you do. Here is my code, if you do find anything that seems to be wrong please let me know.

. Package package = new Package();
MainPipe dataFlow = ((TaskHost)package.Executables.Add("DTS.Pipeline")).InnerObject as MainPipe;
// Add an OLEDB connection manager that is used by the component to the package.
ConnectionManager cm = package.Connections.Add("OLEDB");
cm.Name = "OLEDB ConnectionManager";
cm.ConnectionString = "Data Source=XXXX;Initial Catalog=cdfg05;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Auto Translate=False;";

cm = package.Connections.Add("FLATFILE");
cm.Name = "FLATFILE Connection Manager";
cm.ConnectionString = "C:\\Documents and Settings\\sdssfdsf\\Desktop\\Sdfdfdf_Test.csv";

IDTSComponentMetaData90 component = dataFlow.ComponentMetaDataCollection.New();
component.Name = "OLEDBSource";
component.ComponentClassID = "DTSAdapter.OleDbSource.1";
// You can also use the CLSID of the component instead of the PROGID.
//component.ComponentClassID = "{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}";
// Get the design time instance of the component.
CManagedComponentWrapper instance = component.Instantiate();
// Initialize the component
instance.ProvideComponentProperties();
// Specify the connection manager.
if (component.RuntimeConnectionCollection.Count > 0)
{
component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);
component.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[1].ID;
}
// Set the custom properties.
instance.SetComponentProperty("AccessMode", 2);
instance.SetComponentProperty("SqlCommand", "Select * from t_ggg where Zip='01757'");
// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();
// Create the destination component.
IDTSComponentMetaData90 destination = dataFlow.ComponentMetaDataCollection.New();
destination.Name = "FLATFILE";
destination.ComponentClassID = "DTSAdapter.FlatFileDestination";

CManagedComponentWrapper destDesignTime = destination.Instantiate();
destDesignTime.ProvideComponentProperties();
// Specify the connection manager.
if (destination.RuntimeConnectionCollection.Count > 0)
{
destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
destination.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[0].ID;

}
destDesignTime.AcquireConnections(null);
destDesignTime.ReinitializeMetaData();
destDesignTime.ReleaseConnections();

IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(component.OutputCollection[0], destination.InputCollection[0]);

// Iterate through the inputs of the component.
foreach (IDTSInput90 input in destination.InputCollection)
{
// Get the virtual input column collection for the input.
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the virtual column collection.
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time instance of the component.
destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

}
//map external metadata to the inputcolumn
//int index = 0;
foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)
{
IDTSExternalMetadataColumn90 exMetaColumn = input.ExternalMetadataColumnCollection.New();
exMetaColumn.CodePage = inputColumn.CodePage;
exMetaColumn.DataType = inputColumn.DataType;
exMetaColumn.Length = inputColumn.Length;
exMetaColumn.Name = inputColumn.Name;
inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
destDesignTime.MapInputColumn(input.ID, inputColumn.ID, exMetaColumn.ID);
}
}

DTSExecResult result = package.Execute();

foreach (DtsError pkgerror in package.Errors)
{
string err = pkgerror.Description;

}|||

Do you get any errors? From the code it appears that you are trying to set the flatfile connection manager to be used by the oledb source and the oledb connection manager to be used by the flat file destination since you added the oledb connection manager 1st that would be index 0 and the flat file second so that would be index 1.

Matt

|||Recently I try to create a package completely by c# code:

The package is designed to transfer and trasform a data from OLEDB Source table to OLE DB destination table.

In the package i add:
- a OleDB source componet
- a Derived Column component
- a OleDB Destination componet

I wrote the following codes but it doesn't work.
/////////////////////////////////////////
//- Create Package
Package package = new Package();
//- Aggiungo un task dataFlow
MainPipe dataFlow = ((TaskHost)package.Executables.Add("DTS.Pipeline")).InnerObject as MainPipe;
//- Add a OLEDB connection SOURCE
ConnectionManager cm = package.Connections.Add("OLEDB");
cm.Name = "OLEDB ConnectionManager";
cm.ConnectionString = "Data Source=PAGCOMPUTER;Initial Catalog=DBProva;Provider=SQLOLEDB;Integrated Security=SSPI;"; //Persist Security Info=False;Auto Translate=False;";

//- Add a OLEDB connection DESTINATION
ConnectionManager cm2 = package.Connections.Add("OLEDB");
cm2.Name = "OLEDB ConnectionManager2";
cm2.ConnectionString = "Data Source=PAGCOMPUTER;Initial Catalog=DBProva;Provider=SQLOLEDB;Integrated Security=SSPI;"; //Persist Security Info=False;Auto Translate=False;";

//- Create a OLEDB source component
IDTSComponentMetaData90 component = dataFlow.ComponentMetaDataCollection.New();
component.Name = "OLEDBSource";
component.ComponentClassID = "DTSAdapter.OleDbSource.1";

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

// Initialize the component
instance.ProvideComponentProperties();

// Specify the connection manager.
if (component.RuntimeConnectionCollection.Count > 0)
{
component.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
}
// Set the custom properties.

instance.SetComponentProperty("CommandTimeout", 0);
instance.SetComponentProperty("OpenRowset","[dbo].[FirstName-SurName]");
instance.SetComponentProperty("AlwaysUseDefaultCodePage",false);
instance.SetComponentProperty("AccessMode",0);

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

//- Create the DERIVEDCOLUMN component
IDTSComponentMetaData90 derivedColumn = dataFlow.ComponentMetaDataCollection.New();
derivedColumn.ComponentClassID = "DTSTransform.DerivedColumn.1";
derivedColumn.Name = "MyRole";
derivedColumn.Description = "Transformations Role";
derivedColumn.LocaleID = -1;
derivedColumn.UsesDispositions = true;
derivedColumn.ValidateExternalMetadata = true;
derivedColumn.Version = 0;
derivedColumn.PipelineVersion = 0;

CManagedComponentWrapper dcDesignTime = derivedColumn.Instantiate();
dcDesignTime.ProvideComponentProperties();

//- Create the DESTINATION component
IDTSComponentMetaData90 destination = dataFlow.ComponentMetaDataCollection.New();
destination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";
CManagedComponentWrapper destDesignTime = destination.Instantiate();
destDesignTime.ProvideComponentProperties();

if (destination.RuntimeConnectionCollection.Count > 0)
destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);

destDesignTime.SetComponentProperty("CommandTimeout", 0);
destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[FirstNameSurName]");
destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false);
destDesignTime.SetComponentProperty("AccessMode", 3);
destDesignTime.SetComponentProperty("FastLoadKeepIdentity", false);
destDesignTime.SetComponentProperty("FastLoadKeepNulls", false);
destDesignTime.SetComponentProperty("FastLoadOptions", "TABLOCK, CHECK_CONSTRAINTS");
destDesignTime.SetComponentProperty("FastLoadMaxInsertCommitSize",0);
// Reinitialize the metadata.
destDesignTime.AcquireConnections(null);
destDesignTime.ReinitializeMetaData();
destDesignTime.ReleaseConnections();

//- Create the PATH:
// * source->derivedColumn
IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(component.OutputCollection[0], derivedColumn.InputCollection[0]);
// * derivedColumn->destionation
IDTSPath90 path2 = dataFlow.PathCollection.New();
path2.AttachPathAndPropagateNotifications(derivedColumn.OutputCollection[0], destination.InputCollection[0]);

derivedColumn.InputCollection[0].Name = "Derived Column Input";
derivedColumn.InputCollection[0].Description = "Input to the Derived Column Transformation";
derivedColumn.InputCollection[0].HasSideEffects = false;
derivedColumn.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = false;
IDTSVirtualInput90 vInput = derivedColumn.InputCollection[0].GetVirtualInput();

IDTSInputColumn90 FirstName = null;
IDTSInputColumn90 SurName = null;

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
if (vColumn.Name.Contains("FirstName"))
{
IDTSInputColumn90 col = dcDesignTime.SetUsageType(derivedColumn.InputCollection[0].ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
FirstName = col;
}
if (vColumn.Name.Contains("SurName"))
{
IDTSInputColumn90 col = dcDesignTime.SetUsageType(derivedColumn.InputCollection[0].ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
SurName = col;
}
}

derivedColumn.OutputCollection[0].Name = "Derived Column Output";
derivedColumn.OutputCollection[0].Description = "Default Output of the Derived Column Transformation";
derivedColumn.OutputCollection[0].ExclusionGroup = 1;
derivedColumn.OutputCollection[0].DeleteOutputOnPathDetached = false;
derivedColumn.OutputCollection[0].Dangling = false;
derivedColumn.OutputCollection[0].IsErrorOut = false;
derivedColumn.OutputCollection[0].TruncationRowDisposition = DTSRowDisposition.RD_NotUsed;
derivedColumn.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;

IDTSOutputColumn90 myCol = derivedColumn.OutputCollection[0].OutputColumnCollection.New();
myCol.Name = "fullname";

myCol.ExternalMetadataColumnID = 0;
myCol.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
myCol.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;

myCol.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR,100,0,0,1252);
IDTSCustomProperty90 myProp = myCol.CustomPropertyCollection.New();
myProp.Name = "Expression";
myProp.Value = FirstName.LineageID + SurName.LineageID;

IDTSCustomProperty90 myProp2 = myCol.CustomPropertyCollection.New();
myProp2.Name = "FriendlyExpression";
myProp2.Value = FirstName.Name + SurName.Name;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;
app = new Microsoft.SqlServer.Dts.Runtime.Application();

pkgResults = package.Execute();

DTSExecResult result = package.Execute();

foreach (DtsError pkgerror in package.Errors)
{
string err = pkgerror.Description;
Console.WriteLine(err);
}
Console.WriteLine(pkgResults.ToString());
//////////////////////////////////////////////////////////////

and this is the error message in console:

"The custom property "Expression" needs to be of type VT_BSTR.

"component "Derived Column" (22)" failed validation and returned validation status "VS_ISCORRUPT".

One or more component failed validation.

There were errors during task validation.

Failure"

there is sure something wrong in the derivedcolumn propriety.
Is there anybody know where and why?
Thanks.

|||

Try to remove these rows:

IDTSCustomProperty90 myProp = myCol.CustomPropertyCollection.New();
myProp.Name = "Expression";
myProp.Value = FirstName.LineageID + SurName.LineageID;

I don't understand what there are for...

|||Can you convert the value to string and then assign to the property's Value