Passing a Global Variable Into a SQL Script
-
I have a global variable in a DTS package that I need to concatenate with another value that is in a select SQL statement. Any idea how I can do this in a SQL task or Active X script???
-
Answered my own question - See example below.
' 205 (Change SourceSQLStatement)
Option Explicit
Function Main()
Dim oPkg, oDataPump, sSQLStatement
' Build new SQL Statement
sSQLStatement = "SELECT * FROM dbo.employee WHERE hire_date > '" & _
DTSGlobalVariables("HireDate").Value & "'"
' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQLStatement
' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing
Main = DTSTaskExecResult_Success
End Function
-
Is there any reason you couldn't just use
SELECT * FROM dbo.employee WHERE hire_date > ?
Then assign the global variable using the Parameters button?
--------------------
Colt 45 - the original point and click interface
-
However when you have a bit more complex SQL statement assigning the global variable with ? dos not work. At least I have to use DTSGlobalVariables.Parent when using a global variable in a sub query. It is not very comfortable when you need to change a statement.
Do You know any other solution?
-
Post your SQL statement and table details so we can look at it.
--------------------
Colt 45 - the original point and click interface -
I cant't use a global variable in DTS transformations in sql statements like:
Select A.*,B.*
FROM
(SELECT * FROM C WHERE C.X > ? AND C.Y <?) AS A
LEFT OUTER JOIN
(SELECT * FROM D WHERE ..................) AS B
ON A.H = B.H
I konw could move the where clause to the main select or do it step by step but in more comlpex statements it takes time.
-
There are conditions such as the subquery example previously where the parser has trouble using parameter assignment. You have no choice but to build the string in Script and load to your object. This recent topic was on the same thing http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=159368#bm159425
-
Krystian,
You can't assign parameters there, because technically they aren't parameters. Parameters are passed into stored procedures, and that is just a query. If you place that query in a stored procedure with two parameters and reference them that way, you should be fine.
-
I was able to get this to work just like the example I gave at the top of this post. I passed in the global variable directly into a SQL string. You can also assign the global to a string in your Active X script and then concatenate the string into the sql. See below for another example:
' 205 (Change SQLStatement)
Option Explicit
Function Main()
Dim oPkg, oExecSQL, sSQLStatement
' Build new SQL Statement
sSQLStatement = "INSERT dbo.NewEmployees" & vbCrLf & _
"SELECT * FROM dbo.employee" & vbCrLf & _
"WHERE hire_date > '" & DTSGlobalVariables("HireDate").Value & "'"
' Get reference to the Exec SQL Task
Set oPkg = DTSGlobalVariables.Parent
Set oExecSQL = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_2").CustomTask
' Assign SQL Statement to Exec SQL Task
oExecSQL.SQLStatement = sSQLStatement
' Clean Up
Set oExecSQL = Nothing
Set oPkg = Nothing
Main = DTSTaskExecResult_Success
End Function
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply