February 14, 2008 at 3:19 pm
Hi guys, I need to build an SSIS package that will check that a file exists and then do a simple data import into the SQL Database.
The issue I have is that the filename will vary based on the current date.
[Static Folder Path]\YYYY\MM MMMM\24 hr Operational Plan - DD MMM YY.xls
I've attempted to build a script task to check the file exists but I'm not certain on actually moving to the next step and reading from the file.
Public Class ScriptMain
Public Sub Main()
Dim path As String = "[Static Folder Path]"
Dim dateToday As Date = Now().AddDays(-1)
Dim filePart1 As String = Format(Now(), "YYYY")
Dim filePart2 As String = Format(Now(), "MM MMMM")
Dim filePart3 As String = Format(Now(), "DD MMM YY")
Dim fullFileName As String = path & "\" & filePart1 & "\" & filePart2 & "\" & "24 hr Operational Plan - " & filePart3
If System.IO.File.Exists(fullFileName) Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
End Class
I expect I can assign a variable and use that in the Import process but I'm not sure how.
Additionally, is there a way to debug the code. If I run the step by itself it's failing, however I can't actually see what's going on to determine why it's failing.
February 19, 2008 at 2:44 pm
First of all, where are you getting the file from? Is it from a folder somewhere or is it located in FTP? Because it can be a lot more easier than you think to do this if you can give us a hint of where the files are coming from.
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 19, 2008 at 3:38 pm
Setting a package variable in your Script Task sounds like the right track.
To use that variable open the properties for the Connection Manager used for the Excel file. Select Expressions, for the Property choose ConnectionString and set the Expression = @[User::myVariable].
As far as debugging, setting Break Points in your script may help those efforts. See BOL topic: How to: Set a Breakpoint in Script in a Script Task.
February 19, 2008 at 9:41 pm
Worked it out shortly after - decided on a alternative solution now anyway. But for anyone who's having the issue;
Public Sub Main()
Dim path As String = " "
Dim dateToday As Date = Now()
Dim filePart1 As String = Format(dateToday, "yyyy")
Dim filePart2 As String = Format(dateToday, "MM MMMM")
Dim filePart3 As String = Format(dateToday, "dd MMM yy")
Dim fullFileName As String = path & "\" & filePart1 & "\" & filePart2 & "\" & "24 hr Operational Plan - " & filePart3 & ".xls"
Dts.Variables("User::FileName24HrOperationalPlan").Value = Right(fullFileName, Len(fullFileName) - 1).Replace("\", "\\")
'System.Windows.Forms.MessageBox.Show(Dts.Variables("User::FileName24HrOperationalPlan").Value.ToString)
If System.IO.File.Exists(fullFileName) Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
The 'System.Windows.Forms.MessageBox.Show(Dts.Variables("User::FileName24HrOperationalPlan").Value.ToString)' is a comment but I used this to test the folder being returned. Shortly after located the issue was with my format string.
Cheers
February 21, 2008 at 6:46 am
At http://www.sqlis.com you can find a Task named FileWatcher which will meet your specification.
February 21, 2008 at 8:43 am
yeah, you need to use "\\" instead of "\". and if you only needed to create this string you do not need a script task for it. you can do exactly the same from the connections properties Expressions.
February 21, 2008 at 8:57 pm
The file watcher would work or you can use the foreach task. You can set the foreach task to look for a pattern (somefile_*.txt). If it finds something that meets the pattern you can store that filename and location in a variable to use as a connection string. We have a job that runs several times a day, if it finds a file it loads it. If there isn't one there then the foreach task doesn't execute the steps inside of it.
Hope that helps.
J.D.
May 13, 2009 at 7:47 pm
Hello JD,
I wonder if u can provide a little details of how we can add condition to the flow so that when I find a file, it executes the process. And when when I dont, it doesnt.... i installed the FileWatcher2005 package.
Cheers,
Clement
May 14, 2009 at 9:53 am
ngkahing (5/13/2009)
Hello JD,I wonder if u can provide a little details of how we can add condition to the flow so that when I find a file, it executes the process. And when when I dont, it doesnt.... i installed the FileWatcher2005 package.
Cheers,
Clement
Personally, I like the Foreach Loop container instead of the file watcher task because it works with multiple files. The process is pretty simple.
1. You specify the folder to look at and a pattern for the file (i.e. *.txt) in the enumerator configuration
2. Create variable mapping to hold the file name
3. Inside the foreach loop put the execute task (it really could be any other type of task too) and configure it to do what you need the task to do.
4. If you only want the process to file to fire once for each task, I would suggest moving the file to a different folder (or delete the file). You can use the file system task to accomplish this. One of the operations you can perform is move file.
Throughout the day, I have a sql server job that essentially sweeps the folder looking for new files. If none are there, then no action is taken. We've got some additional controls in place should someone put any processed files back to the listening folder, but this should get you started.
I use this as a best practice where I work. So far, we haven't run into any issues.
J.D.
May 28, 2009 at 1:05 pm
J.D. Gonzalez:
Would be so kind as to supply an example of what you did to implement?
Thanks!!!
September 9, 2009 at 4:23 pm
How can SSIS package know that, which file is newly arrived to extract the date from the particular path.
Gr8 thanks
September 9, 2009 at 4:23 pm
How can SSIS package know that, which file is newly arrived to extract the date from the particular path.
Gr8 thanks
September 10, 2009 at 5:31 am
We use scheduler and run the process every 10 min to check for new files and move the processed files to another location so that everytime you get new files to read.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply