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

Get the InputGlobalVariables from a different package

I've been tasked with figuring out how to alter the version_id of an ExecutePackageTask programatically from a separate DTS package.

The ultimate goal is to have a "utility" DTS pack that
iterates through all the child packages within a given parent package, checks the systems tables to get the latest version_id, then assigns the latest versionid to the ExecutePackageTask. We feel this beats the heck out of opening the parent, opening each ExecutePackageTask, running through the randomly ordered list of Package Names and clicking on the updated package when a child package changes or a program is migrated to a new server.

My approach was to open the parent pack from an activex script in the utility DTS package, then iterate through the task collection looking only at ExecutePackage tasks, then check the versionid against the appropriate row in the sysdtspackages table and update the child package version_id if necessary.

I've only gotten as far as looking at the ExecutePackageTask properties and I'm stumped . . . I'm told that the object doesn't support the property or method, yet you can do this manually through a disconnected edit! Any thoughts?

Here's the code so far:
Function Main()

'create the target parent package object
Set objPackage = CreateObject("DTS.Package2")

'load the target parent package
objPackage.LoadFromSQLServer "PAMB","dts" ,"dts", DTSSQLStgFlag_UseTrustedConnection, , , ,"TESTME2"

'Create the task object
Set objTask = objPackage.Tasks

'Iterate through the tasks
For each objTask in objPackage.Tasks
if left(objTask.Name,29) = "DTSTask_DTSExecutePackageTask" then
msgbox objTask.Description
msgbox objTask.Versionid
end if
Next

Main = DTSTaskExecResult_Success

End Function

Thanks in advance,

PamClearly there are too many projects in my head right now . . .