Post reply

Creating CSV Files on the fly

  • February 9, 2008 at 5:59 am

    #154121

    I am trying to create a package that generates a new CSV file everytime it is run. Basically I am capturing data from different sources into one CSV file using the union all transformation. I realise that in SSIS one has to create destination files and then configure the connection manager. I was wondering is there a way to create files on the fly everytime the package is run and give the new CSV file a name that includes a datetime portion????

  • February 9, 2008 at 11:52 am

    #776696

    To change the output file name at runtime define an expression in the connection manager properties used by the Flat File Destination. In the expression add the ConnectionString property and set that to a package variable ; ex. @[User::varOutputfile].

    Somewhere prior to calling that you'd set the package variable, in a Script Task for example.

  • February 9, 2008 at 3:05 pm

    #776705

    I am not sure if I understand you. When you create lets say a flat file destination and you define a connection manager using an expression the file wouldn't be recognised since it doesn't exist and SSIS throws an error. You need to create a file and set the destination to that file, how will it work?

    I am not sure how to use the script task, and I thought that we can create a new csv file everytime enabling us to keep copies of older files with a datetime portion for archiving and later reference.

    Cheers!

  • February 9, 2008 at 3:52 pm

    #776709

    That sounds right. You define a file once when setting up the connection manager, then you define an Expression that will be used to replace the ConnectionString at runtime.

    For the Script Task, that would added on the Control Flow tab; connected to the Data Flow task.

    The Script Task would execute first, setting the package variable. Then the Data Flow task would execute and use the variables value when assigning the ConnectionString property for the output files connection manager.

    In the Script Task you would add the package variable to the ReadWriteVariables setting and then click on Design Script.

    Public Sub Main()

    Dim strFilename As String

    strFilename = Today.Year.ToString + Today.Month.ToString + Today.Day.ToString

    ' varOutputFile is a package variable.

    Dts.Variables("Variable").Value = "c:\temp\" + strFilename + ".txt"

    Dts.TaskResult = Dts.Results.Success

    End Sub

  • February 9, 2008 at 11:58 pm

    #776716

    Thanks man, it works!!!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply