GVs, SQL task, and Active X?
-
Hi,
I am creating a package in which I have 2 global variables. I want to be able to access those global variables through an Execute SQL Task. The values of my variables come from a .ini file. At the moment, I insert the value of the variables into a temp table through one SQL task using parameters, and then through another I assign them to two variables declared within that task so that I can use them. This works fine
, but I'd rather not have to use the additional table or add the additional tasks to my DTS.Attempting to access and use the global variables directly with the ?, I get the following error:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Syntax error or access violation.
I guess that I am getting this error b/c SQL Server doesn't like how I want to use the global variables? This is why I am using the temp table now.
Here is an example of what I'm doing:
First SQL task:
INSERT INTO DTSParam (DTSName, ParamName, ParamVal)
VALUES ('NameOfDTS', 'NameofParam1', ?)
Then I set up the Parameter 1 for the ?. I have 2 of these, one for each of the 2 global variables.
Second SQL task:
DECLARE @DateUnit datetime --date value stored
DECLARE @MonthCount int --#added to months value
DECLARE @StartDate datetime --starting allocation date
DECLARE @MonthsOut int --#of months to allocate
--Set to 0 so that it starts with value of StartDate
SET @MonthCount = 0
--Set to the value inputted in the ini file that was added to the DTSParam table (this is where I'd like to say @MonthsOut = ?, or something similar)
SET @MonthsOut = (SELECT TOP 1 ParamValue FROM DTSParam WHERE DTSName = 'NameOfDTS' AND ParamName = 'NameOfParam1')
--Set to the value inputted in the ini file that was added to the DTSParam table (this is where I'd like to say @StartDate = ?, or something similar)
SET @StartDate = (SELECT TOP 1 ParamValue FROM DTSParam DTSName = 'NameOfDTS' AND ParamName = 'NameOfParam2')
WHILE @MonthCount < @MonthsOut
BEGIN
SET @DateUnit = DATEADD(mm, @MonthCount, @StartDate )
INSERT INTO Table1 (ID, Date, Value, TimeStamp)
SELECT sa.ID, @DateUnit, t2.Val, getDate()
FROM Table3 t3 INNER JOIN Table2 t2
ON t3.ID = t2.ID
AND t3.EmpD = t2.EmpID
SET @MonthCount = @MonthCount + 1
END
So, essentially, instead of setting @StartDate and @MonthsOut to a Select statement, I'd like to set them to the global variable directly... with a ? or something else... if possible.
I have read that you can use an Active X Script, but I haven't found an example script yet that makes sense for what I'm doing....
I understand using DTSGlobalVariables("Var1").Value and setting up the variables that way, but then do I just Dim strSQL1, Dim strSQL2, etc. all my SQL statements in the ActiveX Script, execute them there, and use that in place of the SQL task? Or am I calling statements from the ActiveX Script in the SQL task?I'm using SQL Server 2000. Can anyone offer any additional solutions or help?
I just don't see why it has to be so difficult...
Thanks for any help at all...
-
rmazzol,
You can get the values out of the .ini file and into the global variables using the DTS Dynamic Properties task.
You then assign the global variables to the local VB Script variables by executing the following statements in the ActiveX Script task:
@MonthsOut = DTSGlobalVariables("NameOfParam1").Value
@StartDate = DTSGlobalVariables("NameOfParam2").Value
Do not use the "Set" keyword. "Set" is only for objects, and local VB Script variables and DTS Global Variables are not objects.
-
Edwin, thanks for the reply. I am using a Dynamic Properties task to get my values out of the .ini file and into the global variables already.
The Active X script doesn't like the use of the "@" symbol for the variables. Even if I add the variables there without using the @ symbol, I still don't understand how I can reference them? How would my SQL task and the Active X script, "talk" to each other? Maybe I'm not asking the question right -- I've never had to use global variables for a DTS before...
-
I have had only one situation where using ? to assign a parameter via a global variable failed. That has since gone away but what I ended up with was an ActiveScript object that I ran first with I believ code like this.
Option
Explicit
Function
Main()
Dim oPkg, oTask, strQuery
Set oPkg = DTSGlobalVariables.Parent
Set oTask = oPkg.Tasks("Task_Name_Not_Description") ' you might have to use disconnected edit to find the actual name of the task.
strQuery = "SQL String GOes Here"
' Build your sql string here. Along with GLobal Variable concatinated in.
oTask.Properties("SQLStatement").value = strQuery
Set oTask = nothing
Set oPkg = nothing
Main = DTSTaskExecResult_Success
End
Function
I would basically alter the SQL Statement of the task I couldn't assin the variable into. Worked fine for my issue.
-
rmazzol,
Sorry, I got the ActiveX and ExecuteSQL syntaxes confused in my head. Plus, I did misunderstand your problem.
-
It makes much more sense now! Thanks, Antares686. I was attempting to do something similar that I found online elsewhere (sans the .Properties ("SQLStatement").Value part), but I didn't realize that I was supposed to be using the actual name of my task, I just thought "DTSTask_ExecuteSQLTask" was what was supposed to go there as a "standard" name for the kind task... So, aside from it just not working, that's why I was wondering how one was even able to "talk" to the other.Thanks to both of you for your replies!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply