SSIS Script task storing DateTime in variable

Question

I'm not sure why this happens, but my script task is not storing a DateTime-Value in a variable. This is the (till now) very basic code I've written:

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
    public void Main()
    {
        DateTime creationTime;
        DateTime modifiedTime;
        DateTime oldCreationDate = (DateTime)Dts.Variables["User::OldFileDate"].Value;
        DateTime oldModifiedTime = (DateTime)Dts.Variables["User::OldModifiedDate"].Value;

        Dts.Variables["User::OldFileDate"].Value = DateTime.Now;

        Dts.TaskResult = (int)ScriptResults.Success;
    }

    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
}

It's not done yet, but I wanted to check if the variable is stored or not. I checked the variables using the debugger, during runtime the varible changes, but if I look at the variable after the execution it is still the same value. I also checked the properties and ReadOnly is False and the Variables are also added to the package. Any Ideas on what I am doing wrong?

Can you not use an expression for this? you could say `@[User::OldFileDate] = GETDATE() ` — Dominic Cotton, May 19 '16 at 12:32
Hi R-Style, Is having this value as a variable necessary? What your trying to do can be achieved a WHOLE lot easier by just making a derived column called OldFileDate and set the value express to GETDATE() — Caz1224, May 19 '16 at 12:58
Your code looks good. In order to see the value of a variable during debug, you should set the breakpoint on the script task to post execute. Run the whole package and not just the task and look in the locals window (that only shows during debug). The variable value that you are showing in your screen shot is the design time value which will remain the same after debug. — Mark Wojciechowicz, May 19 '16 at 12:59
Yes - variables in SSIS go back to their original value (design time value) after the package finishes regardless of what they are set to. — Nick.Mc, May 19 '16 at 13:09
@Nick.McDermaid thanks, this is the info i needed... So it is not possible at all to store the changed variable after the package has finished? — R-Style, May 24 '16 at 06:27
Not unless you write it to a table or a text file or something. What are you actually trying to do? See here: http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem — Nick.Mc, May 24 '16 at 06:30
@Nick.McDermaid I have .xls-File and I want to check if it's an updated one or not, if yes import it to the database, if not do nothing... — R-Style, May 24 '16 at 08:46
What do you mean by updated? Do you mean imported? A well proven pattern for this is: after import, move the file to a different folder (i.e. called _Imported_). Likewise, if the import fails, move it to a subfolder called _Failed_. Any attempt to remember the file name externally is just going to cause trouble. i.e. when the file naming convention changes — Nick.Mc, May 24 '16 at 10:17
Or are you just trying to capture the last updated date of an existing file? You should get that from the file rather than storing it externally. — Nick.Mc, May 24 '16 at 10:18
@Nick.McDermaid I am going to implement your solution, that sounds actually lika a good pattern. Can you reference a link where a solution like yours is described? Thank you a lot! — R-Style, May 25 '16 at 06:49
This is pretty detailed: http://stackoverflow.com/questions/6698075/how-do-i-move-files-to-an-archive-folder-after-the-files-have-been-processed — Nick.Mc, May 25 '16 at 09:50

Joe C · Accepted Answer · 2016-05-24T11:54:35.380

Once stopped at a breakpoint, look at your variables in the watch window for run time values. The variable window is not updated during debugging.

EDIT: Based on your comment ignore the above. You can save the variable at any time in your package including the end. I suggest that you use an execute SQL task to:

Insert Into ImportedFiles (FileName, Imported) Values (?, getdate())

Based on comments to your question you want to determine if the file was imported previously. You can do:

Select 1 From ImportedFiles Where FileName = ?

Then based on that query you can set up logic to either import the file or skip it.

I know that, I needed to know if it was possible to save the variable after the package is finished — R-Style, May 24 '16 at 06:28

SSIS Script task storing DateTime in variable

1 Answers1