none
use the file name in foreach block in a script task

    Question

  • Hi,

    I have to write a SSIS package which reads the files from a  directory and then imports the data to a table. Once the particular file is read I have to move the file to a specific folder.

    I am using a foreach container in SSIS package. Data flow task works fine and loads the data to table. But my active script task fails saying "[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x1BF562A8. "


    Any idea how do I resolve this issue?

    Thanks,
    Prasanna
    Tuesday, February 09, 2010 10:13 PM

All replies

  • What is the reason in your decision to use the ActiveX Script Task?  The Script Task is a better choice as the ActiveX Script Task is deprecated.
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Tuesday, February 09, 2010 10:36 PM
    Moderator
  • Hi Prasanna,
    You can file system task to move a file.If data flow executes with out an error,then file system task move the file to another folder.
    Wednesday, February 10, 2010 12:15 AM
  • @ Phil,

    Thanks for the quick reply

    There is no specific reason for using Active X script. I am just wondering when it is reading correct file name in data flow task, why is it failing in Active X script. I am using dynamic connection string for txt file connection by using variable section of foreach container.

    My script code is follow.. Anything wrong in the code?

    '**********************************************************************

    ' Visual Basic ActiveX Script

    '************************************************************************

    Function Main()

    Dim Chemin, Fichier

    Dim fso

    Dim fldr

    Dim Folder_Name

    Dim ToMove

     

    Chemin = DTSGlobalVariables("File_Path").Value

    Fichier = DTSGlobalVariables("File_Name").Value

    Folder_Name = Chemin & year(now) & month(now) & day(now)

    Set fso = CreateObject("Scripting.FileSystemObject")

    If not (fso.FolderExists(Folder_Name)) Then

    Set fldr = fso.CreateFolder(Folder_Name)

    Else

    Set fldr = fso.GetFolder(Folder_Name)

    End If

    Set ToMove = fso.GetFile(Chemin & Fichier)

    ToMove.Move (fldr & "\" & Fichier)

    Main = DTSTaskExecResult_Success

    End Function

    @ Sai,

    Folder where I have to move the file is not constant. Can I achieve this using File System task?

    Wednesday, February 10, 2010 1:12 AM
  • You can do that by using File System task.You have to make folder path dynamic(You have go to Connection Manager properties,in Expressions set Connection String =@folderpath+@filename+@extension).
    Wednesday, February 10, 2010 7:24 AM