none
Get latest file name using SSIS script task: Error while execution

    Question

  • Hi  , I am trying to get the latest file name to process files in SSIS using vb.net code . I did set the varible in the For each loop container and assigned the variable in my file connection. I have been receiving this error msg.: Please help me out.

    Error: 0x2 at Get File Name: The script threw an exception: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

    Task failed: Get File Name

    Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    SSIS package "testpackage.dtsx" finished: Failure.

    Thanks,

    Thursday, November 11, 2010 10:39 PM

All replies

  • If you are using a script, did you set the variable to be accessible there?

    You get this message if the variable has not been registered with the component

    you can get around by either registering it upfront or using the variables dispenser:

    Dim vars As Variables Dts.VariableDispenser.LockOneForRead("User::sName", vars)


    Rgds Geoff
    Thursday, November 11, 2010 11:05 PM
  • Hey I did not get it; getFileName is my User variable:

    Code is here:

     

    Public Sub Main()

    Dim ServerName As String = Dts.Variables("SERVERNAME").Value.ToString()

    Dim filePath As String = Dts.Variables("DirSource").Value.ToString()

    Dim filePrefix As String = Dts.Variables("FilePrefix").Value.ToString()

    Dts.Variables("GetfileName").Value = System.IO.Path.GetFileName("\\" + ServerName + filePath + "\")

    Dts.TaskResult = Dts.Results.Success

    End Sub

     

    **********

    So you are saying I have to add code:

     

    Dim vars As Variables

    Dts.VariableDispenser.LockOneForRead("User::GetfileName", vars)

    So I have to assign this to  Dts.Variables("GetfileName").Value = ?

     

    Please clarify me.

     

    Thursday, November 11, 2010 11:28 PM
  • You don't have to do it in code - you can simply add it to the Read Variables in the script

    When you open a script task there is a property called "ReadOnlyVariables" and another called "ReadWriteVariables"

    You can simply add them in there by clicking on the elipses (...) and choosing them from the list


    Rgds Geoff
    Thursday, November 11, 2010 11:43 PM
  • Or yes, you can add code as you have written

    Some people prefer to do it in code, others in the gui...


    Rgds Geoff
    Thursday, November 11, 2010 11:44 PM
  • okay,

    Here are my declerations:

    Read variable: DirSource,Servername

    Write variable:GetFileName

    an vbscript code: as I mentioned above.

    But Still I have thsi error. .

     

    Friday, November 12, 2010 12:56 AM
  • If you have referenced the variables in the gui then I can only think that either a: you have a 'case' issue - variables are case sensitive so if you have declared it in your package as GetfileName then it won;t find GetFileName or b: GetFileName is somehow reserved as it is a system.IO function

    How did you declare the write variable? did you just type it or pick from the list?

    Might also need to qualify it as:

    Dts.Variables("User::GetfileName").Value = System.IO.Path.GetFileName("\\" + ServerName + filePath + "\")


    Rgds Geoff
    Friday, November 12, 2010 2:46 AM
  • Hi ,

     Icould able to add these variables correctly; In my data flow Task I am pulling the data from csv file to the database table. Now I have this error message: It is been able to take the file name from the folder but failing to open the file.

    Warning: 0x80070002 at Load TestFile_Load, file [1]: The system cannot find the file specified.

    Error: 0xC020200E at Load TestFile_Load, file  [1]: Cannot open the datafile "Testfile 20101105.csv".

    Error: 0xC004701A at LoadTestFile_Load, DTS.Pipeline: component "DestinationTable" (1) failed the pre-execute phase and returned error code 0xC020200E.

    Information: 0x4004300B at Load TestFile_Load, DTS.Pipeline: "component "DestinationTable" (539)" wrote 0 rows.

    Task failed: Load TestFile_Load

    Please advice.

    Friday, November 12, 2010 2:53 AM
  • Does "Testfile 20101105.csv" exist?

    Have you told SSIS the full path to the file?

    Is there any security on the folder that the file is in?


    Rgds Geoff
    Friday, November 12, 2010 2:58 AM
  • Yes, The Test File exist in the folder. I checked option for identifying file as : Fully Qualified.

     

    Friday, November 12, 2010 4:01 AM
  • How are you telling the connection where to look for the file?

    I presume you are using an expression? If so, please post the expression you are using. It may also be wise to check the value of your variables at runtime using either a "watch" or a messagebox to ensure that the path is fully and accurately qualified


    Rgds Geoff
    Friday, November 12, 2010 4:03 AM
  • I did set the File connection expression value to : User variable where I am storing the file name.

    When I tested by setting the message box I have the full file name in the variable with file extension. I might be missing some thing in the

    Data Flow.

    Friday, November 12, 2010 4:41 AM
  • I did able to import the file correctly. The file name in teh file connection was 0 earlier I set the file name to it . Then It executed perfectly. It has taken lot of time for me to figure it out. Thank you !

    Friday, November 12, 2010 6:11 AM
  • Hi , I still see when ever I execute package for the 1st time I have been receiving this error messade: I have to reset the File name in the File connection to the file path.  Can any one tell why I need to reset the file name ? Once I do this it executes well no matter How many times I process files. When I close application and open it again and start my execution it throws the same error message.

    Warning: 0x80070002 at Load TestFile_Load, file [1]: The system cannot find the file specified.

    Error: 0xC020200E at Load TestFile_Load, file  [1]: Cannot open the datafile "Testfile 20101105.csv".

    Error: 0xC004701A at LoadTestFile_Load, DTS.Pipeline: component "DestinationTable" (1) failed the pre-execute phase and returned error code 0xC020200E.

    Please advice.

    Saturday, November 13, 2010 2:07 PM
  • Try setting the Delay Validation property to True
    Rgds Geoff
    Sunday, November 14, 2010 10:20 PM
  • I did set it to TRUE  now ; It is working! Thank you!
    Friday, November 19, 2010 6:02 PM
  • Hi Md.k check http://plexussql.blogspot.com/2010/04/delete-oldest-file-in-folder-with-ssis.html but dont use the last FSYS object that is deleting the file

    You have to make a small modification for it

    i can email you a sample if you like , just email me at SNikkhah@live.ca

    good luck


    Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Friday, November 19, 2010 6:28 PM
  • How can i get the recent file from a folder?????

    hi

    I have created one SSIS package that takes .csv file data and insert into SQL table,

    But now I want most recently added file in that folder suppose

    My flat file connection path is : C:\Temp

    Now in temp there is my file named 'MYDATA 2011-09-08'

    now some more files are there like 'MYDATA 2011-09-15' , 'MYDATA 2011-09-17''

     

    Now 'MYDATA 2011-09-17'' is the most recent by date and i want to pickup this file how can i do this???


    Ashish Fugat (9960978134) Software Engineer
    Monday, September 19, 2011 7:49 AM
  • As a basic concept

    Use a for each loop on the folder to pick up each file at a time

    use string funcitons on the file name to extract the date

    compare the date to one stored in a variable

    if the file date is greater than the variable date, then update variable date with file date

    continue to end of loop

    the date in your variable is now the max date on file so you should be able to use it to get the file you are looking for


    Rgds Geoff
    Wednesday, September 21, 2011 3:39 AM
  • As a basic concept

    Use a for each loop on the folder to pick up each file at a time

    use string funcitons on the file name to extract the date

    compare the date to one stored in a variable

    if the file date is greater than the variable date, then update variable date with file date

    continue to end of loop

    the date in your variable is now the max date on file so you should be able to use it to get the file you are looking for


    Rgds Geoff
    Thank you can you please give me any link for this...

    Ashish Fugat (9960978134) Software Engineer
    Wednesday, September 21, 2011 5:51 AM
  • Have a look at my blog post Delete files older than a specified number of days for ideas.

    The problem with your first issue was that the GetFileName variable case was incorrect. You provided the solution to the 2nd question yourself and for the 3rd question you can check the blog post above.

    I would advise you to start a new thread for a new query. Your current thread has about 3 issues in one. You would not get proper reply if you continue this.

     


    My Blog    |      Ask Me     
    Wednesday, September 21, 2011 7:33 AM
  • Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Imports System.IO
    Imports System.Globalization

    Public Class ScriptMain

     ' The execution engine calls this method when the task executes.
     ' To access the object model, use the Dts object. Connections, variables, events,
     ' and logging features are available as static members of the Dts class.
     ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
     '
     ' To open Code and Text Editor Help, press F1.
     ' To open Object Browser, press Ctrl+Alt+J.

     Public Sub Main()
      '
      ' Add your code here
            '
            Dim strTaskName As String = "Finding file to import: "

            Dim strDirFullPath As String
            Dim strSearchPattern As String
            Dim arr_strFiles As String()

            strDirFullPath = "\\" + ReadVariable("strServer").ToString + ReadVariable("strImportDirPath").ToString
            strSearchPattern = ReadVariable("strImportFile1_SearchPattern").ToString + "." + ReadVariable("strImportFile1_FileTyp").ToString

            Dts.Events.FireInformation(0, strTaskName, "Directory being search is: " + strDirFullPath, String.Empty, 0, False)

            arr_strFiles = Directory.GetFiles(strDirFullPath, strSearchPattern, SearchOption.TopDirectoryOnly)

            If arr_strFiles.Length = 0 Then
                Dts.Events.FireInformation(0, strTaskName, "FILE NOT FOUND", String.Empty, 0, False)
                WriteVariable("boolFileFound", False)

            ElseIf arr_strFiles.Length = 1 Then
                Dts.Events.FireInformation(0, strTaskName, "FILE FOUND: " + arr_strFiles(0).ToString, String.Empty, 0, False)
                WriteVariable("strImportFile1_FullPath", arr_strFiles(0).ToString)
                WriteVariable("boolFileFound", True)

            ElseIf arr_strFiles.Length > 1 Then
                Dts.Events.FireInformation(0, strTaskName, "MULTIPLES FILES FOUND", String.Empty, 0, False)

                Dim i As Integer
                Dim strFileFullPath As String
                Dim arr_strTokens As String()
                Dim strFileDate As String
                Dim dtDate As DateTime                      'Dim arr_strFilesWithDates As Array = Array.CreateInstance(GetType(String), 0)
                Dim arr_strFilesWithDates As String() = {}
                Dim strLatestFile As String

                For i = 0 To arr_strFiles.Length - 1
                    'if the last 8 positions of the filename before the .csv ending represent a date, then add the filename to the array

                    'extract the last 8 positions of the filename before the .csv ending
                    strFileFullPath = arr_strFiles(i).ToString
                    arr_strTokens = strFileFullPath.Split(Convert.ToChar("."))
                    strFileDate = arr_strTokens(0).ToString.Substring(arr_strTokens(0).ToString.Length - 8, 8)

                    'MsgBox(strFileDate)

                    'check whether the extracted string represent a date, if so add it to the array arr_strFilesWithDates
                    If DateTime.TryParseExact(strFileDate, "yyyyMMdd", CultureInfo.InvariantCulture, DateTimeStyles.None, dtDate) Then
                        Array.Resize(arr_strFilesWithDates, arr_strFilesWithDates.Length + 1)           'Array.Resize(Of String)(CType(arr_strFilesWithDates, String()), arr_strFilesWithDates.Length + 1)
                        arr_strFilesWithDates.SetValue(arr_strFiles(i).ToString, arr_strFilesWithDates.GetUpperBound(0))
                    End If
                Next

                Array.Sort(arr_strFilesWithDates)

                Dts.Events.FireInformation(0, strTaskName, "FILE CHOSEN: " + arr_strFilesWithDates(arr_strFilesWithDates.GetUpperBound(0)).ToString, String.Empty, 0, False)
                WriteVariable("strImportFile1_FullPath", arr_strFilesWithDates(arr_strFilesWithDates.GetUpperBound(0)).ToString)
                WriteVariable("boolFileFound", True)

            End If

            Dts.TaskResult = Dts.Results.Success

        End Sub

        Private Function ReadVariable(ByVal varName As String) As Object
            Dim result As Object
            Try
                Dim vars As Variables
                Dts.VariableDispenser.LockForRead(varName)
                Dts.VariableDispenser.GetVariables(vars)
                Try
                    result = vars(varName).Value
                Catch ex As Exception
                    Throw ex
                Finally
                    vars.Unlock()
                End Try
            Catch ex As Exception
                Throw ex
            End Try

            Return result
        End Function


        Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
            Try
                Dim vars As Variables
                Dts.VariableDispenser.LockForWrite(varName)
                Dts.VariableDispenser.GetVariables(vars)
                Try
                    vars(varName).Value = varValue
                Catch ex As Exception
                    Throw ex
                Finally
                    vars.Unlock()
                End Try
            Catch ex As Exception
                Throw ex
            End Try
        End Sub
    End Class

    • Edited by mediterrano Tuesday, November 08, 2011 2:51 PM
    • Proposed as answer by ashuthinks32 Tuesday, November 08, 2011 3:01 PM
    Tuesday, November 08, 2011 2:51 PM
  • Here is an other example:
    http://microsoft-ssis.blogspot.com/2011/01/use-filedates-in-ssis.html

     


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    Tuesday, November 08, 2011 5:00 PM
  • Hi mediterrano,

    I Tried to use the code you added in blog. but i don't further step to take for it.

    It will be a great help if added the step for it.

    Regards

    Andy

    Thursday, August 02, 2012 7:04 AM