additional logg for DTS_DataPump Task inDTS Package

hi
any one knows how can log in a dts package othere than the defalt log .

i can work it out using DTSPackageLog.WriteStringToLog using a seperate activex task in my package .

the problem is that in my package there are more than 15 dts datapump tasks ,so to make log for each task i have to use 15 such activex task.......

so any one know how we can use DTSPackageLog.WriteStringToLog
or any thing similar in dts datapump task so that we can know the beginning and ending of individual task

abhiCheck this link (http://vyaskn.tripod.com/sql_server_dts_error_file.htm) from Vyas.

Adding ScriptTask programatically

Hi,

I'm developing tool for generating SSIS packages.

I need to add ScriptTask to package programatically and set its script code.

There is no problem for adding package, but I don't know how to set its script code, programatically.

Can anyone help me?

Thanks in advance, Borko

The trick is to use ScriptTaskCodeProvider class, PutSourceCode method.

Monicker argument is build from ScriptTask.VsaProjectName property.

It is usefull to analyze valid package XML during this action.

Regards, Borko

adding precedence to multiple files

hi all,

i have a package here which updates a DB from a flat file source.now the problem is i may get multiple files.i have used a for each loop to handle this. it takes files based on the files name9(file names has a timestamp in it).but i want to give files in order of its Creation time.

Please help me on this.i have written a script task before the for each loop and i have got the minimum creation date from all the files,i am not able to going forward from here.

does any body has an idea!!

ASAIK, the files are show in creation order in the for each loop. However, I am not sure this is gospel.

You could use a script task to load a list of files and sort the list by the file attributes. using this sorted list, you could then loop over the list using the for each loop container.

If you wanted to get clever, modify the For Each Directory example in the MS SQL 2005 example. Create your own file enumerator, ensuring the files are in the order you want.|||Generally, it does sort by filename - but there are no guarantees of this. If you want to ensure the sort order is correct, follow Crispin's advice to modify the For Each Directory sample, or use the sample posted here (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1439218&SiteID=1) by jaegd. Or read your directory, save each filename to a temp table, and use SQL sorting.

Adding Lookup Programmatically:How can I add column from reference dataset to the transformation

Hello,

I have created SSIS package programmatically, I want to add Lookup transformation,

How can I add column from reference dataset to the transformation?

I have try to add new output column but it gives me an validation error, I write following coed to add new output column to lookup.

IDTSOutputColumn90 outputColumn = this.lookup.OutputCollection[0].OutputColumnCollection.New();

outputColumn.Name = col.Name;

outputColumn.Description = "Staging table output";

outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;

outputColumn.ErrorOrTruncationOperation = "Copy Column";

outputColumn.SetDataTypeProperties(col.DataType, col.Length, col.Precision, col.Scale, col.CodePage);

Please suggest other way to add column from reference dataset to transformation output.

You have to set the reference column name to the appropriate custom property of the output column. You do not have to set any truncation or data type attributes, just get the IDTSDesigntimeComponent90 interface and call SetOutputColumnProperty on it. It should set these attributes for you (assuming your reference metadata is set to the component properly).

It should look like this:

designTimeComponent.SetOutputColumnProperty(outputID, outputColumnID, "CopyFromReferenceColumn", refColumnName);

HTH.

|||

Thanks !

Now I have added designtimeComponent property, but I got following error in setting reference table command

"Command text was not set fro the command object" ,before setting designtimeproperties it's working fine

code I used:

this.lookup = this.dataFlow.ComponentMetaDataCollection.New();

// Set component's stock properties.

this.lookup.ComponentClassID = "DTSTransform.Lookup";

this.lookup.Name = "LookupTransform";

this.lookup.Description = "Lookup component";

CManagedComponentWrapper instance = this.lookup.Instantiate();

instance.ProvideComponentProperties();

this.lookup.RuntimeConnectionCollection[0].ConnectionManagerID

= this.package.Connections["OLEDBConnectionLookup"].ID;

this.lookup.RuntimeConnectionCollection[0].ConnectionManager

= DtsConvert.ToConnectionManager90(this.package.Connections["OLEDBConnectionLookup"]);

instance.SetComponentProperty("SqlCommand", "Select id as stg_id1 from [dbo].[Temp2]");

// Attach path between the OLEDB source components Output, and the Sort Component's Input.

this.dataFlow.PathCollection.New().AttachPathAndPropagateNotifications(

this.oledbSource.OutputCollection[0], this.lookup.InputCollection[0]);

instance.AcquireConnections(null);

instance.ReinitializeMetaData();

//Ingore on failuare

this.lookup.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;

IDTSVirtualInput90 vInput1 = this.lookup.InputCollection[0].GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput1.VirtualInputColumnCollection)

{

IDTSInputColumn90 col = instance.SetUsageType(this.lookup.InputCollection[0].ID, vInput1, vColumn.LineageID, DTSUsageType.UT_READONLY);

instance.SetInputColumnProperty(this.lookup.InputCollection[0].ID, col.ID, "JoinToReferenceColumn", "stg_id1");

}

IDTSDesigntimeComponent90 designTimeComponent = this.lookup.Instantiate();

designTimeComponent.ProvideComponentProperties();

designTimeComponent.AcquireConnections(null);

designTimeComponent.ReinitializeMetaData();

IDTSOutputColumn90 newColumn = designTimeComponent.InsertOutputColumnAt(this.lookup.OutputCollection[0].ID, 0, "stg_id1", "reference column");

designTimeComponent.SetOutputColumnProperty(this.lookup.OutputCollection[0].ID, newColumn.ID, "CopyFromReferenceColumn", "stg_id1");

designTimeComponent.ReleaseConnections();

instance.ReleaseConnections();

Any suggetions,Can I set component property (Such as SqlCommand) at design time.

|||

Well, this code seems to have many issues. Let's try to fix it iteratively: for the start do not call ProvideComponentProperties twice, it will reset everything. Also, ReinitializeMetadata has to be called only once.

How many upstream columns are coming to your lookup, it seems like you are mapping all of them to stg_id1. Only one can be used for this lookup.

Try to fix these things and then post the cleaned code and the new errors you are getting. Mark the places in the code where the errors are captured.

Hopefully, we will be able to nail it in the next iteration.

|||

Thanks Bob !

U mean to say I set all component properties using 'IDTSDesigntimeComponent90' instance not using 'CManagedComponentWrapper' (right?)

Can u explain me how can i set component properties at design time

|||

Hi

Now code is working fine but it gives a Validation Error,''Input column "Name" has datatype which cannot be joined on"

I have to input columns id & name. and I want to join on single column 'id',Now what can I do to remove validation error

// Add the component to the DataFlow task.

this.lookup = this.dataFlow.ComponentMetaDataCollection.New();

// Set component's stock properties.

this.lookup.ComponentClassID = "DTSTransform.Lookup";

this.lookup.Name = "LookupTransform";

this.lookup.Description = "Lookup component";

IDTSDesigntimeComponent90 designTimeComponent = this.lookup.Instantiate();

designTimeComponent.ProvideComponentProperties();

this.lookup.RuntimeConnectionCollection[0].ConnectionManagerID

= this.package.Connections["OLEDBConnectionLookup"].ID;

this.lookup.RuntimeConnectionCollection[0].ConnectionManager

= DtsConvert.ToConnectionManager90(this.package.Connections["OLEDBConnectionLookup"]);

designTimeComponent.SetComponentProperty("SqlCommand", "Select id as stg_id1 from [dbo].[Temp2]");

// Attach path between the OLEDB source components Output, and the Sort Component's Input.

this.dataFlow.PathCollection.New().AttachPathAndPropagateNotifications(

this.oledbSource.OutputCollection[0], this.lookup.InputCollection[0]);

designTimeComponent.AcquireConnections(null);

designTimeComponent.ReinitializeMetaData();

IDTSVirtualInput90 vInput1 = this.lookup.InputCollection[0].GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput1.VirtualInputColumnCollection)

{

IDTSInputColumn90 col = designTimeComponent.SetUsageType(this.lookup.InputCollection[0].ID, vInput1, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

foreach (IDTSInputColumn90 col inthis.lookup.InputCollection[0].InputColumnCollection)

{

if (col.Name == "id")

designTimeComponent.SetInputColumnProperty(this.lookup.InputCollection[0].ID, col.ID, "JoinToReferenceColumn", "stg_id1");

}

//Ingore on failuare

this.lookup.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;

IDTSOutputColumn90 newColumn = designTimeComponent.InsertOutputColumnAt(this.lookup.OutputCollection[0].ID, 0, "stg_id1", "reference column");

designTimeComponent.SetOutputColumnProperty(this.lookup.OutputCollection[0].ID, newColumn.ID, "CopyFromReferenceColumn", "stg_id1");

designTimeComponent.ReleaseConnections();

Thanks Bob for solution, can you give me ur e-mail id so I can ask you que. directly if I have

My id omkar.pimplekar@.gmail.com

|||It looks to me that you are selecting every column, as though they all take part in the join, see

foreach (IDTSVirtualInputColumn90 vColumn in vInput1.VirtualInputColumnCollection)

{

IDTSInputColumn90 col = designTimeComponent.SetUsageType(this.lookup.InputCollection[0].ID, vInput1, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

Apply the same filter that you do in the following section, so only select the id column.

|||

Yes he is selecting every column, and it is not necessary. You can select only the column you want to join. Replace the two loops with one like this:

Code Snippet

IDTSVirtualInput90 vInput1 = this.lookup.InputCollection[0].GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput1.VirtualInputColumnCollection)

{

if (vColumn.Name == "id")

{

IDTSInputColumn90 col = designTimeComponent.SetUsageType(this.lookup.InputCollection[0].ID, vInput1, vColumn.LineageID, DTSUsageType.UT_READONLY);

designTimeComponent.SetInputColumnProperty(this.lookup.InputCollection[0].ID, col.ID, "JoinToReferenceColumn", "stg_id1");

}

}

I do not think that is causing your error though. The error is caused by data type mismatch between your "id" and "stg_id1" columns. They need to exactly match in data types.

HTH.

|||

Hi,

Thanks Bob,

Now All working fine,again thanks for quick suggetions

Adding Fuzzy Lookup Programmatically

I am trying to create a package that reads an input file or input table, does a fuzzy lookup, and outputs results to another table. I was wondering if this can be done programmatically? I have tried adding the fuzzy lookup component like this:

IDTSComponentMetaData90 FuzzyLookupDF = dataFlow.ComponentMetaDataCollection.New();

FuzzyLookupDF.ComponentClassID = "Fuzzy Lookup";

FuzzyLookupDF.Name = "FuzzyLookup";

I wondering how I can change the properties, such as what the input column is, reference table, lookup column, etc? I am not even sure if this can be done; if it can, I'd like some guidance on what the properties I would need to change are.

Thanks!

amber

Please see this topic which describes how to add and configure a data flow component programmatically - it apllies to any component (of course, each component has its own properties):
http://msdn2.microsoft.com/en-us/library/ms135932.aspx

There is also appropriate sample:
http://msdn2.microsoft.com/en-us/library/ms161541.aspx|||

I have read the examples and topics you mentioned. I am able to set certain properties of the fuzzy lookup, such as MatchIndexOptions, ReferenceTableName, MinSimilarity. But I am still not able to set certain things such as JoinToReferenceColumn and JoinType. When I set those in code like this:

instance.SetComponentProperty("JoinType", 2);

I get the following error:

System.Runtime.InteropServices.COMException was unhandled
Message="Exception from HRESULT: 0xC0204006"
Source="Microsoft.SqlServer.DTSPipelineWrap"
ErrorCode=-1071628282

I created a Fuzzy Lookup package I using the SSIS interface, and then looked at the XML format of the package. The properties I could set were part of the "Fuzzy Lookup" component. The ones I can't set are still part of the same component, but under an "OleDbConnection".

Should I still be able to set these connections of the Fuzzy Lookup component? Is there something else I need to add first? I looked at a list of available components that I can use, and I didn't see an OledbConnection in there.

|||

For the obvious reason that its not a property you can set. Only the properties that appear on the properties windows can be changed
|||

You have to study the samples more to get familiar with the design-time logic of data flow components -- how to reference upstream columns and generate output columns. The properties you are mentioning are parts of input columns. They can be set by calling SetInputColumnProperty, but only after the columns are instantiated.

"OleDbConnection" is a placeholder entry for a connection manager this component will use, it should not be related with the properties you are mentioning.

HTH,

Bob

Adding Fuzzy Lookup Programmatically

I am trying to create a package that reads an input file or input table, does a fuzzy lookup, and outputs results to another table. I was wondering if this can be done programmatically? I have tried adding the fuzzy lookup component like this:

IDTSComponentMetaData90 FuzzyLookupDF = dataFlow.ComponentMetaDataCollection.New();

FuzzyLookupDF.ComponentClassID = "Fuzzy Lookup";

FuzzyLookupDF.Name = "FuzzyLookup";

I wondering how I can change the properties, such as what the input column is, reference table, lookup column, etc? I am not even sure if this can be done; if it can, I'd like some guidance on what the properties I would need to change are.

Thanks!

amber

Please see this topic which describes how to add and configure a data flow component programmatically - it apllies to any component (of course, each component has its own properties):
http://msdn2.microsoft.com/en-us/library/ms135932.aspx

There is also appropriate sample:
http://msdn2.microsoft.com/en-us/library/ms161541.aspx|||

I have read the examples and topics you mentioned. I am able to set certain properties of the fuzzy lookup, such as MatchIndexOptions, ReferenceTableName, MinSimilarity. But I am still not able to set certain things such as JoinToReferenceColumn and JoinType. When I set those in code like this:

instance.SetComponentProperty("JoinType", 2);

I get the following error:

System.Runtime.InteropServices.COMException was unhandled
Message="Exception from HRESULT: 0xC0204006"
Source="Microsoft.SqlServer.DTSPipelineWrap"
ErrorCode=-1071628282

I created a Fuzzy Lookup package I using the SSIS interface, and then looked at the XML format of the package. The properties I could set were part of the "Fuzzy Lookup" component. The ones I can't set are still part of the same component, but under an "OleDbConnection".

Should I still be able to set these connections of the Fuzzy Lookup component? Is there something else I need to add first? I looked at a list of available components that I can use, and I didn't see an OledbConnection in there.

|||

For the obvious reason that its not a property you can set. Only the properties that appear on the properties windows can be changed
|||

You have to study the samples more to get familiar with the design-time logic of data flow components -- how to reference upstream columns and generate output columns. The properties you are mentioning are parts of input columns. They can be set by calling SetInputColumnProperty, but only after the columns are instantiated.

"OleDbConnection" is a placeholder entry for a connection manager this component will use, it should not be related with the properties you are mentioning.

HTH,

Bob

Adding Fuzzy Lookup Programmatically

I am trying to create a package that reads an input file or input table, does a fuzzy lookup, and outputs results to another table. I was wondering if this can be done programmatically? I have tried adding the fuzzy lookup component like this:

IDTSComponentMetaData90 FuzzyLookupDF = dataFlow.ComponentMetaDataCollection.New();

FuzzyLookupDF.ComponentClassID = "Fuzzy Lookup";

FuzzyLookupDF.Name = "FuzzyLookup";

I wondering how I can change the properties, such as what the input column is, reference table, lookup column, etc? I am not even sure if this can be done; if it can, I'd like some guidance on what the properties I would need to change are.

Thanks!

amber

Please see this topic which describes how to add and configure a data flow component programmatically - it apllies to any component (of course, each component has its own properties):
http://msdn2.microsoft.com/en-us/library/ms135932.aspx

There is also appropriate sample:
http://msdn2.microsoft.com/en-us/library/ms161541.aspx|||

I have read the examples and topics you mentioned. I am able to set certain properties of the fuzzy lookup, such as MatchIndexOptions, ReferenceTableName, MinSimilarity. But I am still not able to set certain things such as JoinToReferenceColumn and JoinType. When I set those in code like this:

instance.SetComponentProperty("JoinType", 2);

I get the following error:

System.Runtime.InteropServices.COMException was unhandled
Message="Exception from HRESULT: 0xC0204006"
Source="Microsoft.SqlServer.DTSPipelineWrap"
ErrorCode=-1071628282

I created a Fuzzy Lookup package I using the SSIS interface, and then looked at the XML format of the package. The properties I could set were part of the "Fuzzy Lookup" component. The ones I can't set are still part of the same component, but under an "OleDbConnection".

Should I still be able to set these connections of the Fuzzy Lookup component? Is there something else I need to add first? I looked at a list of available components that I can use, and I didn't see an OledbConnection in there.

|||

For the obvious reason that its not a property you can set. Only the properties that appear on the properties windows can be changed
|||

You have to study the samples more to get familiar with the design-time logic of data flow components -- how to reference upstream columns and generate output columns. The properties you are mentioning are parts of input columns. They can be set by calling SetInputColumnProperty, but only after the columns are instantiated.

"OleDbConnection" is a placeholder entry for a connection manager this component will use, it should not be related with the properties you are mentioning.

HTH,

Bob

Adding Foreach container programmatically and setting the enumerator properties

Hi

I have a package which contains a foreach container. Can anyone help me in setting the properties for the enumerators programmatically? I am trying to set the properties for the enumerator "ForEach File Enumerator"

The properties which i need to set are

1. Folder

2. File Type

3. Traverse Subfolder

4. Retrieve File Name

Thanks in Advance

Suganya

Can anyone help me..?Am still searching for a solution

-Suganya

Adding folders in the solution explorer?

Hi..
Isn′t it possible to create sub folders in the solution explorer?
I have it hard time to create a decent structure inside the SSIS Package folder, since I have like 30 packages in my project. Is it just me being silly or cannot this be done?
Have a nice day
/Erik

nope...

You can add folders to a solution but withing a project, everything is either in packages of misc.

|||ok, thanks for the input though

Adding delete statement in a dts package script to delete records

I am using a dts package to move tables from one server to another and will
like to know how I can add a delete statement that can delete records more
than a year old from a particular table in the source database before the dts
package moves the the tables to the destination database. Any help will be
appreciated
Bothe servers are running sql server 2000.Hi
You can add an Execute SQL Task and add workflow so that it completes before
the transformation. You may want to check out www.sqldts.com for more
information on using DTS or check out the content in Books Online
John
"Aboki" wrote:
> I am using a dts package to move tables from one server to another and will
> like to know how I can add a delete statement that can delete records more
> than a year old from a particular table in the source database before the dts
> package moves the the tables to the destination database. Any help will be
> appreciated
> Bothe servers are running sql server 2000.

Adding delete statement in a dts package script to delete records

I am using a dts package to move tables from one server to another and will
like to know how I can add a delete statement that can delete records more
than a year old from a particular table in the source database before the dt
s
package moves the the tables to the destination database. Any help will be
appreciated
Bothe servers are running sql server 2000.Hi
You can add an Execute SQL Task and add workflow so that it completes before
the transformation. You may want to check out www.sqldts.com for more
information on using DTS or check out the content in Books Online
John
"Aboki" wrote:

> I am using a dts package to move tables from one server to another and wil
l
> like to know how I can add a delete statement that can delete records more
> than a year old from a particular table in the source database before the
dts
> package moves the the tables to the destination database. Any help will be
> appreciated
> Bothe servers are running sql server 2000.