June 16, 2004 at 3:16 pm
I have been using DTS for years, but have just recently started using Global Variables. I have a package with an ActiveX script where I would like to set a bunch of variables based on the value of a variable set in the dtsRun command. I want to get the variable name from an array, but DTS doesn't like it.
Can I use a variable for the variable name? Here's my code:
Function Main()
dim tables
dim count
dim table
dim library
dim newValue
library = DTSGlobalVariables("Library").value & ".dbo." & "<table>"
tables = split ("matterImport,clientImport,PeopleIgnore",",")
for count = 0 to ubound(tables)
table = tables(count)
newValue = replace(library,"<table>",table)
'this line doesn't work
DTSGlobalVariables(table).value = newValue
next
End Function
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
June 17, 2004 at 2:04 am
Your code is sound but the issue is that the variable is declared as a type variant whereas the DTSGlobalVariables requires a string. If you simply add some double quotes to implicitly convert the variant to a string then the code works fine.
i.e. DTSGlobalVariables("" & table).value = newValue
June 17, 2004 at 8:00 am
Wow! That worked. I've written VBScript for years and never came up against this problem before. Thanks. You saved me from writing some ugly code.
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
June 18, 2004 at 3:30 am
There is a problem on SQL2000 SP3 documented in KB815115 A DTS Package That Uses Global Variables Ignores Error Message Raised by RAISERROR. A fix is available on request from Microsoft.
Make sure you get a recent build of this fix. We originally got the fix built on a base of 8.00.869 which introduced a regression bug. We are currently testing a new version based on 8.00.928 and hope this will be OK.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply