SSIS dynamic variable assignment

Hi,
I have a SSIS package that grabs data from SQL table and stores it in a flat file..
This flat file is dynamically named with the help of variables and current timestamp is appended at the end of the file name. ( FileName_201004091445 this is how it will be named)
Now this "DATA" file is created everynight. I have to calculate a checksum and rowcount for the same file and store these values in a different "CHECK" file
Now the input for the check file has the input from the same variable in which the DATA files name is stored and this has a timestamp on it.. now this calculation of checksum and rowcount follows the dataflow task. The dataflow task sometimes takes more than a minute to process so when the checksum part evaluates the variable for file name , the timestamp differs by a few minutes and hence the file name changes and the checksum and rowcount for the same file cant be done... Any work around for this??
 so here is the flow of the task


DATA FLOW                The "DATA" file is created and name stored in a Variable "FileName"

Calulating                          The "FileName" variable is used as a source for the CHECK file..
checksum
and rowcount


Now because of the timestamp even a minutes difference changes the file name and the CHECK file has a differenct name as input file than what I would like it to have..

All the help is greatly appreciated..
esotericmeeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mrichmonCommented:
you need to store the timestamp used to generate the filename and use that stored value to calculate both the filename and the checksum.  The checksum process should not reevaluate the time
0
esotericmeeAuthor Commented:
Thats how I have it implemented.. I have the timestamp value stored in a variable and it gets reevaluated at run time... thats the problem I am facing..
so file name is actually concatenation of 3 variables
( we have this process for 4 different files )

FinalFileName = user::FilePathVariable + "\"+User::FilenameVariable +"_"+ User::Timestampvariable
 
so output is something like C:\Documents\Foldername\Filename_201004091330

This gets evaluated in the property expressions at runtime...
so it gets different file names if minute changes.... timestamp is upto the minute...
0
mrichmonCommented:
It should only evaluate in one place and they all places use that one value - not be evaluated at runtime each time you access it.  So minutes should  not matter because it should only evaluate once.

You said the issue was that you evaluate it, create the file which can take some time (more than 1 min) then evaluate to create the checksum.

I am saying evaluate and STORE it.  Then use the stored value to create the file and the checksum so it won't change no matter how long it takes to create the file.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Reza RadConsultant, TrainerCommented:
try this:
set file name before the data flow in a Script task,
and then use this name in your destination flat file connection manager.
0
esotericmeeAuthor Commented:
Guys thanks for ur help...
I mean I had that logic of storing the name and then just passing this name to the checksum file in my mind, but I am new to SSIS and I am not sure how to store it in a variable because from what i tried it gets reevaluated..

Reza Rad: I am not very familiar with the SCRIPT task.

if you could tell me how to actually store the name ( where to store it and how )
It would be really helpful...
I have  always had that logic in my mind but I am not sure how to implement it...

thanks a lot for your help guys...
I really appreciate it..
0
Reza RadConsultant, TrainerCommented:
oK,
do these things:
right click on your control flow, select variable,
add new variable and name it as FinalFileName, scope: package, datatype: string ,set a default value for it, notice that default value must indicate a real file name, don't worry about this, this is just a default value for validation in compile time of SSIS.
now add a Script Task in dataflow, right before data flow task,
set language as Visual C#,
set ReadOnlyVariable with these variables:
user::FilePathVariable ,User::FilenameVariable ,User::Timestampvariable
set ReadWriteVariables with :
user::FinalFileName

edit script,
write this code in main () method:


Dts.Variables["User::FinalFileName"].Value = Dts.Variables["User::FilePathVariable"].Value.ToString() + System.IO.Path.DirectorySeparatorChar + Dts.Variables["User::FilenameVariable"].Value.ToString() + "_" + Dts.Variables["User::Timestampvariable"].Value.ToString();

, now build script,

this script will set value of FinalFileName variable,
now only thing you need is to set this variable in flat file connection manager.

the whole script of script task attached too.


/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_cde76cb580884975a18581abaf21f9f6.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        /*
		The execution engine calls this method when the task executes.
		To access the object model, use the Dts property. Connections, variables, events,
		and logging features are available as members of the Dts property as shown in the following examples.

		To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
		To post a log entry, call Dts.Log("This is my log text", 999, null);
		To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

		To use the connections collection use something like the following:
		ConnectionManager cm = Dts.Connections.Add("OLEDB");
		cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

		Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
		
		To open Help, press F1.
	*/

        public void Main()
        {

            Dts.Variables["User::FinalFileName"].Value = Dts.Variables["User::FilePathVariable"].Value.ToString() + System.IO.Path.DirectorySeparatorChar + Dts.Variables["User::FilenameVariable"].Value.ToString() + "_" + Dts.Variables["User::Timestampvariable"].Value.ToString();

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrichmonCommented:
Did that work?

I was going to post actual code after your last response, but reza_rad got to posting actual code first and so I was waiting to see if you had further questions...

If so let us know...
0
esotericmeeAuthor Commented:
Hi,
I have been working on a high priority job right now and did not have time to try this out. Will update as soon as I get a chance. Thanks a lot for all the help.
0
Reza RadConsultant, TrainerCommented:
@angelIII:
I bring solution in this post: #30230264  exactly.


0
esotericmeeAuthor Commented:
thanks for the help.. this worked like a charm!!!
Great help.. Really appreciated!!!
Sorry for not replying earlier.. my apologies...

But thanks for all the help...
0
Reza RadConsultant, TrainerCommented:
Glad to help,
Regards,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.