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 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 recordset result in variables

Hello folks.
I am running a series of queries in a stored procedure.
For example, my first query might return a recordset like this
Apples
Oranges
Pears
Turnips
I want to put those reults in variables
So I might have
Declare @.Fruit1 char(10),@.Fruit2 char(10),@.Fruit3 char(10),@.Fruit4
char(10
Select top 4 fruits from tblFruits
How do I get the recordset into the variable?Sorry to answer you with another question but could you explain just
*why* you would want to assign the results to variables? Your reason
may have some bearing on the answer.
Your requirement is a bit unusual. SQL Server doesn't have arrays. The
main data structure is a table and it is hard work to manipulate lists
of variables just because that's not really what the declarative SQL
language was designed to do.
David Portas
SQL Server MVP
--|||Try one by one.
declare @.Fruit1 char(10)
declare @.Fruit2 char(10)
declare @.Fruit3 char(10)
declare @.Fruit4 char(10)
select top 1 @.Fruit1 = fruits from tblFruits
select top 1 @.Fruit2 = fruits from tblFruits
where fruits != @.Fruit1
select top 1 @.Fruit3 = fruits from tblFruits
where fruits != @.Fruit1 and fruits != @.Fruit2
select top 1 @.Fruit4 = fruits from tblFruits
where fruits != @.Fruit1 and fruits != @.Fruit2 and fruits != @.Fruit3
go
AMB
"Bob" wrote:

> Hello folks.
> I am running a series of queries in a stored procedure.
> For example, my first query might return a recordset like this
> Apples
> Oranges
> Pears
> Turnips
> I want to put those reults in variables
> So I might have
> Declare @.Fruit1 char(10),@.Fruit2 char(10),@.Fruit3 char(10),@.Fruit4
> char(10
> Select top 4 fruits from tblFruits
> How do I get the recordset into the variable?
>

get records after executing a stored procedure

Hi All,

I have a Execute SQL Task I get some values from a table onto three variables. Next step in a DFT, I try to execute a stored proc by passing these variables as parameters.

EXEC [dbo].[ETLloadGROUPS]
@.countRun =?,
@.startTime =?,
@.endTime = ?

This is the syntax i use, in the parameters tab of the DFT I ensured that all the parameters are correctly mapped.

When I run the package, it executes successfully but no rows are fectched. I tried running the stored proc manually in the database, and it seems to work fine.

Am I missing something here ? Please Advice

Thanks in Advance

I am sure it is a type issue. SSIS has a VERY VERY irritating feature of not telling you it can't convert your var to the SQL type you set in the parameters section, it just ignores it and sets it to nothing.

Try setting your vars to "String" types and your parameters in the task to "VARCHAR". I bet it will work.

You might also try setting vars inside the SQL to the ?. I have had issues where it doesn't like ? in certain places.

DECLARE @.count INT, @.stime datetime, @.etime datetime
SET @.count = ?
SET @.stime = ?
SET @.etime = ?

EXEC [dbo].[ETLloadGROUPS]
@.countRun =@.count,
@.startTime =@.stime,
@.endTime = @.etime|||

Tom,

Thanks for the quick response. but guess am into a soup here... I have done the following in the parameters tab of the Execute SQL Task.

varName Direction Datatype ParaName

user::countRun Input varchar 0

user:endDate input varchar 1

user:runDate Input varchar 2

In the result set , I have done the following,

Result Name variable Name

0 user::countRun

1 user:endDate

2 user:runDate

al the three variables are of the datatype String.

when I execute the package its now failing with the error, the type of the value assigned to the variable differs from the current datatype. I guess the values from the table which are int and date are not accepted in this parameter mapping. How to handle this?

Thanks for the help so far

|||It sounds like you don't have dates in the strings. What are the values of the parameters you are passing.

Try this:

SET @.count = CAST(? AS INT)
SET @.stime = CAST(? AS DATETIME)
SET @.etime = CAST(? AS DATETIME)

You could run the SQL Profiler and capture exactly the command it is running.

|||

When you say "no rows are fetched" how are you determining this?

what are you doing with the results of the sqltask?

|||

Hi Jeff,

My whole idea is to query a table, get three values onto three variables, pass these values to a stored procedure and then get the entire set of records returned by the proc and then insert it to another OLE DB Destination.

|||

Tom Phillips wrote:

It sounds like you don't have dates in the strings. What are the values of the parameters you are passing.

Try this:

SET @.count = CAST(? AS INT)
SET @.stime = CAST(? AS DATETIME)
SET @.etime = CAST(? AS DATETIME)

You could run the SQL Profiler and capture exactly the command it is running.

I am selecting all the three values from a table and then passing them to a stored procedure.

|||

Hi All,

I have solved all the issues, now at the last summit though its giving me an error with the OLE DB Source component where am calling the stored proc. The erorr is, A rowset based on the SQL Command was not returned by the OLE DB Provider.

Any idea to resolve this ?

Thanks in advance.

|||adding "SET NOCOUNT ON" at the start of the stored proc resolved the issue. Thx for all the help :)