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