Help required using Variables and Comparing them in SSIS

Hi i am New to ssis and Scripting in ssis what i was trying to do was to compare few variables which i populate using execute sql tasks and determine which process needs to kick off based on Task success or Failure

are the variables i am populating Mapping them to result set in execute sql task.

This is the criteria for script task to Fail or success

I am not able to populate the varibales with the Values from Execute Sql and also i am encountering errors in the script task.

I am Declaring ex: myvar11 as variables and then using it , please help me as i am new and guide me for the right approach. and can this logic be implemented in ssis for example like && operator used in script.
End IF

I've done something similar to this, albeit much simpler.

I created an Execute SQL task to select a single row from a table.

In the SQL Task Editor, General, I specified ResultSet = Single row, SQLSourceType = Direct Input, and entered the Select SQL in the SQLStatement field.

In the Result Set pane, I mapped the Result Names to the Variable Names using 0 for column1, 1 for columnn 2 etc.

Then in the Precedence Constraint Editor, I selected Evaluation Operation = Expression and Constraint, Value = Success, and entered my expression using the Variable Names.

The result of the expression controls the conditional execution of one of the two following data flows

|||

We have done something similar in a script task, you should be able to call the required variables using something similar to below

If Dts.Variables("gvMedSumFileMode".ToString() = "P" && Dts.Variables("gvMedSumFileType").ToString() = "U" then

if cint(Dts.Variables("gvMedSumFileVolume").ToString()) = cint(dts.Variables("gvMedCtrlFileVolume").ToString()) + 1 then

Dts.Task.Result = success

....

....

....

|||

Dev2624 wrote:

Hi i am New to ssis and Scripting in ssis what i was trying to do was to compare few variables which i populate using execute sql tasks and determine which process needs to kick off based on Task success or Failure

gvMedSumFileMode

gvMedSumFileType

gvMedSumFileVolume

gvMedCtrlFileVolume

gvMedSumSupplementNumber

gvMedCtrlSupplementNumber

are the variables i am populating Mapping them to result set in execute sql task.

This is the criteria for script task to Fail or success

If myvar11("gvMedSumFileMode").ToString = "P" && myVar12("gvMedSumFileType").ToString = "U" Then

If CType(myVar13("gvMedSumFileVolume").Value, Integer) = CType(myVar14("gvMedCtrlFileVolume").Value, Integer) + 1 Then

DTS.Task .Result = success

else

if CType(myVar13("gvMedSumFileVolume").Value, Integer) = CType(myVar14("gvMedCtrlFileVolume").Value, Integer) && CType(myVar15("gvMedSumSupplementNumber").Value, Integer) = CType(myVar16("gvMedCtrlSupplementNumber").Value, Integer) + 1 then

DTS.Task .Result = success

else
DTS.Task .Result = Failure

End if

End if

else

DTS.Task .Result = failure

I am not able to populate the varibales with the Values from Execute Sql and also i am encountering errors in the script task.

I am Declaring ex: myvar11 as variables and then using it , please help me as i am new and guide me for the right approach. and can this logic be implemented in ssis for example like && operator used in script.
End IF

I agree with the first answer; using expressions in the precedence constratint is the best way to 'direct' the traffic in your control flow, as they are easier to maintain and implement. Make sure you research on 'Precedence constraints'.|||

When you are using DTS.Variables are you writing them in the main page of script task under readonly Variables or declaring the variables as MyVariables is the right approach, also the Previous reply to this that i have followed the same method and trying to populate the variables , Can you please guide me as to how can we use these as expressions(in my case)

where conditions are for ex if varx="p" && vary="u"

then if varz= varc+1 success

or other case is if varx="p" && vary="u"

then if varz=varc

if varg=varh+1

success

how do we acheive these conditions in expressions.

|||

Also when i am trying to use && to evaluate both the variables at once it is not allowing me to do it , its throwing an error as expression expected .The questions i might ask might be trivial to you but please help me as i am a newbie and learning the steps ..

also is cint is the right word since the variable gvmedsumfilevolume is int in type. and i am trying to compare two integer values in the expression.

|||

Sorry I was getting confised between c# and vb.net. Below is the correct code, the variables that you are using within the script must also be placed in the "ReadOnlyVariables" property on the "Script Task Editor" > "Script" page. They should be in the format

gvMedSumFileMode,gvMedSumFileType,gvMedSumFileVolume",gvMedCtrlFileVolume

Note that there are no gaps between the commas and the next variable. Not sure if this is a bug but we found that it would ignore the remaining variables if spaces were included.

If Dts.Variables("gvMedSumFileMode").Value.ToString() = "P" And Dts.Variables("gvMedSumFileType").Value.ToString = "U" Then

If CInt(Dts.Variables("gvMedSumFileVolume").Value.ToString()) = CInt(Dts.Variables("gvMedCtrlFileVolume").Value.ToString()) + 1 Then

Dts.TaskResult = Dts.Results.Success

Else

If CInt(Dts.Variables("gvMedSumFileVolume").Value.ToString()) = CInt(Dts.Variables("gvMedCtrlFileVolume").Value.ToString()) And _

CInt(Dts.Variables("gvMedSumSupplementNumber").Value.ToString()) = CInt(Dts.Variables("gvMedCtrlSupplementNumber").Value.ToString()) + 1 Then

Dts.TaskResult = Dts.Results.Success

Else

Dts.TaskResult = Dts.Results.Failure

End If

End If

Else

Dts.TaskResult = Dts.Results.Failure

End If

|||

You should be using the variabledispenser... BEWARE :-)

