how to get total number of variables using scripttask

I want to loop thru all the variables in my package and set number of variables that had variable-name begin w/LOCAL, so I can use the name to generate a dynamic SQL query for the next EXEC SQL Task. any one know how to do this.

Use the count property on the variables collection.|||

that is what I did, Dts.Varaiables.count, but it did not return the right numbers. I have over 10 variables and it return 1.

|||

Have you added your 10+ variables to either the ReadOnlyVariables or ReadWriteVariables list? The Dts.Variables collection is generated based on those preset list of variables.

Thanks,
Patrik

|||

no, I did not.

OK, let me back out a little bit, I try to use the event handler as a trigger for my custom audit logging, for each task when I am done, I want to use a script to find out what is in my variables pool, loop thru them and build a query, (“select count(*) from ‘[“ + @.variable1), and then the next execute SQL task will execute this and return the count to a variable so I can have the OnPostExecute event write out the record counts, so I only have 1 variable “MySQL” in the ReadWriteVariables list, since what I want is when I create a new package, all I have to do is to add or remove local variables, no code changes is need it. if I have to put in all the variables, then I defeat my intention, is there a work around for this? Any suggestion is greatly appreciated.

|||

What sort of processing are you trying to achieve with the Script Task? Which variables are you using and looking for in the loop? Is the collection of variables you're interested in always the same? If you don't fill in the ReadOnlyVariables and ReadWriteVariables properties you can do the work using the VariableDispenser (http://msdn2.microsoft.com/de-de/library/microsoft.sqlserver.dts.runtime.variabledispenser.aspx); however, you would still need to know the name of the variables you're interested in reading.

If you're using the Script Task just to create a SQL command statement for the Execute SQL Task you could look at using expressions (http://msdn2.microsoft.com/en-us/library/ms141214.aspx).

Cheers,
Patrik

|||

Hi..

You can read all variables by using ActiveX Script Task instead of using Script Task.

Function Main()
Dim oVal

Dim isSystem, varType, varName
Dim str
MSGBOX "Total Variable = " & DTSGlobalVariables.Count

For each oVal in DTSGlobalVariables
isSystem = oVal.SystemVariable
varType = oVal.DataType
varName = oVal.QualifiedName

MSGBOX varName & " / " & varType & " / " & "System:" & isSystem

Next
Main = DTSTaskExecResult_Success

End Function

HTH

ADConsulting / SQLLeader.com / Daeseong Han

How to get the value of System::TaskName of a higher up level?

Hi there,

I was wondering how can I get the value of System::TaskName of a higher scope when I have a Master Package that have several sequence task, data flow tasks and execute package tasks. For each task inside this Master Package on the Post-Execute event handler I have a script task that logs the execution of each task.

After running this master package I saw in my db that I have a row for every single tasks executed in the process and not only the tasks that exist in the master package. For instance, for simplicity let's say my master package looks like this:

SQL Task Script
|
Exec Package 2
|
Exec Package 3
|
Exec Package 4

Package 2,3 and 4 have a SQL Task Script with name Execute SQL Task Package 2, Execute SQL Task Package 3, Execute SQL Task Package 4.

Here's what I got in my db:

Execute SQL Task
Execute SQL Task Package 2
Package2
Execute Package 2
Execute SQL Task Package 3
Package3
Execute Package 3
Execute SQL Task Package 4
Package4
Execute Package 4

I see two TaskName variables in the Variable window, one with OnPostExec scope and the other with Execute Package 2 for instance. I want to get the value of System::TaskName with Execute Package 2 scope.

I want to see in my db only the tasks in bold. Any ideas of how can I do this? I hope you understand what I'm trying to achieve.

Thanks!

Each eventhandler has a variable called @.[System:ropogate] which should be all that you need.

System Variables

(http://msdn2.microsoft.com/en-us/library/ms141788.aspx)

I could tell you how to use it but you'll probably learn more by fiddling with it yourself. its fairly self-explanatory.

-Jamie

|||Hi Jamie,

I knew about the Propagate variable and I tried setting it to false for every post-execute event handler in my master package. I still got multiple records in my table.

Then I tried with False at the package level on every subpackage my master package is calling but I got the same results.

Finally, I remove all my post-execute event handlers from my master package and leave just one, at the master package level and set the Propagate to false and I still got all the subtasks logged into my table.

Could you tell me how to use this variable?

Thanks!|||

You need to set it on the eventhandlers that are scoped to the tasks for which you don't want to do any logging. So, if you have a task called "Ricardo's Execute SQL Task" in a child package that you don't want to capture information for, set that task's eventhandlers to ahve System:ropogate=FALSE.

-Jamie

|||Hi Jamie,

Well, I guess I'm doing something wrong because I set to False the Propagate variable on the Execute SQL Task of my first child package and also at the package level. So now, the Post-Execute event handler for these two objects have nothing but I changed the value of Propagate from True to False.

In my parent package, I disabled all the post-execute event handlers except for the Execute Package Task that calls Package 2.

I got 3 rows inserted in my table:

Taskname = Script Task Post-Exec Package 2 - SourceName = Execute SQL Task Package 2
Taskname = Script Task Post-Exec Package 2 - SourceName = Package2
Taskname = Script Task Post-Exec Package 2 - SourceName = Execute Package 2

the values from above are from System::TaskName and System:ourceName

Thanks!

How to get the return/execution value of a package from a parent?

Hi there,

I'm trying to get the return value of a package. I see there is a ForcedExecutionValue property which I set using an expression (variable). What I'm executing are 2 packages, Package1 contains an Execute Package Task that calls Package 2. Package 2 contains a Script Task that sets the value of variable Max. I want to get the value of Max in Package 1 then how can I do this?

My first approach is toset the return value of Package 2 = Max and then I thought I could retrieve this value from Package 1 but I'm not able to do that yet.

Any thoughts?

Thanks for any help!The way I would approach this is to use a Script Task (surprise, surprise) to load and execute the package instead of the Execute Package Task. Your script has the ability to read the child packages variables after it has executed, thus allowing child variables to be passed back to the parent.
http://blogs.msdn.com/jamesk/archive/2005/12/21/506463.aspx

Alternatively, you can also have the child package set the parent's variable directly.
http://blogs.conchango.com/jamiethomson/archive/2005/03/17/1151.aspx|||Hi JayH,

Yes, I did the first approach and it worked fine. I'm loading the package from a Script Task and getting the Executables.Count and store this value in a local variable.

Thanks for the suggestion!.

Ricardo

How to get the number of queries fired to the database by a single ssis package

Hi,

Is there any way that I can know the count of queries and queries fired to the database after running a ssis package?

can any one help me out?

Thanks in advance.

Programatically yes, otherwise I haven't any idea.|||SQL Profiler could help you.

how to get the most recent file?

daily my client uploads a flat file to a folder.

each file has the same name with an " _##### " id number.

I have created a ssis package that will download the file, and put the data in a table, but I have to change the ID number manually.

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

Try to use PropertyExpressions to set the ConnectionString of your flat file connection manager. You create a expression that appends the ID to the "same_file_name" and the ID can be set in a variable using configurations.

HTH,
Ovidiu Burlacu

|||

Ovidiu Burlacu wrote:

Try to use PropertyExpressions to set the ConnectionString of your flat file connection manager. You create a expression that appends the ID to the "same_file_name" and the ID can be set in a variable using configurations.

HTH,
Ovidiu Burlacu

And if the ID is always incremented, you can store either the last value or the next value in a flat file, or holding table in a database. For example, when you build the package, you could run an Execute SQL task to select the next ID from the database table and then use that result to build the filename.|||

Jdmaddox,

I did not try but just an idea. You can use 'xp_cmdshell'. Using this you can give OS commands.

http://msdn2.microsoft.com/en-us/library/aa260689(SQL.80).aspx

And in DOS cmd: try DIR /? in cmd to get dir options

c:\>dir /OD gives the files names in sorted based on time, so hopefully you can parse them.

Hope this helps,

Venkat

|||maybe I should have started with...I am completely new to SSIS and DTS|||

jdmaddox wrote:

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

Is that really what you want? What if two files have arrive since you last executed your package? You'll miss one of the files.

-Jamie

|||

jdmaddox wrote:

maybe I should have started with...I am completely new to SSIS and DTS

The fact that you are using SSIS and have never used DTS is a good thing in my opinion

-Jamie

|||

yes, a new file is uploaded daily, and I bring it in daily.

At present, I am executing the package by hand, and changing the file name in the connection manager.

It would be much simpler if I could get the package to target the most recent file by date and bring that into the DB

|||

jdmaddox wrote:

yes, a new file is uploaded daily, and I bring it in daily.

At present, I am executing the package by hand, and changing the file name in the connection manager.

It would be much simpler if I could get the package to target the most recent file by date and bring that into the DB

You could use For Each Loop ForEach File enumerator to loop over all the files, on the last iteration you will be looking at the last file, right? Assuming the files appear in date order (the easy way to do this is to store all files with a filename of YYYYMMDD*.*) then this will work.

-Jamie

how to get the most recent file?

daily my client uploads a flat file to a folder.

each file has the same name with an " _##### " id number.

I have created a ssis package that will download the file, and put the data in a table, but I have to change the ID number manually.

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

Try to use PropertyExpressions to set the ConnectionString of your flat file connection manager. You create a expression that appends the ID to the "same_file_name" and the ID can be set in a variable using configurations.

HTH,
Ovidiu Burlacu

|||

Ovidiu Burlacu wrote:

Try to use PropertyExpressions to set the ConnectionString of your flat file connection manager. You create a expression that appends the ID to the "same_file_name" and the ID can be set in a variable using configurations.

HTH,
Ovidiu Burlacu

And if the ID is always incremented, you can store either the last value or the next value in a flat file, or holding table in a database. For example, when you build the package, you could run an Execute SQL task to select the next ID from the database table and then use that result to build the filename.|||

Jdmaddox,

I did not try but just an idea. You can use 'xp_cmdshell'. Using this you can give OS commands.

http://msdn2.microsoft.com/en-us/library/aa260689(SQL.80).aspx

And in DOS cmd: try DIR /? in cmd to get dir options

c:\>dir /OD gives the files names in sorted based on time, so hopefully you can parse them.

Hope this helps,

Venkat

|||maybe I should have started with...I am completely new to SSIS and DTS|||

jdmaddox wrote:

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

Is that really what you want? What if two files have arrive since you last executed your package? You'll miss one of the files.

-Jamie

|||

jdmaddox wrote:

maybe I should have started with...I am completely new to SSIS and DTS

The fact that you are using SSIS and have never used DTS is a good thing in my opinion

-Jamie

|||

yes, a new file is uploaded daily, and I bring it in daily.

At present, I am executing the package by hand, and changing the file name in the connection manager.

It would be much simpler if I could get the package to target the most recent file by date and bring that into the DB

|||

jdmaddox wrote:

yes, a new file is uploaded daily, and I bring it in daily.

At present, I am executing the package by hand, and changing the file name in the connection manager.

It would be much simpler if I could get the package to target the most recent file by date and bring that into the DB

You could use For Each Loop ForEach File enumerator to loop over all the files, on the last iteration you will be looking at the last file, right? Assuming the files appear in date order (the easy way to do this is to store all files with a filename of YYYYMMDD*.*) then this will work.

-Jamie

How to get status for every individual Task in a package

Hi,

I have a package in which there are four tasks. I have to get the status of those four tasks and finally send a mail. I am unable to get the status for individual task.

Has anyone tried this? Please help.

Thanks in advance.

Hi,

Create 'OnError' and 'OnPostExecute' event handlers for each task. In the 'OnError' event, set the error message to a variable. In the 'OnPostExecute' event, check the variable. If is it empty set the status as 'Success' or else set it as 'Failure' with the error message. In that event itself, u write the status to a file in append mode (first task should open the file in overwrite mode).

Hope it would help.

How to get execution progress ?

Does anyone knows how to retrieve execution progress from ssis package, that was
executed programatically.
What I am trying to do, is to launch ssis package on server
and send the progress ("percent complete") to some remote client in order to display it
in progress bar.
I want that ,while ssis package executes, it will raise some event every time the progress changes.

Implement an IDTSEvents interface and pass it to the Execute method.

This Books Online page has more details:

http://technet.microsoft.com/en-us/library/ms135967.aspx

|||I've tried to do that, but without any success.

namespace SSIS_EXEC_SERVER
{
class ExecuteSSIS : IDTSEvents, IHandler
public ExecuteSSIS(I)
{
MyRemotableObjects.Cache.Attach(this);
}

public String fire(string location)
{

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
package = app.LoadPackage(location, this);
package.DesignEvents = this;
Microsoft.SqlServer.Dts.Runtime.DTSExecResult result= package.Execute();

}

IDTSEvents Members
.
.
.
.

}

When I preform package.Execute() the ExecuteSSIS class does not recieve any events.
There is OnProgress method in IDTSEvents and I asumed that it will be informed by the package (there
is PercentComplete paramether in the method).
But still no success...