September 23, 2010 at 9:57 pm
OK, I'm no SSIS expert, but I am trying to do something very simple which I just cant get to work.
It seems there are lots of posts about this issue but I am doing the same as all the posts suggest, without success.
I will make this as simple as possible in order to demonstrate the issue.
1. Create a new SSIS Package and save it as TestPackage.
2. Right click on the canvas of the "Control Flow" tab and click "Variables".
3. Create a new variable from the "Variables" window and callit "varInt". Scope will default to the package name (i.e. "TestPackage"), DataType=Int32, Value = 0, ReadOnly=False.
4. Add a "Script Task" to the Control Flow tab.
5. Edit the Script Task and set the "ReadWriteVariables" value to "varInt".
6. Click on "DesignScript" and add the following code.
MsgBox(Dts.Variables("varInt").Value.ToString)
Dts.Variables("varInt").Value = 2
MsgBox(Dts.Variables("varInt").Value.ToString)
Dts.TaskResult = Dts.Results.Success
7. Save the above, then Execute the "Script Task".
The result I get is the first message box display "0", the second message box display "2". As expected.
If I execute the script again, unless I am missing something fundamental, I would expect the first message box to now show "2", then the second box to show "2". However, it shows me the "0" followed by the "2" again.
I therefore assume that whilst the value of the variable is being changed successfully whilst inside the ScriptTask, the new value is not be saved upon completion of the ScriptTask. The new value assigned to the variable "varInt" will therefore not be available to other tasks in the package which is the ultimate aim here.
How do I therefore update the value of a variable from a Script Task so that the new value of the variable is available to other task which will access the variable.
Any help would be appreciated as I have Googled this death.
Cheers
Doug
September 23, 2010 at 10:08 pm
The behaviour is perfectly normal. The variable is losing the scope as it is not persistent as the process terminates
Raunak J
September 23, 2010 at 10:14 pm
Thanks for the reply. I guess the obvious question then is, how do I make it persistent? Or is this not possible? Maybe I totaly miss the point of variables then?
September 23, 2010 at 10:52 pm
You may store the variable value Post Execution in some another variable which will be accessed in the next script task...
But what is it that you wish to achieve
Raunak J
September 23, 2010 at 11:06 pm
Basically, I need to increment the number in the variable, and then use that number (variable) in the next step (task) of the package. Here's what I am trying to achieve.
I have an FTP folder into which an application creates files on a daily basis. I only want to download the last file created. The files are named numerically in the following format "X999999.txt" where 999999 is incremented daily. So for example today a file called "X000010.txt" may be created, tomorrow it will create "X000011.txt", the day after "X000012.txt" etc etc.
I was considering using two variables, one to store the number used in the file name and one to store the filename itself. The package would contain two script tasks and the FTP task.
1. Script Task 1 - Increment a variable called "varInt". For example, it may be set to "10"
2. Script Task 2 - Pass in "varInt" as ReadOnlyVariable then format the filename based on the value of "varInt". Set the value of a second variable to store the filename. i.e "FileNameVar" would contain "X000010.txt"
3. FTPTask - Would use "FileNameVar" as the file to FTP.
This package would be run daily, each day downloading the file with the next sequential number as the filename.
Cheers
Doug
September 23, 2010 at 11:15 pm
doug.milostic (9/23/2010)
I have an FTP folder into which an application creates files on a daily basis. I only want to download the last file created. The files are named numerically in the following format "X999999.txt" where 999999 is incremented daily. So for example today a file called "X000010.txt" may be created, tomorrow it will create "X000011.txt", the day after "X000012.txt" etc etc.
Doug,
I have few questions:
1.Is only one file created at remote location of multiple files
2.Why not use the WQL Query
Using the Windows Event Managament Instrumentation you may achieve the same easily and elegantly.
you may google for better understanding
Raunak J
September 23, 2010 at 11:20 pm
There is one file created every day, so the files accumulate in the FTP folder. They do get cleaned out, but usually only files older than 30 days are removed.
I dont know what WQL Query is so would need to research as you suggest.
What about storing it Post Execution? I've seen this terminology around the blogs, but no idea what it means?
Thanks again,
September 23, 2010 at 11:32 pm
Doug,
You have 2 options at this point of time:
Option 1: Use WQL Query and trigger on FileCreate Event
OR
Option 2: Use FileSystemWatcher in Script Task and trigger on FileCreate Event
Both are logically same. Conceptually it will help you understand the more granular basics of Enterprise development.
Raunak J
September 23, 2010 at 11:37 pm
Thanks Raunak, I'll look into it.
Cheers.
September 24, 2010 at 1:55 am
I think that there is another option: store your incrementing values in a database table. Your package just needs to read/increment as necessary - then you get the data persistence you need.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 24, 2010 at 5:37 am
Or, just to add another option:
1. Add a script task.
2. In the script, create a directoryinfo on the directory. (let's name it di)
3. Use the getfiles method: di.getfiles() and store the results in an array of fileinfo's
4. Search the fileinfo with the "highest" name.
5. Use the path of the fileinfo for the rest of your package.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 24, 2010 at 5:41 am
da-zero (9/24/2010)
Or, just to add another option:1. Add a script task.
2. In the script, create a directoryinfo on the directory. (let's name it di)
3. Use the getfiles method: di.getfiles() and store the results in an array of fileinfo's
4. Search the fileinfo with the "highest" name.
5. Use the path of the fileinfo for the rest of your package.
"highest" name???
Raunak J
September 24, 2010 at 5:44 am
Raunak Jhawar (9/24/2010)
da-zero (9/24/2010)
Or, just to add another option:1. Add a script task.
2. In the script, create a directoryinfo on the directory. (let's name it di)
3. Use the getfiles method: di.getfiles() and store the results in an array of fileinfo's
4. Search the fileinfo with the "highest" name.
5. Use the path of the fileinfo for the rest of your package.
"highest" name???
Based on the ascending file-naming scheme detailed in an earlier post.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 24, 2010 at 6:29 am
class Program
{
static void Main(string[] args)
{
DirectoryInfo di = new DirectoryInfo(@"D:\Personal");
FileInfo[] fi = di.GetFiles("*.msg", SearchOption.TopDirectoryOnly);
Array.Sort<FileInfo>(fi, delegate(FileInfo a, FileInfo b) { return a.CreationTime.CompareTo(b.CreationTime); });
}
}
I got it!!!!:-D:-D:-D
Raunak J
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply