Populate a variable

Populate a variable

• vaioks

  Thanks all - finally I got it to worrk.

  
  

• cislom

  Public Sub Main()

  MsgBox(Dts.Variables("RutaFicheroCarga").Value)

  Dts.TaskResult = Dts.Results.Success

  End Sub

  
  
  

• jakorakosaya

  If you retrieve your record using the Execute Sql Task...

  1. Double-click to edit properties.
  2. In the ResultSet drop down choose Single Row
  3. In the ResultSet tab map the result to a Variable
  4. In the constraint editor just put variable == 0

  Is this what you are looking for

  
  
  

• John Campbell-Higgens

  sqlCmd = New SqlCommand(sSql, sqlConn)

  sqlParam = New SqlParameter("@Name", SqlDbType.Bit)

  sqlCmd.Parameters.Add(sqlParam)

  With sqlCmd

  if Dts.variables("variable").value == "Y" then

  .Parameters("@Name").Value = True

  else

  .Parameters("@Name").Value = False

  .executenonquery()

  ..

  ..

  
  
  

• Iam_Lio

  If you have one record, you actually don't need a dataflow unless you're trying to transform the data from Y/N in one table to True/False in another, which wouldn't require a variable. Note that you also don't need to change Y and N to booleans unless you really feel strongly about it. You can just put a condition such as @[myVar]=="Y" as an expression in the constraint editor.

  Just create an Execute SQL Task, double click it to open the editor.

  The trick is to create the SQL so it will return a bit value for true or false, on the General page of Execute SQL Task Editor:
  Enter your SQL:SELECT CASE WHEN fieldname='Y' THEN 1 ELSE 0 END FROM tablename

  And since there is always more than one way to do something, you can also take Y and N in directly (say to variable @Test) and have another expression variable (say @Condition) that will be True or False depending on the Y or N :)

  Make your result set: Single Row

  In the Resultset map Result Name 0 to a variable of your choosing, make sure the variable type agrees with the type being returned, Boolean for bit, Dates, Strings, Integers, etc.
  If you have multiple single record values you can map them by just changing the Result Name to be the 0 based index value of the column in the result set.

  I.e. SELECT 'A', 'B','C' would have 3 fields with indexes of 0, 1, and 2 all three of which could be mapped to different variables.

  Define the variables you use at at least one scope higher than the Execute SQL Task so you can use them in subsequent steps.

  If you have several records, you can process them as well.

  Make your result set: Full Result Set
  On the Resultset page map Result Name 0 to a variable defined as type object.
  Use a For Each to process each record mapping fields to variables similarly using a ForEach ADO Enumerator.

  Hope this helps.

  Larry C.

  
  
  

• Julien Roger

  oops, i'm sorry.

   

  Dts.Variables("RutaFicheroCarga").Value = "hi"

  
  
  

• prk

  Thanks for all the answers.

  Guess my brain is turning soft because I am still unable to get it to work :)

  Let me try to explain again.

  I have a single record in a table - the Value can be 1 or 0. Let us call the field "Check"

  I like to use this value to make a check in my control flow in the Constrain Editor.

  So if value is 1 then continue with workflow and if value eq 0 then stop work flow.

  My problem is I do not know how to write this statement in the Constrain Editor.

  For a start I have selected - "evaluation expression" = Expression, but I am not sure what to put in the expression field. Please specify the full statement that will resolve my issue

  Thanks alot,

  
  

• Helen Cool Granny

  Thanks for answer but not sure I can get it to work.

  Let me be more specific.

  I got one record in my table and the "colunm name" can either be "Y" or "N".

  However I like to convert this Value into my variable "Verify"

  So if "colunm name" is "Y" => Verify = TRUE

  if "colunm name" is "N" => Verify = FALSE

  Thanks in advance for helping me.

  
  

• Superobotz

  Thanks for answer.

  I am trying your suggestion - but I am not sure how to populate the "result".

  If I make a statement like this:

  Select field from table where 1=1

  (only one record)

  How does the value of "field" get transfered to "result" ( which again will lead to populate the "variable")

  Thanks for advise
  

  
  

• alien-

  For a single row result set, you map fields with their "offset". So for example if I have Select field1, field2, field3 FROM table, In the result set, I will have three mappings.

  ResultName: 0 Variable name: myVariable1 maps field1 to myVariable1
  ResultName: 1 Variable name: myVariable2 maps field2 to myVariable2
  ResultName: 2 Variable name: myVariable3 maps field3 to myVariable3

  The result name is the offset of the field from the first returned field (so the first field is 0 since there are 0 fields between the first field and itself)

  If you are dealing with a full result set (multiple records), ResultName is always 0 (at least currently) and the Variable name refers to an object.

  If you haven't seen it yet, you will probabbly find the following link highly helpful from time to time:
  http://www.databasejournal.com/article.php/1503191

  It's a (currently) 34 part series by Marcin Policht on various aspects of building SSIS packages, great stuff.

  Larry
  

  
  
  
• Populate a variable