how to create report by month from vb

hi all, i m using this code to display report of month selected from combo1
it shows records but only single of current month and moreover if there is no record of a month it shows previous result.
plz help me soon
bye

///////////
mon = Combo1.Text
cmd.CommandText = "Select * from travel"
rs2.Open cmd, , adOpenStatic, adLockReadOnly
Do While Not rs2.EOF
mo = Format(rs2.Fields("report_date"), "mmm")

If mo = mon Then

crxreport.RecordSelectionFormula = "{travel.travel_id} = " & rs2.Fields("travel_id")
CRViewer1.ReportSource = crxreport

End If

rs2.MoveNext

Loop
rs2.Close

CRViewer1.Zoom (100)
CRViewer1.ViewReportu cn do it easily by inserting the required field of that month into a temp table.

& then link the temp table to ur report

i think it will do|||thanks for response,
sorry i not understand what u mean by temp table
and how i can pass month name from combo box in vb to crystal report for displaying record of that month

waiting for reply
bye|||insert a combobox containg all the month in the form where u u load ur report .
Then make a query(for ur defined month from the combobox) 2 select the data from the original .insert these data 2 a temp table which is linked 2 the crystal report.

Then load the report.

bye.|||thanks
please tell me what is problem in my code given above
or give some sample code
to pass month name from vb form
to report
thankssql

How to create CHECK CONSTRAINT with SMO

I am using C# and SMO to create tables. Have figured out how to create the tables and foreign keys. but not how to add a Check Constraint in code as in:

ALTER TABLE [dbo].[BackgroundChecks] CHECK CONSTRAINT [FK_BackgroundChecks_BackgroundCheckTypes1]

Any help is appreciated.

Have you looked at the AddDefaultConstraint method of the Column object?|||

On second thought, you probably want to work with the ForeignKey object. Here's an example from BooksOnline that should help:

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Declare a Table object variable and reference the Employee table.
Dim tbe As Table
tbe = db.Tables("Employee", "HumanResources")
'Declare another Table object variable and reference the EmployeeAddress table.
Dim tbea As Table
tbea = db.Tables("EmployeeAddress", "HumanResources")
'Define a Foreign Key object variable by supplying the EmployeeAddress as the parent table and the foreign key name in the constructor.
Dim fk As ForeignKey
fk = New ForeignKey(tbea, "test_foreignkey")
'Add EmployeeID as the foreign key column.
Dim fkc As ForeignKeyColumn
fkc = New ForeignKeyColumn(fk, "EmployeeID", "EmployeeID")
fk.Columns.Add(fkc)
'Set the referenced table and schema.
fk.ReferencedTable = "Employee"
fk.ReferencedTableSchema = "HumanResources"
'Create the foreign key on the instance of SQL Server.
fk.Create()

|||That creates the foreign key, but not the Check Constraint.|||

OK, got it. Much simpler than it should have been given the amount of time spent.

string sqlCmd = "ALTER TABLE [" + myTable.Name + "] CHECK CONSTRAINT [" + myForeignKey.Name + "]";

myDB.ExecuteNonQuery(sqlCmd);

|||There have been a number of cases where I've used Transact-SQL to solve a problem where I couldn't find the solution within SMO, but in this case it might be worth your while to check out the AddDefaultConstraint method of the Column object, as I mentioned in my first post. The argument you'd use with the method would be the myForeignKey.Name property.

How to create apps that write code to retrieve data with foreign keys?

Hi Guys

Off late, I've grown with programming that requires more than a number of tables that has foreign keys with other tables' primary keys. It takes a really cumbersome coding to retrieve the code from another table with the other table having foreign keys. My question is, how do we program VS 2005 such that it does all the retrieval of the data from the database instead of us writing the code all by ourself?

Is it really good database technique to bend the normalcy rules and have one to two columns having redundant data?

Can anyone tell me how to write code that retrieves the foreign key data when the data from the other table is called?

Thanks

You use the Pk-FK relation to join the two tables and retrieve the columns from either of the two tables.

SELECT t1.col1, t2,col2, t2.col3

FROM Table1 t1

JOIN Table2 t2 ON t1.somecolumn = t2.somecolumn

Assuming the 'SomeColumn' here is the common column between the two tables, the above SELECT statement can be modified to retrieve columns from either of the tables. And I dont think it is cumbersome to retrieve info from another table. Your tables have to be properly normalized. This is the key. Joining too many tables in the query could also be detrimental. It depends on how well your tables are normalized.

>> Is it really good database technique to bend the normalcy rules and have one to two columns having redundant data?
It may not be a good tatabase technique to bend normalcy rules but from a practical/real world perpspective, sometimes, people do have redundant data. If you have enough justification (not just laziness or saving time or writing less code) then yes.

>>Can anyone tell me how to write code that retrieves the foreign key data when the data from the other table is called?
Sure, I did that above already.

|||

Thanks ndinakar for the reply. Code that I referred here is not the SQL query, that is not the cause of concern as I too know to retrieve the data from another table which has the Pk-Fk relationship another table with an SQL query. However, it often requires quite a lot of C# or VB code to retrieve the data from the other table when relationships are used.

Can you tell me if there are any in-built code that could be used to retrieve the data from the Pk table given the Fk table? I think I was not clear in my post and this is now clear.

Thanks

sql

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 create a stored procedure of this code (inline sql in aspx) ?

I have some SQL code as inline SQL (bad habit, I know). Now I want to convert this to an sproc, but I'm pretty much out of ideas here. The code looks like this:

string

SQL ="SELECT * FROM MyDBTable WHERE 1=1";if (txtMyField1.Text !="")

{

SQL = SQL +

" AND MyField1 = @.MyField";

}

if (txtMyField2.Text !="")

{

SQL = SQL +

" AND MyField2 LIKE '%'+ @.MyField2 + '%'";

}

if (txtMyField3.Text !="")

{

SQL = SQL +

" AND MyField3 LIKE '%' + @.MyField3 + '%'";

}

I have an search page built on ASP.NET 2.0. Based on what the user has entered to the form fields, the SQL in constructed on the fly. Since this is now inside codebehind file (aspx.cs), I want to get rid of it and move it to an sproc. But the question is how ? Some simple SQL clauses are easy to convert to an sproc but this is causing me lots of issues.

You may try this see if it is working or not:

CREATE PROCEDURE dbo.sp_Test (
@.MyField varchar(5) = null,
@.MyField2 varchar(5)= null,
@.MyField3 varchar(5)= null
)
AS

Begin
declare @.Syntax varchar(500)

SELECT @.Syntax = 'SELECT * FROM MyDBTable' +
'WHERE 1=1' +
CASE
WHEN @.MyField IS NOT NULL and @.MyField <> ''
THEN ' AND MyField1 = ''' + @.MyField + ''''
ELSE ''
END +
CASE
WHEN @.MyField2 IS NOT NULL and @.MyField2 <> ''
THEN ' AND MyField2 LIKE ''%'+ @.MyField2 + '%'''
ELSE ''
END +
CASE
WHEN @.MyField3 IS NOT NULL and @.MyField3 <> ''
THEN ' AND MyField3 LIKE ''%'+ @.MyField3 + '%'''
ELSE ''
END

EXECUTE(@.syntax)
End

You need adjust the size of the variables as request.

|||Thanks for the code. I'll try this later and let you know how it worked.|||Thanks JimmyM for your assistance, it worked !!! However you had an typo in your reply, since FROM was there twice. But this wasn't an issue.|||Sorry for the typo, glad that the solution is working for you.sql

How to create a SAPConnection in .Net Code with .NET Data Provider

In the SQL Server Technical Article Microsoft .NET Data Provider for mySAP? Business Suite

is a Code example of how to invoke a BAPI in .Net Code. First line shows how to create a Connection to SAP, but there is no information in which lib the SAPConnection type is stored:

SAPConnection con = new SAPConnection("ASHOST=<SAPserver>; CLIENT=<client>;SYSNR=<sysnr>;USER=<user>;PASSWD=<password>;LANG=<logon

language>");
con.Open();

Does somebody can help me?Assembly Microsoft.Adapter.SAP.SAPProvider|||Correct! That really helped me out! Thanks.

How to create a report parameter

I'm pretty new at this, how the heck do you edit and create parameters? Not really looking to edit code yet, just want to know how to use the interface to do this.
Open the report in Visual Studio. Go to the "Report" Menu at the top of the IDE and select Report Parameters.|||Ok, so now i have teh parameter. I want to have it be a dropdown list so they can pick from values in the table....which now have working, but the problem is these values are not grouped so each value hase many copies...is there a way to group them without having to group my dataset?

How To Count how many ROWS are returned in SQL Data Reader

My code keeps reading for another row when none are present....

There's are only unique records in the dB so far, but dupes are possible

While reader.has rows

reader.read

if value not db.null

get values

end if

end while

The program gets the row and reads it. After processing the values, it goes to thru the While and reads the next row (none), it then crashes on if value not db.null... because the value is null or no record. The dB has Values Required option on - no empty fields allowed.

Is there a way to count the ROWS returned?

Thanks.

That depends on your coding language, which one are you using ? For example the .NET Framework has the read() method which returns false if no record can be read anymore, leading to the following code:

while dr.Read()
{}

and to the fact that you won′t get an error if no rows is read anymore.

In my good old ASP ages there was something similar like rs.EOF or something, I guess there has to be be something in your coding language too.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I'm using VB.

But I'm using the WHILE there are ROWS, READ, so it shouldn't read if no more rows are present. Only ONE ROW is returned, so the WHILE statement should return FALSE on the second run of the loop and exit the LOOP because no more ROWS are present.

Moverover, it continued to READ, so perhaps the framework (NET) detected another row (empty) and reads it (while rows are present, read). Howbeit, this ROW is empty so an error is generated at the if not null.db line - but even so, it should NOT generate an error because NOT db.Null should detect that there is a Null Value and return FALSE and exit the loop.

How to correctly propogate data back to the database

I have the following code but do not know the best way to return the updated
DataTable back to the database. I believe I can use the Update method of the
Data Adapter, BUT if true, I also believe I have to 'long-hand' write code
for each individual column data that's being added.....this seems a bit
daft considering that the data is already in the disconnected data table.
Have I lost the plot?? Based on the code below, what is the correct
approach?

Note: sqlcnn is defined at module level.

Public Sub AddRequest(ByVal Eng As String, ByVal Bran As String, ByVal Req
As String) Implements IHelpSC.AddRequest

Dim dtNew As New DataTable("dtNew")
Dim drNew As DataRow
sqlda = New SqlDataAdapter("SELECT * FROM ActiveCalls", sqlcnn)
sqlda.Fill(dtNew)

'Add and populate the new datarow with
'data passed into this subroutine

drNew = dtNew.NewRow
drNew("CallTime") = Format(Date.Now, "dd MMM yyyy").ToString
drNew("Engineer") = Eng
drNew("Branch") = Bran
drNew("Request") = Req
dtNew.Rows.Add(drNew)

End Sub

Hope one of you wizards can help.

Rgds....and Merry Christmas.

PhilPhil (Phil@.nospam.com) writes:
> I have the following code but do not know the best way to return the
> updated DataTable back to the database. I believe I can use the Update
> method of the Data Adapter, BUT if true, I also believe I have to
> 'long-hand' write code for each individual column data that's being
> added.....this seems a bit daft considering that the data is already in
> the disconnected data table.

Yes and no.

First, if memory serves, you don't have to write any extra code, if
you use the default .Update method on the data adapter, but it will
include all columns. But you are better of asking about that in group
like microsoft.public.dotnet.framework.adonet that is devoted to ADO .Net.
Or, instead of asking, just conduct an experiment.

Then comes the next question, whether you actually want it. For a
application of any size, it is usually best to perform all access to
SQL Server through stored procedure. In this way, users does not have
to have direct access to the tables, but only access to the stored
procedures. This makes a big difference for the security of the database.
In many shops, the DBA will not permit anything but stored procedures
anyway.

And if you use stored procedures, it follows by necessity that if you
add another column to a query, that you will have to add it to the
SelectCommand, UpdateCommand and InsertCommand of the DataAdapater as
well. And, yes, that means some extra overhead when you add a new
column, but it is not really a big deal.

Finally some notes about your code:

> sqlda = New SqlDataAdapter("SELECT * FROM ActiveCalls", sqlcnn)

This may be just an example, but permit me to point out that "SELECT *"
should never occur in production code. It may look convenient, but it
isn't. It gives you an extra overhead of retrieving columns, you don't
nead. And it makes it very difficult to find out if a column is actually
used or not, in case you are looking into to drop a column.

> drNew("CallTime") = Format(Date.Now, "dd MMM yyyy").ToString

I don't really know what happens in the end, but you should probably
pass dates as dates. If you format dates and passes them as strings to
SQL Server, they may not be understood by SQL Server, if there are
some unexpected dateformat and langauge settings.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

It sounds like you know what you are talking about! :-).....and you raise
some excellent points. I am extremely new to db programming and just finding
my feet. The comments you make about using 'SELECT *' and stored procedures
I have read in my 'programming databases using .NET ' book. THANKS for the
confirmation. I will eventually change to using stored procedures but I am
trying to pick up the basics regarding 'connected classes' and 'disconnected
classes', Data Adapters, Data Tables, Datasets etc etc etc.

I will take a look at the suggested newsgroups, thanks for your response.

All the best,

Phil

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95CB77CF3AB42Yazorman@.127.0.0.1...
> Phil (Phil@.nospam.com) writes:
> > I have the following code but do not know the best way to return the
> > updated DataTable back to the database. I believe I can use the Update
> > method of the Data Adapter, BUT if true, I also believe I have to
> > 'long-hand' write code for each individual column data that's being
> > added.....this seems a bit daft considering that the data is already in
> > the disconnected data table.
> Yes and no.
> First, if memory serves, you don't have to write any extra code, if
> you use the default .Update method on the data adapter, but it will
> include all columns. But you are better of asking about that in group
> like microsoft.public.dotnet.framework.adonet that is devoted to ADO .Net.
> Or, instead of asking, just conduct an experiment.
> Then comes the next question, whether you actually want it. For a
> application of any size, it is usually best to perform all access to
> SQL Server through stored procedure. In this way, users does not have
> to have direct access to the tables, but only access to the stored
> procedures. This makes a big difference for the security of the database.
> In many shops, the DBA will not permit anything but stored procedures
> anyway.
> And if you use stored procedures, it follows by necessity that if you
> add another column to a query, that you will have to add it to the
> SelectCommand, UpdateCommand and InsertCommand of the DataAdapater as
> well. And, yes, that means some extra overhead when you add a new
> column, but it is not really a big deal.
> Finally some notes about your code:
> > sqlda = New SqlDataAdapter("SELECT * FROM ActiveCalls", sqlcnn)
> This may be just an example, but permit me to point out that "SELECT *"
> should never occur in production code. It may look convenient, but it
> isn't. It gives you an extra overhead of retrieving columns, you don't
> nead. And it makes it very difficult to find out if a column is actually
> used or not, in case you are looking into to drop a column.
> > drNew("CallTime") = Format(Date.Now, "dd MMM yyyy").ToString
> I don't really know what happens in the end, but you should probably
> pass dates as dates. If you format dates and passes them as strings to
> SQL Server, they may not be understood by SQL Server, if there are
> some unexpected dateformat and langauge settings.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp