Help with assigning variables to from a SQL query

I've reconfigured Microsoft's IBS Store shopping cart to function within a small e-commerce website. What I am trying to do is to modify the code slightly in order to use a third party credit card processing center. The situation is this: once the customer clicks the final "check out" button, a stored procedure writes all of the product ordering information into the database. I, then, capture what they're wanting to purchase with the following SQL statement:

Dim strSQL as String = "Select orderID, modelNumber from orderDetails" & _
"where CustomerID = " & User.Identity.Name & _
"And orderid = (SELECT MAX(orderid)FROM orderDetails" & _
"where CustomerID = " & User.Identity.Name & ")"

What I would like to do is assign specific values to variables based off of the above query. For example:

Dim orderItem as String = (all of the modelNumbers from the query)
Dim orderIdItem as String = (all of the orderIDs from the query)

How do I do this?? Any help is much appreciated! Thanks in advance.

RonI'm not fluent in VB, but try to grasp the outline of the code :)

[code]
Dim myConnection As SqlConnection = New SqlConnection("..my connection string..")
Dim myCommand As SqlCommand = New SqlCommand("the query...")
myCommand.Connection = myConnection
myConnection.Open()
Dim myReader As SqlDataReader = myCommand.ExecuteReader()

'String Builder objects speed up performance, as strings are immutable.
Dim modelNumbers As System.Text.StringBuilder = New System.Text.StringBuilder
Dim orderID As System.Text.StringBuilder = New System.Text.StringBuilder

' Go through all the rows of the query
While (myReader.Read())
modelNumbers.Append(myReader.GetString(0)) 'Add the ModelNumber of the row to the string
orderID.Append(myReader.GetString(1)) ' Add the OrderID of the row to the string
End While

myReader.Close()
myConnection.Close()
[/code]

HTH|||Thanks for your help.

The code creates this error: "Specified cast is not valid", from the following line:

modelNumbers.Append(myReader.GetString(0)) 'Add the ModelNumber of the row to the string

Any ideas?

Again, thanks for your help!

Ron|||The field is either null or its a byte field or something.

I would check if its null first before adding it to the string.

HTH
Tony|||It doesn't come back null. To test this I simply bound the query data to a datagrid in order to display the information on the page. There are no null items; only data...as expected|||Try

modelNumbers.Append(myReader(0).ToString())

Does this help|||This worked beautifully.
Thank you!

Help with assigning values to variables

With some help Jacco Schalkwijk (SQL Server MVP), I've been able to query
accurate data from my shopping cart database. This query collects customer
purchase information based on the current session and userID. The query
Dim objCommand as SqlCommand = New SqlCommand("Select orderID, modelNumber from orderDetails where CustomerID = " &
User.Identity.Name & " And orderid = (SELECT MAX(orderid) FROM orderDetails where CustomerID = " &
User.Identity.Name & ")"
Now that I've collected all the orderIDâ's associated with the current customer,
I would like to assign specific values to variables based on this query. I would
like to create something like the following
Dim orderItem as String = (all of the modelNumbers from the query)
Dim orderIdItem as String = (all of the orderIDs from the query)
How do I do this' Any help is much appreciated! Thanks in advance.
Ro=?Utf-8?B?Um9u?= (ron.r.williams@.comcast.net) writes:
> I would like to assign specific values to variables based on this query.
> I would like to create something like the following:
> Dim orderItem as String = (all of the modelNumbers from the query)
> Dim orderIdItem as String = (all of the orderIDs from the query)
> How do I do this' Any help is much appreciated! Thanks in advance.
You would have to iterate over the dataset to build this string. So this
is more of a VB .Net question than an SQL question. I should add that
doing this in SQL is possible, but it is not a good idea.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

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