Getting a Package's ExecutionID

Hello everyone

I'm logging the execution of my package into a SQL SERVER table. Everything works right. I'm also executing this package using a web service, I'd like to retrive the package's executionId after it is executed. By the way, i'm using a Microsft.SqlServer.Dts.Runtime.Package object but i haven't found any property that could give this.

any clue?

thanks a lotHave you searched this forum for executionID? There has been many threads around this topic and the current version of SSIS' shortcomings with respect to logging the execution ID.|||There is a system variable ExecutionId. Try that for now, not sure if the value is available post execution, but would seem sensible.|||I mean there is a system variable called ExecutionInstanceGUID which is the same value as you get logged in the sysdtslog90 table.

Getting - Setting Environment Variables

Hi,

1st. Issue

I have written a package that uses an environmet variable which contains certain information that the user must enter. A sql script task will then get the information from the environment variable and manipulate it.

I don't have a problem programmatically getting information from the environment variable. However, when I manually edit the contents of the environment variable, the changes won't take place unless the box is rebooted. Is there a way to refresh environment variables without rebooting?

2nd. Issue

I have written another package that also reads data from an environment variable but it must also manipulate the data and save the changes to the environment variable.

I have accomplished this programmatically by using GetEnvironmentVariable() and SetEnvironmentVariable(), but unfortunately the changes live during program execution only. Afte the program executes, the changes are wiped out.

How can I make changes stick to environment variables (programmatically)?

-- I found a solution to this:

When programmatically getting and/or setting an environment variable the 'target' parameter must be specified in the method call. By 'target' I mean the location in the registry where your environment variable is stored.

GetEnvironmentVariable( var, target )

SetEnvironmentVariable( OldValue, NewValue, target )

Please visit this link for more info:

http://msdn2.microsoft.com/en-us/library/96xafkes.aspx

Get the variable from Execute Process Task to C#

Hi!

I need help with some C# code. I have build a SSIS package with an Execute Process Task. I need to send dynamic variables in to my C# program so I thought it was a good idea to use the StandardInputVariable.

How do I get the variable in my C# code?
Thanks

CarlYour Main methods parameter collection?|||

Peter K wrote:

Your Main methods parameter collection?

yes. provided that this functionality has been built into the c# code.|||Thanks for your help.

I tried to get the variable through the main method but it dont work, the only thing I got was the argument.
My test code:
static void Main(string[] args)
{

for (int i=0; i<args.Length; i++)
{
Console.WriteLine(args);
Console.ReadLine();
}

Carl|||

ctsand wrote:

Thanks for your help.

I tried to get the variable through the main method but it dont work, the only thing I got was the argument.
My test code:
static void Main(string[] args)
{

for (int i=0; i<args.Length; i++)
{
Console.WriteLine(args);
Console.ReadLine();
}

Carl

did you include the variable as an argument to your c# executable in the execute process task?|||I have a static argument and a variable in StandardInputVarable. I put a value in the variable for testing but it will be dynamic.|||

ctsand wrote:

I have a static argument and a variable in StandardInputVarable. I put a value in the variable for testing but it will be dynamic.

is this necessary? can't you just use a dynamically updated ssis variable when calling your executable in the execute process task?|||

My intention was have to have the argument to jump to a special method in the code. The variable will have information about witch rows in the table the code shall read in and treat.

In any case, can I put a dynamic variable in the argument?

Carl

|||

the code below is how to execute package in c# code.the red code tell you how to dymamic edit variable.i think this method can get variable.but i didn't try.please try it

//add reference "Microsoft.SqlServer.ManagedDTS"(in microsoft.sqlserver.manageddts.dll)
Imports Microsoft.SqlServer.Dts.Runtime

Dim pkg As String = "package directory"

Dim app As Application = New Application()
Dim p As Pakage = app.LoadPackage( pkg, Nothing )
p.InteractiveMode = true
Dim pty As DtsProperty

'Dim n As Integer = p.Configurations.Count

Dim strPty As String
For Each pty In p.Properties
strPty = pty.Name & ":"
Try
If pty.Get Then strPty &= pty.GetValue( pty ).ToString()
Catch
End Try
Console.WriteLine( strPty )
Next

Dim vir As Variables = p.Variables
vir( "strFile" ).Value = "C:\MyApp2.txt"

Console.WriteLine( p.Execute( Noting, vir, Nothing, Nothing, Nothing ).ToString() )

|||

ctsand wrote:

My intention was have to have the argument to jump to a special method in the code. The variable will have information about witch rows in the table the code shall read in and treat.

understood

In any case, can I put a dynamic variable in the argument?

Carl

i don't know. did you try?|||

I tried it but it didnt worked. The only thing I got was the name of the variable.

You wrote earlier:

is this necessary? can't you just use a dynamically updated ssis variable when calling your executable in the execute process task?

What did you mean by that?

|||

ctsand wrote:

I tried it but it didnt worked. The only thing I got was the name of the variable.

You wrote earlier:

is this necessary? can't you just use a dynamically updated ssis variable when calling your executable in the execute process task?

What did you mean by that?

what are the option settings in the process page of the execute process task editor?|||

RequireFullFileName = True

Executable = M:\Program\Person.exe

Arguments = fakt

WorkingDirectory = M:\Program

StandardInputVariable = User::test

StandardOutputVariable =

StandardErrorVariable =

FailTaskIfReturnCodeIsNotSuccessValue = True

SuccessValue = 0

TimeOut = 0

TerminateProcessAfterTimeOut = True

WindowStyle = Normal

|||what is the value and data type of User::test immediately before the execute process task starts executing? is the value of this variable correct?|||

The data type is string and the value is testing.

I have a question for you.

Is't meaning that I shall use the main-method to get the argument and the variable?

Carl

Get the Row Count

I have created a package which is transforming the data from Source OLEDB Sql Server to Destination OLEDB Sql Server programatically in VB.NET

During execution phase, I want to generate an event after every 1000 rows are transformed. As per I think, OnProgress event of IDTSEvent doesn't support this kind of a thing.

And after my Transformation is completed, I want to know how many rows have being transformed.

So how can the above two task be performed?

Add a Row Count component to your data flow to capture the number of rows.

You should be able to fire events using the FireProgress or FireInformation methods of the ComponentMetaData class. See this topic in Books Online: Raising Events in the Script Component (http://msdn2.microsoft.com/en-us/library/aa337081.aspx)

|||

I am creating the package programmatically and not using designer for it.

I have already created the package which has OLEDB and Source and Destination and added to TaskHost. For Row Count Component I think I have to create another Task Host and then add it to main pipe. But it has to be added as For Each Loop. But if I use For Each Loop then one Task Host will process at a time.

So what is the solution for this?

|||

I have added Row Count Component as Transformation (DTSTransform.RowCount) to the ComponentMetaData.

Now I get the number of Row Transformed after post execute event whereas I need to raise an event after every 1000 rows are transformed. According to me after PostExecute event of the DataFlow, the number of Rows Transformed as set into the variable by Row Count Component. So how can I raise event after every 1000 rows?

|||One way to do this would be to add a script transform to monitor the number of rows and fire the event after 1000 rows pass through.|||

I have used Script Component to transform. As I am creating this programmatically, I have copied the code generated in xml format of dtsx file to my application as a string array exactly as in the file to SourceCode property.

I have set PreCompile Property to false. So I don't need to set the BinaryCode property.

I have used code as below:

Dim DFTransform As IDTSComponentMetaData90

DFTransform = DTP.ComponentMetaDataCollection.New()

DFTransform.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

DFTransform.Name = "ScriptTransform"

DFTransform.UsesDispositions = False

Dim TransInst As CManagedComponentWrapper = DFTransform.Instantiate()

TransInst.ProvideComponentProperties()

DTP.PathCollection.New().AttachPathAndPropagateNotifications(DFSource.OutputCollection(0), DFTransform.InputCollection(0))

TransInst.SetComponentProperty("VsaProjectName", "ScriptComponent_3a1cf20682b14906bbdc971f7768e55c")

TransInst.SetComponentProperty("SourceCode", AddSourceCode(DFTransform.ComponentClassID))

TransInst.SetComponentProperty("BinaryCode", AddBinaryCode)

TransInst.SetComponentProperty("PreCompile", False)

TransInst.SetComponentProperty("UserComponentTypeName", "Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

TransInst.AcquireConnections(Nothing)

TransInst.ReinitializeMetaData()

Dim output As IDTSOutput90 = DFTransform.OutputCollection(0)

Dim outputColumn As IDTSOutputColumn90 = output.OutputColumnCollection.New()

outputColumn.Name = "myCount"

outputColumn.SetDataTypeProperties(Wrapper.DataType.DT_I4, 0, 0, 0, 0)

DFTransform.OutputCollection(0).ExternalMetadataColumnCollection.IsUsed = False

TransInst.ReleaseConnections()

But when I compile my application I get following errors:

Error Code :-1073450910

Sub Component :- Script Component [43]

Description :- System.NullReferenceException: Object reference not set to an instance of an object.

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()

Error Code :-1073450901

Sub Component :-DTS.Pipeline

Description :- "component "Script Component" (43)" failed validation and returned validation status "VS_ISBROKEN".

Is there anything which I need to do differently

Get the list of variables in a package inside a custom component

Hi

I am developing custom dataflow component ,I need to get the of variables of the current package in the component , how can i get it?

Thanks

Mani

Why do you need a list of variables?

Normally the two things you would do in a component is to validate a variable exists and read or write the value. Both of these can be achieved with the VariableDispenser class that is avilable from the base PipelineComponent object.

For example, in validate I would do something like this-

object obj1 = ComponentHelper.GetPropertyValue("OutputRowCountVariable", base.ComponentMetaData());
if ((obj1 != null) && (obj1.ToString().Length > 0))
{
if (!base.VariableDispenser().Contains(obj1.ToString()))
{
this.PostError(string.Format(Resources.ErrorPropertyInvalidVariableNotExist, "OutputRowCountVariable", obj1.ToString()));
return 1;
}
if (!this.ValidateVariableType(obj1.ToString(), out code1))
{
this.PostError(string.Format(Resources.ErrorInvalidVariableType, "OutputRowCountVariable", code1.ToString()));
return 1;
}
}

|||

Hi Darren

I am developing a oracle source component , I need to get the table or view name which are stored as variables. Iam not getting the ComponentHelper class .

thanks

Mani

|||So you do not need a list of variables, you just need to get the variable value. Use the VariableDispenser. Ignore the ComponenHelper, that is just a wrapper of mine, and in that instance I am just getting the value, nothing more than that.|||

hi thanks darren i got it i used the code

IDTSVariables90 var;

ArrayList tableOrViewName = new ArrayList();

this.VariableDispenser.LockForRead("TableName");

this.VariableDispenser.GetVariables(out var);

foreach (IDTSVariable90 variable in var)

{

tableOrViewName.Add(variable.Value);

}

Thanks

Mani

|||

A minor point but you could save the loop and array. You are only reading from one variable, so you could use LockOneForRead, e.g.

string tableName = "";

IDTSVariables90 variables = null;

VariableDispenser.LockOneForRead("TableName", ref variables);

tableName = variable.Value.ToString();

variables.Unlock()

You should probably check that the Value of teh variable is not null as well, before calling ToString. Always call Unlock as soon as you can.

If you expect multiple tables to be selected, then this would need to be a delmited list in your variable value, you cannot have multiple variables of the same name. You could have a more complex type for the variable value, but I would use a delimited string so it is easier to manage for both design-time setting and also persistance. You can set a string through an expression for example, but not an object.

Get the InputGlobalVariables from a different package

I've been tasked with figuring out how to alter the version_id of an ExecutePackageTask programatically from a separate DTS package.

The ultimate goal is to have a "utility" DTS pack that
iterates through all the child packages within a given parent package, checks the systems tables to get the latest version_id, then assigns the latest versionid to the ExecutePackageTask. We feel this beats the heck out of opening the parent, opening each ExecutePackageTask, running through the randomly ordered list of Package Names and clicking on the updated package when a child package changes or a program is migrated to a new server.

My approach was to open the parent pack from an activex script in the utility DTS package, then iterate through the task collection looking only at ExecutePackage tasks, then check the versionid against the appropriate row in the sysdtspackages table and update the child package version_id if necessary.

I've only gotten as far as looking at the ExecutePackageTask properties and I'm stumped . . . I'm told that the object doesn't support the property or method, yet you can do this manually through a disconnected edit! Any thoughts?

Here's the code so far:
Function Main()

'create the target parent package object
Set objPackage = CreateObject("DTS.Package2")

'load the target parent package
objPackage.LoadFromSQLServer "PAMB","dts" ,"dts", DTSSQLStgFlag_UseTrustedConnection, , , ,"TESTME2"

'Create the task object
Set objTask = objPackage.Tasks

'Iterate through the tasks
For each objTask in objPackage.Tasks
if left(objTask.Name,29) = "DTSTask_DTSExecutePackageTask" then
msgbox objTask.Description
msgbox objTask.Versionid
end if
Next

Main = DTSTaskExecResult_Success

End Function

Thanks in advance,

PamClearly there are too many projects in my head right now . . .

get SQL connection for SQL config from XML

Hi

In toder to make my SSIS packages portable, I need to be able to set connection string to sql package configuration. I thought I can do that via XML package, problem is, at execution time SQL server package configuration is queried first, then variables from XML.
This way, I have no chance to set connection via XML.
Any ideas on how to aproach this ?
(I want to have as much configuration on SQL server so I can modify it easily from future GUI application.

best regards

For this scenario what you need is an indirect configuration, where the connection string to the SQL Server database where your "real" configurations are stored is in turn stored in an environment variable:

http://dotnetjunkies.com/WebLog/appeng/archive/2006/05/30/indirectconfigpackagessis.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx

I do not know of any way to perform this indirection through an intermediary XML config file - only environment variables are supported.

|||

One more method:

http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

|||You should be able to set your initial SQL Server connection from an XML configuration file. In the Configurations dialog, make sure that the XML configuration is at the top of the list, so that it is executed first.

Get server name

Is there any way to get the SQL Server's name without running SELECT
@.@.SERVERNAME? I want a DTS package that I am working on to be able to
migrate from the dev server to the live server without having any connection
s
to the dev server. To run the query to find the server name, you need a SQL
server to run the query on. However, the dev server is not guaranteed to be
accessible to run the query on and I don't know the name of the servers that
this will be migrated to.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsHi
Unless you are using a default connection your installation process should
really ask for the Sqlserver name/instance. You could look at deciphering th
e
output of NET START. If you only want the computer name the command prompt
command HOSTNAME will give you it.
You may want to look at:
http://www.sqldts.com/default.aspx?242
John
"Chris Lieb" wrote:

> Is there any way to get the SQL Server's name without running SELECT
> @.@.SERVERNAME? I want a DTS package that I am working on to be able to
> migrate from the dev server to the live server without having any connecti
ons
> to the dev server. To run the query to find the server name, you need a S
QL
> server to run the query on. However, the dev server is not guaranteed to
be
> accessible to run the query on and I don't know the name of the servers th
at
> this will be migrated to.
> Thanks in advance
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps