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

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

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

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

how to correctly work with two (or more) databases?

Does anybody know how to work with two (or more) databases in SQL Server 2005; or where that information can be obtained? I searched online, in BOL and asked in this forum but with no success.

information in this posting does not work; results in invalid object name (source database) and/or database does not exist (destination database) errors:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=292125&SiteID=1

this post about the database does not exist has received no replies:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=295742&SiteID=1

Of course, both databases exist and their names are valid. Both can be accessed individually thru SSMS and a VB app I am coding. The problem is when trying to work with both of them.

Any information on the subject of working with multiple datatabases and/or links to said subject would be appreciated.

Hi,

ok noone answered because this *has* to be a typo or a logical error. Try to make sure that you specified the *right* schema and that you specified the right database and make sure that you are connecting to the right server and the right instance.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

I don't understand. How I can successfully run queries on each database individually; yet, fail when trying to run a query that refers to both of them?

Here are two version of a query I tried. The first reports error: database 'tf_1' does not exist. The second reports invalid object name 'rawtf_1.dbo.TstTable_1'.

These queries are being run in SSMS. BTW, the [brackets] are used because the fieldnames are keywords in other programs that I am using.

SELECT
[DateTime],
[Open],
[High],
[Low],
[Close],
[Volume]
INTO tf_1.dbo.TstTable_1
FROM TstTable_1

SELECT
[DateTime],
[Open],
[High],
[Low],
[Close],
[Volume]
INTO tf_1.dbo.TstTable_1
FROM rawtf_1.dbo.TstTable_1

When I run sp_databases, both databases are listed; a query such as:

SELECT * FROM TstTable_1

runs successfully.

How can I discover what I am doing wrong?

|||

Are both the objects in the dbo schema, or are they in a different schema? Do you have permission to access each of the databases in question?

When you run the query that is successful, which database are you running the query in? Also, what is your default schema? To get some of this information, simply run the following queries and post the output:

select db_name()

select schema_name()

select schema_name(schema_id), name from tf_1.sys.tables where name = 'TstTable_1'

select schema_name(schema_id), name from rawtf_1.sys.tables where name = 'TstTable_1'

select name from master.sys.databases where database_id > 4

|||

First, thanks for taking the time to help me with this.

Both are in the dbo schema. I have successfully run queries on both databases individually so I assume that means I have the necessary permissions. I'm the admin on the computer and I created both databases.

In SSMS, running your queries:

select db_name() // correct dbname when run on each database

select schema_name() // dbo when run on each database

select schema_name(schema_id), name from tf_1.sys.tables where name = 'TstTable_1' // could not run this because have yet to successfully copy the table from rawtf_1.mdf

select schema_name(schema_id), name from rawtf_1.sys.tables where name = 'TstTable_1' // ran this and got the dreaded Invalid object name 'rawtf_1.sys.tables'. error message.

select name from master.sys.databases where database_id > 4 // ran this on each database and both times successfully listed both databases

|||

hmmm...well, are you by chance on a case-sensitive server? Is the database name all lower-case, or mixed-case?

Also, what compatibility level is each of these databases running under?

Something must be going on somewhere, this is definately something you should be able to (and can normally) do...hopefully we'll get it figured out...

|||

How can I tell if I'm on a case-sensitive server? I tried running the SELECT INTO query using upper case; same result database 'TF_1' does not exist. Both databases were created with all lowercase names.

Both databases have compatibility set to SQL Server 2005 (90).

I have discovered something interesting tho. In SSMS, I opened each databases properties dialog box and lined them up side by side. On the Options tab, in State options: for rawtf_1, Database State is Normal; for tf_1, there is no value. In the General tab, rawtf_1 Status is Normal; for tf_1, it is Shutdown, Normal.

|||

For the heck of it, I again tried deleting and recreating the tf_1 database. This time, fortunately, the SELECT INTO query worked. Unfortunately, I am now getting the following error message when I tried open TstTable_1 in either database in SSMS:

Class does not support aggregation (or class object is remote) (Exception from HRESULT: 0x80040110 (CLASS_E_NOGGREGATION)) (Microsoft.SqlServer.SqlTools.VSIntegration)

Other posts on the subject suggested uninstalling and reinstalling. Not a happy prospect but perhaps that is my only option at this point.

|||

Trying to uninstall and reinstall sql server only made things worse. I've moved this discussion to Setup & Upgrade; tho I'll probably be offline a day or two while I do a complete reinstallation of windows xp, programs and data. what a bummer.

http://forums.microsoft.com/MSDN/AddPost.aspx?ForumID=95&SiteID=1

|||BTW, did a complete reinstall of winxp and sql server 2005 dev edition. That just generated new problems.|||

After more than a week of aggravation and complete reinstalls of winxp and sql server 2005 dev edition, I'm back to the same situation: I cannot run a query involving two databases. Does anybody know what the problem could be? Or does anyone know of any other dbms that can easily work with two databases? Is the sql server 2000 any less aggravating than 2005?

how to correctly work with two (or more) databases?

Does anybody know how to work with two (or more) databases in SQL Server 2005; or where that information can be obtained? I searched online, in BOL and asked in this forum but with no success.

information in this posting does not work; results in invalid object name (source database) and/or database does not exist (destination database) errors:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=292125&SiteID=1

this post about the database does not exist has received no replies:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=295742&SiteID=1

Of course, both databases exist and their names are valid. Both can be accessed individually thru SSMS and a VB app I am coding. The problem is when trying to work with both of them.

Any information on the subject of working with multiple datatabases and/or links to said subject would be appreciated.

Hi,

ok noone answered because this *has* to be a typo or a logical error. Try to make sure that you specified the *right* schema and that you specified the right database and make sure that you are connecting to the right server and the right instance.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

I don't understand. How I can successfully run queries on each database individually; yet, fail when trying to run a query that refers to both of them?

Here are two version of a query I tried. The first reports error: database 'tf_1' does not exist. The second reports invalid object name 'rawtf_1.dbo.TstTable_1'.

These queries are being run in SSMS. BTW, the [brackets] are used because the fieldnames are keywords in other programs that I am using.

SELECT
[DateTime],
[Open],
[High],
[Low],
[Close],
[Volume]
INTO tf_1.dbo.TstTable_1
FROM TstTable_1

SELECT
[DateTime],
[Open],
[High],
[Low],
[Close],
[Volume]
INTO tf_1.dbo.TstTable_1
FROM rawtf_1.dbo.TstTable_1

When I run sp_databases, both databases are listed; a query such as:

SELECT * FROM TstTable_1

runs successfully.

How can I discover what I am doing wrong?

|||

Are both the objects in the dbo schema, or are they in a different schema? Do you have permission to access each of the databases in question?

When you run the query that is successful, which database are you running the query in? Also, what is your default schema? To get some of this information, simply run the following queries and post the output:

select db_name()

select schema_name()

select schema_name(schema_id), name from tf_1.sys.tables where name = 'TstTable_1'

select schema_name(schema_id), name from rawtf_1.sys.tables where name = 'TstTable_1'

select name from master.sys.databases where database_id > 4

|||

First, thanks for taking the time to help me with this.

Both are in the dbo schema. I have successfully run queries on both databases individually so I assume that means I have the necessary permissions. I'm the admin on the computer and I created both databases.

In SSMS, running your queries:

select db_name() // correct dbname when run on each database

select schema_name() // dbo when run on each database

select schema_name(schema_id), name from tf_1.sys.tables where name = 'TstTable_1' // could not run this because have yet to successfully copy the table from rawtf_1.mdf

select schema_name(schema_id), name from rawtf_1.sys.tables where name = 'TstTable_1' // ran this and got the dreaded Invalid object name 'rawtf_1.sys.tables'. error message.

select name from master.sys.databases where database_id > 4 // ran this on each database and both times successfully listed both databases

|||

hmmm...well, are you by chance on a case-sensitive server? Is the database name all lower-case, or mixed-case?

Also, what compatibility level is each of these databases running under?

Something must be going on somewhere, this is definately something you should be able to (and can normally) do...hopefully we'll get it figured out...

|||

How can I tell if I'm on a case-sensitive server? I tried running the SELECT INTO query using upper case; same result database 'TF_1' does not exist. Both databases were created with all lowercase names.

Both databases have compatibility set to SQL Server 2005 (90).

I have discovered something interesting tho. In SSMS, I opened each databases properties dialog box and lined them up side by side. On the Options tab, in State options: for rawtf_1, Database State is Normal; for tf_1, there is no value. In the General tab, rawtf_1 Status is Normal; for tf_1, it is Shutdown, Normal.

|||

For the heck of it, I again tried deleting and recreating the tf_1 database. This time, fortunately, the SELECT INTO query worked. Unfortunately, I am now getting the following error message when I tried open TstTable_1 in either database in SSMS:

Class does not support aggregation (or class object is remote) (Exception from HRESULT: 0x80040110 (CLASS_E_NOGGREGATION)) (Microsoft.SqlServer.SqlTools.VSIntegration)

Other posts on the subject suggested uninstalling and reinstalling. Not a happy prospect but perhaps that is my only option at this point.

|||

Trying to uninstall and reinstall sql server only made things worse. I've moved this discussion to Setup & Upgrade; tho I'll probably be offline a day or two while I do a complete reinstallation of windows xp, programs and data. what a bummer.

http://forums.microsoft.com/MSDN/AddPost.aspx?ForumID=95&SiteID=1

|||BTW, did a complete reinstall of winxp and sql server 2005 dev edition. That just generated new problems.|||

After more than a week of aggravation and complete reinstalls of winxp and sql server 2005 dev edition, I'm back to the same situation: I cannot run a query involving two databases. Does anybody know what the problem could be? Or does anyone know of any other dbms that can easily work with two databases? Is the sql server 2000 any less aggravating than 2005?

How to correctly update a table which values can be either inserted/updated/deleted on upd

Hi SQL fans,
I realized that I often encounter the same situation in a relation
database context, where I really don't know what to do. Here is an
example, where I have 2 tables as follow:
________________________
__________________ | PortfolioTitle
|
| Portfolio |
+---+
+--+ | tfolio_id (int)
|
| folio_id (int) |<<-PK--FK--| tfolio_idfolio (int)
|
| folio_name (varchar) | | tfolio_idtitle (int)
|--FK--PK->>[ Titles]
+--+ | tfolio_weight
(decimal(6,5)) |
+---+
Note that I also have a "Titles" tables (hence the tfolio_idtitle
link).
My problem is : When I update a portfolio, I must update all the
associated titles in it. That means that titles can be either removed
from the portfolio (a folio does not support the title anymore), added
to it (a new title is supported by the folio) or simply updated (a
title stays in the portfolio, but has its weight changed)
For example, if the portfolio #2 would contain :
[ PortfolioTitle ]
id | idFolio | idTitre | poids
1 2 1 10
2 2 2 20
3 2 3 30
and I must update the PortfolioTitle based on these values :
idFolio | idTitre | poids
2 2 20
2 3 35
2 4 40
then I should
1 ) remove the title #1 from the folio by deleting its entry in the
PortfolioTitle table
2 ) update the title #2 (weight from 30 to 35)
3 ) add the title #4 to the folio
For now, the only way I've found to do this is delete all the entries
of the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio = 2), and then insert new values for each entry based on the new given
values.
Is there a way to better manage this by detecting which value has to be
inserted/updated/deleted?
And this applies to many situation :(
If you need other examples, I can give you.
thanks a lot!
ibizaerrr...I realized the diagram is all messed up :\
here it is : http://img156.imageshack.us/img156/5096/model8al.gif|||errr...I realized the diagram is all messed up :\
here it is : http://img156.imageshack.us/img156/5096/model8al.gif|||On 16 Feb 2006 10:39:45 -0800, ibiza wrote:
>Hi SQL fans,
(snip)
>My problem is : When I update a portfolio, I must update all the
>associated titles in it. That means that titles can be either removed
>from the portfolio (a folio does not support the title anymore), added
>to it (a new title is supported by the folio) or simply updated (a
>title stays in the portfolio, but has its weight changed)
>For example, if the portfolio #2 would contain :
>[ PortfolioTitle ]
>id | idFolio | idTitre | poids
>1 2 1 10
>2 2 2 20
>3 2 3 30
>and I must update the PortfolioTitle based on these values :
>idFolio | idTitre | poids
>2 2 20
>2 3 35
>2 4 40
>then I should
>1 ) remove the title #1 from the folio by deleting its entry in the
>PortfolioTitle table
>2 ) update the title #2 (weight from 30 to 35)
>3 ) add the title #4 to the folio
Hi ibiza,
Deleting all rows, then re-inserting (as you do now) is one of the two
popular ways to acheive this. The second is (assuming that the new
values are stored in the table NewFolioTitles):
-- Step 1: DELETE rows that are no longer needed
DELETE FROM FolioTitles
WHERE NOT EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre)
-- Step 2: UPDATE rows that have been changed
UPDATE FolioTitles
SET poids = (SELECT poids
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre)
WHERE EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre
AND n.poids <> FolioTitles.poids)
-- Or, as an alternative, use the version below;
-- this is shorter and often faster, but uses
-- proprietary code and is therefor less portable
-- UPDATE f
-- SET poids = n.poids
-- FROM FolioTitles AS f
-- INNER JOIN NewFolioTitles AS n
-- ON n.idFolio = f.idFolio
-- AND n.idTitre = f.idTitre
-- WHERE n.poids <> f.poids
-- Step 3: INSERT new rows
INSERT INTO FolioTitles (idFolio, idTitre, poids)
SELECT n.idFolio, n.idTitre, i.poids
FROM NewFolioTitles AS n
LEFT JOIN FolioTitles AS f
ON f.idFolio = n.idFolio
AND f.idTitre = n.idTitre
WHERE f.idFolio IS NULL
This is just the basic outline - you should enclose it in a transaction
and add proper error handling.
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
thank you very much for your excellent reply.
Is there a big performance difference between the two methods? I guess
the only difference is that the identity numbers increment a lot
quicker with the first method of deleting a batch then reinserting a
batch, even if only a few records differ.
If I have to run this kind of update quite frequently (once/twice a
day, with thousands of records), what method would you suggest me?
Thanks again! :)
ibiza|||On 16 Feb 2006 12:54:19 -0800, ibiza wrote:
>Hi Hugo,
>thank you very much for your excellent reply.
>Is there a big performance difference between the two methods? I guess
>the only difference is that the identity numbers increment a lot
>quicker with the first method of deleting a batch then reinserting a
>batch, even if only a few records differ.
Hi ibiza,
Why would you even want to have an identity on the linking table
FolioTitles? Are there many other tables that refer to rows in this
table? I don't think so, since that would make deleting and re-inserting
rows a very bad idea!
If not, then stick to using only the natural key (FolioID + TitreID) and
leave the identity column out.
>If I have to run this kind of update quite frequently (once/twice a
>day, with thousands of records), what method would you suggest me?
If performance matters, than you should test both versions on your
hardware, with your data. That's the only way to get relevant data.
(My recommended test procedure is: clear cache; run one method a few
times, then clear cache again and run second method the same number of
times. Do thin on a test system that is as much as possible like your
production system. Compare average execution times).
--
Hugo Kornelis, SQL Server MVP|||ibiza (lambertb@.gmail.com) writes:
> thank you very much for your excellent reply.
> Is there a big performance difference between the two methods? I guess
> the only difference is that the identity numbers increment a lot
> quicker with the first method of deleting a batch then reinserting a
> batch, even if only a few records differ.
Why is there an IDENTITY columns at all? Surely a pair of
(foliotitle_idfolio, foliotitle_idtable) can only appear once in table?
Then this should be the primary key, and not any identity column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Yes, you are both right, I will remove it then.
I thought it was always a good idea to have an identity primary key on
any table.
Thanks for the tip!|||ibiza (lambertb@.gmail.com) writes:
> Yes, you are both right, I will remove it then.
> I thought it was always a good idea to have an identity primary key on
> any table.
If you ask some people, they will tell you that it is never a good idea!
Personally, I say it is a good idea when there is no useful natural key,
which often is the case for base entities: customers, financial
instruments, addresses etc. However for connection table, or tables
describing composed entities, articifical keys are usually not needed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx