Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Import/Export (DTS) and Replication (2000)  sql task & global variables question

Author  Topic  rmazzol Starting Member 1 Post Posted - 2005-02-01 : 14:08:57 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. I would like to avoid having to do it this way, but at the same time, not have to use an ActiveX script, if possible. Without using the first SQL task to insert the variables into a temp table, I get the following error: Error Source: Microsoft OLE DB Provider for SQL Server Error Description: Syntax error or access violation. Am I getting this b/c you are not allowed to access global variables via an SQL task? 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 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 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. Is it? Thanks.