Get the Row Count

I have created a package which is transforming the data from Source OLEDB Sql Server to Destination OLEDB Sql Server programatically in VB.NET

During execution phase, I want to generate an event after every 1000 rows are transformed. As per I think, OnProgress event of IDTSEvent doesn't support this kind of a thing.

And after my Transformation is completed, I want to know how many rows have being transformed.

So how can the above two task be performed?

Add a Row Count component to your data flow to capture the number of rows.

You should be able to fire events using the FireProgress or FireInformation methods of the ComponentMetaData class. See this topic in Books Online: Raising Events in the Script Component (http://msdn2.microsoft.com/en-us/library/aa337081.aspx)

|||

I am creating the package programmatically and not using designer for it.

I have already created the package which has OLEDB and Source and Destination and added to TaskHost. For Row Count Component I think I have to create another Task Host and then add it to main pipe. But it has to be added as For Each Loop. But if I use For Each Loop then one Task Host will process at a time.

So what is the solution for this?

|||

I have added Row Count Component as Transformation (DTSTransform.RowCount) to the ComponentMetaData.

Now I get the number of Row Transformed after post execute event whereas I need to raise an event after every 1000 rows are transformed. According to me after PostExecute event of the DataFlow, the number of Rows Transformed as set into the variable by Row Count Component. So how can I raise event after every 1000 rows?

|||One way to do this would be to add a script transform to monitor the number of rows and fire the event after 1000 rows pass through.|||

I have used Script Component to transform. As I am creating this programmatically, I have copied the code generated in xml format of dtsx file to my application as a string array exactly as in the file to SourceCode property.

I have set PreCompile Property to false. So I don't need to set the BinaryCode property.

I have used code as below:

Dim DFTransform As IDTSComponentMetaData90

DFTransform = DTP.ComponentMetaDataCollection.New()

DFTransform.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

DFTransform.Name = "ScriptTransform"

DFTransform.UsesDispositions = False

Dim TransInst As CManagedComponentWrapper = DFTransform.Instantiate()

TransInst.ProvideComponentProperties()

DTP.PathCollection.New().AttachPathAndPropagateNotifications(DFSource.OutputCollection(0), DFTransform.InputCollection(0))

TransInst.SetComponentProperty("VsaProjectName", "ScriptComponent_3a1cf20682b14906bbdc971f7768e55c")

TransInst.SetComponentProperty("SourceCode", AddSourceCode(DFTransform.ComponentClassID))

TransInst.SetComponentProperty("BinaryCode", AddBinaryCode)

TransInst.SetComponentProperty("PreCompile", False)

TransInst.SetComponentProperty("UserComponentTypeName", "Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

TransInst.AcquireConnections(Nothing)

TransInst.ReinitializeMetaData()

Dim output As IDTSOutput90 = DFTransform.OutputCollection(0)

Dim outputColumn As IDTSOutputColumn90 = output.OutputColumnCollection.New()

outputColumn.Name = "myCount"

outputColumn.SetDataTypeProperties(Wrapper.DataType.DT_I4, 0, 0, 0, 0)

DFTransform.OutputCollection(0).ExternalMetadataColumnCollection.IsUsed = False

TransInst.ReleaseConnections()

But when I compile my application I get following errors:

Error Code :-1073450910

Sub Component :- Script Component [43]

Description :- System.NullReferenceException: Object reference not set to an instance of an object.

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()

Error Code :-1073450901

Sub Component :-DTS.Pipeline

Description :- "component "Script Component" (43)" failed validation and returned validation status "VS_ISBROKEN".

Is there anything which I need to do differently