variables in sql command

variables in sql command

• JenLeonard

  g4rc wrote:

  hello It just not work here I still get the following in the value property "select * from dimAccount WHERE AccountDescription = '" + @Descr + "'" What must be the type of the variable

  
  
  String
  
  You're doing something wrong. The only things you need to change are the EvaluateAsExpression & Expression properties. Have you done that
  
  -Jamie
  
  
  
  

• shax

  g4rc wrote:

  Hello I have an existing DTS package and I convert this to Integration Services but now I have a problem: i have in my dataflow an OLE DB SOURCE and an OLE DB DESTINATION In the source I have the following sql command: IF '<<FullLoad>>' = 'Y' BEGIN  SELECT *  FROM  Maintenance_Departments END ELSE BEGIN  SELECT *  FROM  Maintenance_Departments  WHERE Last_Updator_Date_Time BETWEEN '<<StartDate>>' AND '<<EndDate>>' END The <<FullLoad>>, <<StartDate>> and <<EndDate>> are variables, these variables must be replaced  with a value of an settings_table. This is the way it happens in SQL 2005: I have an activex script with the following code For Each oTsk In oPck.Tasks If Left(oTsk.Description, 3) = "Extract" Then '*** SQL-Task *** sSQL = oTsk.CustomTask.SQLStatement sSQL = Replace(sSQL, "<<FullLoad>>", sFullLoad, 1, -1, vbTextCompare) sSQL = Replace(sSQL, "<<StartDate>>", sExtractionStartDt, 1, -1, vbTextCompare) sSQL = Replace(sSQL, "<<EndDate>>", sExtractionEndDt, 1, -1, vbTextCompare) oTsk.CustomTask.SQLStatement = CStr(sSQL) End If Next Is there also a way in Integration Services for this. Thx

  
  
  Yeah there is. In your OLE DB Source component select that the SQL command should come from a variable.
  For that variable set EvaluateAsExpression=TRUE and build your SQL statement using an expression making reference to your parameters. I use this technique ALOT and it works really well.
  
  The only hinderance is the lack of an expression editor on the Expression property of a variable but I think I've maybe complained about this once too often by now :)
  
  -Jamie
  
  

• bb_spinoza

  Allan
  
  can you give me the properties of your connection to the source
  
  Greetz
  

• dbloke

  And I've just come across this post from Ash: http://blogs.msdn.com/ashvinis/archive/2005/10/20/483147.aspx that explains this in more detail.
  
  -Jamie
  
  
  

• Jocke Intellibis

  The 'Value' property of a variable will show you what the expression evaluates to!
  
  -Jamie
  
  
  

• Dugan Porter - Microsoft

  If I put the following statement in the value:
  
  "select * from dimAccount WHERE AccountDescription = '" + @Descr + "'"
  
  I get the same in statement in the value properties
  
  So the problem is that the @Descr variable is not translated to the correct value
  
  Greetz
  

• csi95

  hello
  
  It just not work here
  
  I still get the following in the value property
  
  "select * from dimAccount WHERE AccountDescription = '" + @Descr + "'"
  
  What must be the type of the variable
  
  
  

• Steven .NET

  I put the same code as you Allan, but I get the error
  
  'Incorrect syntax near '+'
  
  Any idea
  

• Domingo Samper

  Here is an example I used to populate a variable (evaluate as expression = true) with input from another variable.
  
  The value I entered for @Descr was My Description(no quotes)
  
  This is the expression (with quotes - double)
  
  

  "select * from dimAccount WHERE AccountDescription = '" + @Descr + "'"

  
  
  
  Does this help
  
  
  Allan
  

• Carl72

  I am using an OLEDB connection
  
  OK When you enter the expression on the variable properties does it evaluate it correctly
  
  allan
  

• TonyMS

  g4rc wrote:

  If I put the following statement in the value: "select * from dimAccount WHERE AccountDescription = '" + @Descr + "'" I get the same in statement in the value properties So the problem is that the @Descr variable is not translated to the correct value Greetz

  
  
  Set:
  'EvaluateAsExpression'=TRUE
  'Expression'="select * from dimAccount WHERE AccountDescription = '" + @Descr + "'"
  
  'Value' should then have the correct thing in it. You should not be making explicit changes to 'Value'.
  
  -Jamie
  
  
  
  

• Johan

  And because of Jamie's point then here is an example of such an expression.
  
  Based on the value of the PivotVar variable I want to decide what the statement will be.  if it is 0 then the statement will be a straight SELECT.  If it is anything other than 0 then I will add a WHERE Clause.
  
  

  @[User::PivotVar] == 0    "SELECT * FROM HumanResources.Employee"  :  "SELECT * FROM HumanResources.Employee WHERE SalariedFlag = 0"

  
  
  Allan
  
  
  
  

• MCrw

  Hello
  
  I tried the following
  
  create two variables:
  
  - name: costcenter
     type:  string
     value:  "OBOK"
  
  - name: sql_command
     type: string
     value: 'select * from costcenters where costcenter = ' + @costcenter
     evaluateasexpression = TRUE
  
  In the dataflow in the ole db source i change data acces mode to sql command from variable = User::sql_command
  
  when I click the preview  button I get the following message:
  
  Statement could not be prepared
  Incorrect syntax near 'select * from costcenters where costcenter = '.''
  
  Can anyone help me
  
  Thx
  

• AmigaBlitter

  How can I evaluate the expression on the variable properties
  
• variables in sql command