Append rowcount to filename

I wrote a SSIS package that will generate XML files from stored procedure results. The Data is move though a data flow task. I have a "Record Count" data flow item that assign the record count to a user variable named "User::rowcount".

The destination item is a script component where I have the rowcount variable as readonly. In my vb script, I am creating the filename under Public Overrides Sub PreExecute() "See line 36 of code".

When executed, it fails with the following error messages:

[Row Count [159]] Error: The variable "User::rowcount" specified by VariableName property is not a valid variable. Need a valid variable name to write to.

[DTS.Pipeline] Error: component "Row Count" (159) failed the post-execute phase and returned error code 0xC02020EE.


However, the file is still created but the default value of User::rowcount of 0 is appended to the the filename.


Anyone have any solutions to this?

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Xml

Public Class ScriptMain
    Inherits UserComponent
    Dim sw As StreamWriter
    'In addition to using the Imports System.Xml statement a reference must be added to the
    'System.Xml assembly  (Select Project-Add Reference from IDE)
    Dim xWriter As XmlTextWriter
    Dim OutputFileType As String '.csv or .xml'
    Dim Counter As Integer


    Public Overrides Sub PreExecute()

        'Read Only variables
        Dim gsPickUp As String = Me.Variables.gsPickUp 'D:\ftproot\Out\Avid'
        Dim gsPickUpFilename As String = Me.Variables.gsPickUpFilename '1_AVID_'
        Dim gsPickUpFileExtn As String = Me.Variables.gsPickUpFileExtn '.csv'
        Dim gsMemoText As String = Me.Variables.gsMemoText 'Memo Text : credit adjustment'
        Dim gsStatementText As String = Me.Variables.gsStatementText 'Statment Text : credit adjustment'
        Dim gsRunMode As String = Me.Variables.gsRunMode 'UPDATE'
        Dim gsFileType As String = Me.Variables.gsFileType
        Dim fileName As String = gsPickUp & "\" & gsPickUpFilename
        'Dim cnt As Integer = Me.Variables.rowcount
        'Dim cnt As Variable = Dts.VariableDispenser.LockOneForRead("User::rowcount", cnt)
        Counter = Variables.rowcount
        fileName = fileName & (Format(Now(), "yyyyMMddHHmm").ToString) & "_" & Counter


        'MsgBox(fileName)


        OutputFileType = gsPickUpFileExtn

        If OutputFileType = ".xml" Then
            fileName = fileName & gsPickUpFileExtn
            'xWriter = New XmlTextWriter(Me.Connections.XMLConnection.ConnectionString, Nothing)
            'xWriter.WriteStartDocument()
            'xWriter.WriteComment("Customer file parsed using script")
            'xWriter.WriteStartElement("x", "customer", "http://some.org/name")
            'xWriter.WriteAttributeString("FileName", Me.Connections.XMLConnection.ConnectionString)
            xWriter = New XmlTextWriter(fileName, Nothing)
            'xWriter.Formatting = Formatting.Indented
            'xWriter.Indentation = 0
            xWriter.WriteStartDocument()
            xWriter.WriteComment("Customer file parsed using script")
            xWriter.WriteStartElement("x", "Communication", "")
            'xWriter.WriteAttributeString("Type", gsFileType)



        End If



    End Sub




    Public Overrides Sub ParsedInput_ProcessInputRow(ByVal Row As ParsedInputBuffer)

        Counter += 1


        If OutputFileType = ".xml" Then
            xWriter.WriteString(Environment.NewLine)
            xWriter.WriteStartElement("x", "Communication", "")
            xWriter.WriteAttributeString("Type", "LogCall")


            xWriter.WriteElementString("CommunicationLog_CallId", Row.CommunicationLogCallId.ToString)
            xWriter.WriteElementString("CommunicationLogId", Row.CommunicationLogId.ToString)
            xWriter.WriteElementString("LMDealershipCode", Row.LMDealershipCode.ToString)
            xWriter.WriteElementString("MQDealershipId", Row.MQDealershipId.ToString)
            xWriter.WriteElementString("ChannelCode", Row.ChannelCode.ToString)
            xWriter.WriteElementString("VersionId", Row.VersionId.ToString)
            xWriter.WriteElementString("InstanceId", Row.InstanceId.ToString)
            xWriter.WriteElementString("SendDateTime", Row.SendDateTime.ToString)
            xWriter.WriteElementString("TimeZone", Row.TimeZone.ToString)
            xWriter.WriteElementString("CampaignType", Row.CampaignType.ToString)
            xWriter.WriteElementString("CampaignCode", Row.CampaignCode.ToString)
            xWriter.WriteElementString("CampaignName", Row.CampaignName.ToString)
            xWriter.WriteElementString("CustomerFirstName", Row.CustomerFirstName.ToString)
            xWriter.WriteElementString("CustomerLastName", Row.CustomerLastName.ToString)
            xWriter.WriteElementString("CustomerAddress1", Row.CustomerAddress1.ToString)
            xWriter.WriteElementString("CustomerAddress2", Row.CustomerAddress2.ToString)
            xWriter.WriteElementString("CustomerCity", Row.CustomerCity.ToString)
            xWriter.WriteElementString("CustomerState", Row.CustomerState.ToString)
            xWriter.WriteElementString("CustomerZip", Row.CustomerZip.ToString)
            xWriter.WriteElementString("CustomerHomePhoneNumber", Row.CustomerHomePhoneNumber.ToString)
            xWriter.WriteElementString("CustomerCellPhoneNumber", Row.CustomerCellPhoneNumber.ToString)
            xWriter.WriteElementString("CustomerEmailAddress", Row.CustomerEmailAddress.ToString)
            xWriter.WriteElementString("VIN", Row.VIN.ToString)
            xWriter.WriteElementString("DMSEventDateTime", Row.DMSEventDateTime.ToString)
            xWriter.WriteElementString("DMSEventType", Row.DMSEventType.ToString)
            xWriter.WriteElementString("DMSRetailCode", Row.DMSRetailCode.ToString)
            xWriter.WriteElementString("IsTest", Row.IsTest.ToString)
            xWriter.WriteElementString("ToPhoneNumber", Row.ToPhoneNumber.ToString)
            xWriter.WriteElementString("FromPhoneNumber", Row.FromPhoneNumber.ToString)
            xWriter.WriteElementString("RecordingFileName", Row.RecordingFileName.ToString)
            xWriter.WriteElementString("Script", Row.Script.ToString)
            xWriter.WriteElementString("ProcessDateTime", Row.ProcessDateTime.ToString)
            xWriter.WriteElementString("SourceCode", Row.SourceCode.ToString)
            xWriter.WriteElementString("SourceId1Name", Row.SourceId1Name.ToString)
            xWriter.WriteElementString("SourceId1Value", Row.SourceId1Value.ToString)
            xWriter.WriteElementString("SourceId2Name", Row.SourceId2Name.ToString)
            xWriter.WriteElementString("SourceId2Value", Row.SourceId2Value.ToString)
            xWriter.WriteElementString("SourceId3Name", Row.SourceId3Name.ToString)
            xWriter.WriteElementString("SourceId3Value", Row.SourceId3Value.ToString)


            xWriter.WriteEndElement()
            'xWriter.WriteString(Environment.NewLine)
            'Write("<br/>")

        End If




    End Sub



    Public Overrides Sub PostExecute()


        If OutputFileType = ".xml" Then
            'xWriter.WriteStartElement("RecordCount")
            'xWriter.WriteString(Me.Variables.rowcount.ToString)
            'xWriter.WriteEndElement()
            'xWriter.WriteEndElement()
            xWriter.WriteEndDocument()
            xWriter.Close()
        End If


    End Sub


End Class

Select allOpen in new window