May 9, 2008 at 2:20 am
Hi All,
I am struggling to do what sounds like a simple task (famous last words)
I have a SSIS package which pulls data from an Excel Spreadsheet and puts it a SQL DB. (simply put)
The Spreadsheet is produced by a scheduled Crystal report.
What i want to do in my Package is to check to make sure the Spreadsheet has been updated today before bringing it into the DB. If it hasn't i want to be notified (the package can still run but need to know the data is out of date)
Any thoughts on the best method of doing this?
Thanks very much in advance!
Richard Farrington
"No Plan of Action ever survives Contact with the Users!"
May 9, 2008 at 7:37 am
Script task all the way!
Assuming you can check the location for the possibly updated file. You could easily get this info.
For instnace.
Dim t As DateTime = File.GetCreationTime(_filepath)
If t.[Date] = DateTime.Today.[Date] Then
'do something.
End If
let me know if you need any help pulling this info back out into the package.
Good Luck.
May 12, 2008 at 1:26 am
James and thanks alot for your reply!
Looks good to me, i am after the modifed time but the principle is still the same,
As you so kindly offered 🙂 How would you get this info back into the package then - can the script return a variable accessible by the rest of the package?
Thanks again!
Richard Farrington
"No Plan of Action ever survives Contact with the Users!"
May 14, 2008 at 1:22 pm
The steps to pass values from a script task back to SSIS are:
1. Define the SSIS variables (doh!!).
2. List them in the "ReadWriteVariables" entry for the script task. You have to click "Script" over on the left side of the editor box, right below "General", to get to that part. When entering the variable name, that's all that you put in -- no "@" or anything else.
3. In the script itself, assign the value to each like this (I assume your date last modified is in "t"):
Dts.Variables("User::Last_Modified").Value = t
May 14, 2008 at 2:52 pm
Rich, Sorry for the delay. Here's the meat and potatoes.
Create a Script Task. The script task either needs to have the path passed in or needs to discover the path on its own.
If you're passing it in It will come from a User Variable, do the following.
Create a variable (If you're going to use the variable in more than one task make sure that the scope of the variable is global) (In the example my variable is called "FullFilePath")
Double click your new Script Task. Click Script on the Left.
Click Design Script.
[font="Courier New"]Dim vars As Variables
Dts.VariableDispenser.LockOneForRead("User::FullFilePath", vars)
Dim filepath As String = vars("FullFilePath").Value.ToString() '+ "\" +
vars("FileName").Value.ToString()
vars.Unlock()
[/font]
Now you've got a the value from the variable inside your script. Now take that value and do the following.
[font="Courier New"]
Dim d As New FileInfo(filePath)
Dim aTime As DateTime = d.LastWriteTime
Dim d As New FileInfo(filePath)
Dim aTime As DateTime = d.LastWriteTime
Dim getfile As Boolean = False
If aTime.[Date] = DateTime.Today.[Date] Then
getfile = True
End If
[/font]
Now you've got a boolean variable that'll tell you if that file was altered today.
You can carry this boolean to other places in the script by declaring a script variable of boolean type and entering the following code.
[font="Courier New"]Dim writeVars As Variables
Dts.VariableDispenser.LockOneForWrite("User::GetFile", writeVars)
writeVars(0).Value = getfile
writeVars.Unlock()[/font]
If this doesn't cut it respond back! Good luck!
May 14, 2008 at 3:07 pm
James,
Thanks for the full picture. As a new user of SSIS, I'd be interested in the advantages of using the VariableDispenser approach as you did in your code. It seems to bypass the need to declare SSIS variables in the Script Task Editor as I'd specified in the quick example I posted earlier. But it does seem to require more coding in the script itself. Why do we want to do that?
--John
May 20, 2008 at 1:30 am
Apologies for Delay in replying - Been away 🙂
Thanks so much James and John for your replies
I think i understand whats needed 🙂 i will give this a try this week and let you know how i get on,
Thanks again!
Richard Farrington
"No Plan of Action ever survives Contact with the Users!"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply