Get latest file name using SSIS script task: Error while execution
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,
November 12th, 2010 1:39am

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
Free Windows Admin Tool Kit Click here and download it now
November 12th, 2010 2:05am

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.
November 12th, 2010 2:28am

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
Free Windows Admin Tool Kit Click here and download it now
November 12th, 2010 2:43am

Or yes, you can add code as you have written Some people prefer to do it in code, others in the gui...Rgds Geoff
November 12th, 2010 2:44am

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. .
Free Windows Admin Tool Kit Click here and download it now
November 12th, 2010 3:56am

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
November 12th, 2010 5:46am

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.
Free Windows Admin Tool Kit Click here and download it now
November 12th, 2010 5:53am

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
November 12th, 2010 5:58am

Yes, The Test File exist in the folder. I checked option for identifying file as : Fully Qualified.
Free Windows Admin Tool Kit Click here and download it now
November 12th, 2010 7:01am

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 qualifiedRgds Geoff
November 12th, 2010 7:03am

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.
Free Windows Admin Tool Kit Click here and download it now
November 12th, 2010 7:41am

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 !
November 12th, 2010 9:11am

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.
Free Windows Admin Tool Kit Click here and download it now
November 13th, 2010 5:07pm

Try setting the Delay Validation property to TrueRgds Geoff
November 15th, 2010 1:20am

I did set it to TRUE now ; It is working! Thank you!
Free Windows Admin Tool Kit Click here and download it now
November 19th, 2010 9:02pm

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 luckSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
November 19th, 2010 9:28pm

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
Free Windows Admin Tool Kit Click here and download it now
September 19th, 2011 10:49am

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 forRgds Geoff
September 21st, 2011 6:39am

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
Free Windows Admin Tool Kit Click here and download it now
September 21st, 2011 8:51am

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
September 21st, 2011 10:33am

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
Free Windows Admin Tool Kit Click here and download it now
November 8th, 2011 9:53am

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
November 8th, 2011 12:02pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics