Append rowcount to filename

jorbroni
jorbroni used Ask the Experts™
on

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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
When you access the other variables you're using Me.Variables.VariableName but then for rowcount you're just using Variables.VariableName - could that be the problem?

L

Author

Commented:
I tried using it like the other variables with no sucess

Commented:
Is there a way you can be sure rowcount contains a value? Is it definitely a number not a string? Maybe you need to .toString() it?

fileName = fileName & (Format(Now(), "yyyyMMddHHmm").ToString) & "_" & Counter.ToString

L
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:

I tried that too with the same results. For some reason it doesn't like me calling that variable in preexecute.
Commented:
Found solution. For some reason it didn't like the variable being populated inside the dataflow task. What I did is populated the User::recordcount variable outside of the dataflow task and x in the script component
I added the variable to readonly list of variable and added the script below in my vb script.:

Dim records As String = (Me.Variables.recordcount.ToString)

fileName = fileName & (Format(Now(), "yyyyMMddHHmmssfff").ToString) & "_" & records

Commented:
This didn't work for me.  Has anyone else encountered this same thing?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial