Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123

    Unanswered: Changing the SourceFileName in SSIS like DTS

    Hey guys im looking to be able to change the source file name in a SSIS package. The flat file comes in with a datetime stamp. In DTS I used a active x script to change the sourcefile name. What would be the way to handle it in SSIS or a better way of handling it.

    thanks,
    Jonathan

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I kludge it.

    I use xp_cmdshell and the DOS copy command to copy the original file to a static file name and I do this all in sql before the sql calls the SSIS.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Create a Foreach Loop, enumerate the folder in question...
    On the Variable Mappings option, create a new variable with the following options:
    Container: Package > Executables > Your Foreach Container
    Name: FileName
    Namespace: User
    Value Type: String

    In your Foreach loop container add a script task with the following
    Code:
    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Imports System.IO
    
    Public Class ScriptMain
    
        Public Sub Main()
    
            Dim file As New FileInfo(Dts.Variables("User::FileName").Value.ToString())
            Try
                If file.Name.ToString() = Date.Now.ToString() Then
                    file.CopyTo("C:\CorrectFile\" & file.Name.ToString(), True) 'True means "Overwrite"
                End If
    
            Catch ex As Exception
                Dts.Events.FireWarning(0, "Copy error", "Exception: " & ex.Message, Nothing, Nothing)
            End Try
    
            Dts.TaskResult = Dts.Results.Success
        End Sub
    
    End Class
    Play about with the bit highlighted to match your condition


    HTH
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh - you also have to put FileName in to the ReadOnlyVariables property of the script task
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    mine is so much less complicated. and more SQL Server 2011 compliant.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Personally I'd use an ActiveX script, but I've been assured that this is the preferred and accepted method in 2005; hence why I could look up it's implementation on one of our boxes.

    Oh, and xp_cmdshell isn't enabled on this server; hence why it was done outside of this.
    George
    Home | Blog

  7. #7
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123
    Very helpful thats what im after.
    cmd shell is disabled and policy is to keep disabled
    much appreciated....
    learning how to use the script task in SSIS WMI etc....
    thanks,
    jonathan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •