how to create package

i need to transfer the data from allbase (old database) to sql server 2005 .

moreover the structure is different say in allbase we may have 10 tables but now we want more tables .and some tables may have more fields than the previous table's fields for ex: productdigit (previous table name) have 2 fields and present table productline have 4 fields (while using export/import wizard those are set to null but we dont want to set null).

and i dont have knowledge on ssis now i am learning it. but i could not understand how to use those items in toolbox and how to specify datasource for odbc and all.

and please help me to create custom component if needed.

thanks in advance

B L,

I would reccomend you to go to books on line; there are some tutorials that will help you on getting started. Getting some books will help you as well. Then you can come back with specific questions/issues.

http://msdn2.microsoft.com/en-us/library/bb418492.aspx

|||

thanks for immediate response .

we have different data flow sources in toolbox but there is nothing for odbc .

If i want to connect to odbc what item should i use?

and for transferring data from source to destination which item i should use in in data flow transformation.

do i need to write any script if so can i write c# instead of vb.net wt property i need to set

|||

Few things:

Toolbox is context sensitive; it will show a diffrent set of tools depending on where you are: control flow or data flow To pump data from an ODBC source: Create a connection manager that points to your ODBC source (DNSmust be define in the machine running the package) Create a data flow task in in the control flow In Data flow task add a data reader that uses the connection manager you created add a destination component COnnect the source component to the destination component.|||

thanks for valuable suggestion .

for transferring data from old database to new one when the structure is different means

--> the number of tables / name of tables is different.

--> the structure of table is also different say the old table contains n number of columns and the new table may contain either n+ or n- number of columns and some columns in new table might not be there in old table .can i have new columns as primary?

can u guide me in that way and can you tell me which items i should use in data flow task.

|||

hello,

i have data in old database which contains many tables and i want to retrive data from two tables and insert that data into one table in the destination by SSIS package.

for example:

input output

- --

userinfo ( table ) userdetails ( table )

user_id --> id

userdata ( table2 )

name --> name

city --> city

can i do like that? please suggest me to do so .

|||

There are several ways. My first approach would be to write a query (IF the 2 source tables are in the same DB); then use that query in the source component. That way the dataflow will have all required columns right out of the source component.

Another apporach is to use 2 source componnets and then a Merge Join transformation to join the 2 data sets. This approach requires the 2 data sets to be order before hiting the merge join. Performance can also suffer with this approach since the Join takes place inside SSIS rather than in the DB engine.

|||

B L Rao wrote:

do i need to write any script if so can i write c# instead of vb.net wt property i need to set

The script task and script component only support VB.NET, not C#.

You can build your own custom SSIS component DLLs using any .NET language, but this adds a LOT of complexity.

|||

hi Rafel Salas ,

thank you for your reply by which i got some idea ,

yes we have those tables in same DB.

but still some confusion in that first approach that is where to write that query and if possible what query i can write .

so plz give me clear idea .

and second approach is clear to me .

thanks and regards

B L Rao.

|||Just write the query that gives the desired output and then you can place it directly in the OLE DB source component by changing the data access mode to "SQL Command". Another option is to place the query in a SSIS string variable and then use data access mode "SQL Command from variable"|||

hi,

The approaches are best suitable in the scenario where we can make join between two tables. Means when we have one common column on the basis of which we can create join.

But what should we do if we want to fetch data of column1, column2 from table 1 and data of col5 from table 2 without making join.

For example

table 1 has

column1

column2

column3

table 2

col1

col2

col3

col4

col5

I have tried Union All task, and Merge task but it returned 2 rows like following

null,null,col5

column1,column2,null

but output through SSIS in destination table 3 should be combination of three columns in one row like this :

column1,column2,col5 .

can you please help as soon as possible?

if possible can u please send a small package or query for this?

thanks & Regards,

Hariax

|||Try creating a dummy column (like a simple sequential row number) in each table to join on and use the Merge Join.|||

Hi,

Please help me, I am getting the 'DTS_E_UNABLETORETRIEVEMETADATA' error while I am performing bellow mentioned steps.

1. Created 'Execute Sql Task'

2. In the editor of this task, General Page ->Sql statement, I wrote create table query.

3. I tried to execute the package, and got following error.

ADDITIONAL INFORMATION:

Error at Data Flow Task 1 [SQL Server Destination [376]]: The metadata for "[dbo].[temptable]" cannot be retrieved. Make sure the object name is correct and the object exists.

Error at Data Flow Task 1 [DTS.Pipeline]: "component "SQL Server Destination" (376)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task 1 [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task 1: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

If anybody has the solution of this then please let me know.

same query is running successfully from sql server management studio.

Thanks & Regards,

Hariax J. Thoria

|||If you are creating the table for the destination in the same package, make sure the DelayValidation property on the SQL Server Destination is set to TRUE.

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 Copy Tables Across Servers?

Hi,
We got a production database that gets
tables added to it daily.
So, every single day we transfer those new
tables via DTS to five (5) other servers!
Can we accomplish the same thing using a
'Select' statement?
I found no way to use the 'select' stmt
across servers.
I'm not conversant with TSQL (yet!) and
would very much appreciate any help you can
give me.
Thank you very much
mike
Mike
*** Sent via Developersdex http://www.codecomments.com ***
"mike" <-nospam@.yahoo.com> wrote in message
news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
Did I understand you correctly? Your database schema changes DAILY? I know
nothing of your application obviously but new tables daily sounds like you
have some major issues that ought to be better fixed by a different
architecture or a better change control process.
The best way to replicate schema changes is to script them and then apply
the scripts. Or invest in a tool like RedGate SQL Compare
(http://www.red-gate.com/). Don't you do that anyway in order to test the
changes?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||I agree with David - that many new tables every day seems like a lot.
If you're just doing data transfer across servers, and need no schema
changes, try checking out Linked Servers. You can link serverA to
serverB and then do cross-server queries, such as ....
INSERT TableA
SELECT *
FROM Server2.dbo.TableA
(I think this is correct).
David Portas wrote:
> "mike" <-nospam@.yahoo.com> wrote in message
> news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
>
> Did I understand you correctly? Your database schema changes DAILY? I know
> nothing of your application obviously but new tables daily sounds like you
> have some major issues that ought to be better fixed by a different
> architecture or a better change control process.
> The best way to replicate schema changes is to script them and then apply
> the scripts. Or invest in a tool like RedGate SQL Compare
> (http://www.red-gate.com/). Don't you do that anyway in order to test the
> changes?
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
|||Again, it sounds like you have bigger issues.. But if you must do this, try
the following approach:
1. link your servers.
2. run some script like this to determine new tables
select * from sysobjects where type = 'u' and datediff(d, crdate, getdate())
< 1
3. then grab the new tables and run something like this.
select * into server2.dbname.dbo.newtable1 from server1.dbname.dbo.newtable1
You could wrap that all up in a sp or something and automate it.. Obviously
it needs a bit of refinement
"CoreyB" wrote:

> I agree with David - that many new tables every day seems like a lot.
> If you're just doing data transfer across servers, and need no schema
> changes, try checking out Linked Servers. You can link serverA to
> serverB and then do cross-server queries, such as ....
> INSERT TableA
> SELECT *
> FROM Server2.dbo.TableA
> (I think this is correct).
>
> David Portas wrote:
>
|||Hi Mike,
What are the other 5 servers used for? Would Log Shipping be an option for
you as this also handles schema changes.
Adam J Warne, MCDBA
"mike" wrote:

> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||David, There is no schema changes. The tables added are of the same
structure as the previous type tables.
Adding those tables is , unfortunately, is a requirement of an
application.
The other servers to where I need to copy to (daily); represent a
replica of the original server. They are used by other entities.
Each server got two databases. One database is being replicated to the
other 5 servers.
Could not find a way to replicate the second database since new tables
are added daily.
Thank you
Mike
*** Sent via Developersdex http://www.codecomments.com ***
|||Thank you very much Corey.....LInking servers to transfer tables across
servers would resolve this situation...as long as the current date is
always accessible in TSQL!
We are using SQL 2000 on Windows03 platform.
T
Mike
*** Sent via Developersdex http://www.codecomments.com ***