http://blogs.conchango.com/jamiethomson/archive/2007/08/28/Beware-of-variable-usage-in-script-tasks.aspx

|||

This is the code that i am using and the task is failing is this the right way to do this . I am populating the variables using sql direct input and mapping them into the result set.

Variable names have changed and made these changes in the code. Please tell me how to resolve this

MedSumFileMode String

MedSumFileType String

MedSumFileVolume int32 rest all variables are type int32.

|||Dev, please share the error message. That would help, don't you think?

Also, You don't want LockOneForWrite unless you're only working with one variable. In this case you're not, so you'll want to use LockForWrite instead. If you're not writing to the variables, use LockForRead. Search for examples, but for starters:

Code Snippet

...

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode",vars)
Dts.VariableDispenser.LockForRead("MedSumFileType",vars)
Dts.VariableDispenser.LockForRead("MedSumFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber",vars)
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber",vars)
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

|||

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode",vars)
Dts.VariableDispenser.LockForRead("MedSumFileType",vars)
Dts.VariableDispenser.LockForRead("MedSumFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber",vars)
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber",vars)
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

when i use this it highlights an error saying too many arguments Public SubLock For Read(variable As String)

|||

If i use Dim vars As Variables
Dts.VariableDispenser.LockOneForRead("MedSumFileMode",vars)
the error goes away. is this the right approach.

ill post back with the error details

|||

Dev2624 wrote:

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode",vars)
Dts.VariableDispenser.LockForRead("MedSumFileType",vars)
Dts.VariableDispenser.LockForRead("MedSumFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber",vars)
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber",vars)
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

when i use this it highlights an error saying too many arguments Public SubLock For Read(variable As String)

Yep, I'm not quite awake yet this morning.

Code Snippet

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode")
Dts.VariableDispenser.LockForRead("MedSumFileType")
Dts.VariableDispenser.LockForRead("MedSumFileVolume")
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume")
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber")
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber")
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

|||

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)

at ScriptTask_08359122705d4affbf377ddc743eea59.ScriptMain.Main() in dts://Scripts/ScriptTask_08359122705d4affbf377ddc743eea59/ScriptMain:line 30

this is the error .

|||

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode")
Dts.VariableDispenser.LockForRead("MedSumFileType")
Dts.VariableDispenser.LockForRead("MedSumFileVolume")
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume")
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber")
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber")
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

The problem with this method is that the vars collection does not take the string name of the variable it will only take the index of the variable, hence the code becomes difficult to read

If vars(0).Value.ToString = "P" And vars(1).Value.ToString = "U" Then.......

Help regarding passing parameters in SQL and Lookup

Hi,

1) I am using exceute SQL tasks in my control flow. 3 variables have been defined at the package level.They are mapped to 3 parameters respectively in the Execute SQL task.

When I try using these parameters in SQL error is thrown.Query is not getting parsed.My connection is OLEDB. Target and source are in SQL Server.

Can anyone suggest a workaround?

2) Before loading my target I need to define a Lookup . My requirement is if say consumer key matches in fact table then update it else insert.

2 kinds of lookup are available in SSIS dataflow tools. Simple Lookup for exact matching and Fuzzy Lookup for matching based on probability.

Neither of it supports my requirement? Can i put a select and insert query directly in Lookup or will need to call it from a file as a stored procedure?

Please suggest a solution for this too.

Thanks in advance.

Regards,

Aman Anand

aman anand wrote:

Hi,

1) I am using exceute SQL tasks in my control flow. 3 variables have been defined at the package level.They are mapped to 3 parameters respectively in the Execute SQL task.

When I try using these parameters in SQL error is thrown.Query is not getting parsed.My connection is OLEDB. Target and source are in SQL Server.

Can anyone suggest a workaround?

It'd be alot easier to work out what's going on if you told us:

The full error message|||

Jamie,

Thanks for your reply. My query is mentioned below:

INSERT INTO AUD_PROCESS_CONTROL
(LOAD_ID, MASTER_SEQ_ID, PROCESS_START_TIME, PROCESS_END_TIME, SOURCE_RECORD_COUNT, LOAD_RECORD_COUNT,
UPDATE_RECORD_COUNT, FAIL_RECORD_COUNT, EXTRACT_FROM_DATE_TIME, EXTRACT_TILL_DATE_TIME, LOAD_CONTROL_STATUS)
VALUES (1, ?, GETDATE(), GETDATE(), ?, ?, 0, 0, GETDATE(), GETDATE(), 'LR')

Audit table is being maintained to keep track of ETL parameters.

MASTER_SEQ_ID

SOURCE_RECORD_COUNT and

LOAD_RECORD_COUNT

are the 3 variables defined as int32 and the scope is at package level.

They are mapped to 3 parameters which are also defined with datatype as int.

error is something like it says unable to parse the parameters in the query!

will look at the links mentioned in your reply.

thanks again.

Regards,

Aman

|||

aman anand wrote:

error is something like it says unable to parse the parameters in the query!

I meant to copy and paste the error message!

But regardless, you should be able to achieve this using an expression. The links I provided earlier will help.

-Jamie

|||

Jamie,

Sorry for not copy pasting the error before here goes the entire stuff.

INSERT INTO AUD_PROCESS_CONTROL
(LOAD_ID, MASTER_SEQ_ID, PROCESS_START_TIME, PROCESS_END_TIME, SOURCE_RECORD_COUNT, LOAD_RECORD_COUNT,
UPDATE_RECORD_COUNT, FAIL_RECORD_COUNT, EXTRACT_FROM_DATE_TIME, EXTRACT_TILL_DATE_TIME, LOAD_CONTROL_STATUS)
VALUES (1, @.P_MASTER_SEQ_ID, GETDATE(), GETDATE(), @.P_SOURCE_RECORD_COUNT, @.P_LOAD_RECORD_COUNT, 0, 0, GETDATE(), GETDATE(), 'LR')

Error:

TITLE: SQL Task

The query failed to parse. Must declare the scalar variable "@.P_MASTER_SEQ_ID".

Variables have been defined as int64 and while mapping them to parameters datatype has been given as Long.

Regards,

Aman

|||

Again, try using an expression.

-Jamie

|||

Sometimes it works even though the parser says opposite

Another solution is to build the complete query in a varaible

Turn Evaluate as an expression to True and build it like

"SELECT Dato, Kurs from [" + @.[User::Kurstype] + "$] WHERE (NOT (Kurs IS NULL))"

|||

Hi,

Thanks for your invaluable replies jamie and cgpl.

I tried another workaround for my problem. Used ADO.NET connection manager for the query which was posing problems and passed the parameters as @.P_......,

Now this issue has been sorted.

Thanks again.

Regards,

Aman