DTSGlobalVariables
-
hi,
how can i to use the DTSGlobalVariables on Sql scripts?
I mean, i'm using a Dts with a date variable and I must to modify everytime when is necessary in all the scripts on Dts.
By the other hand, this date isn't the current date always, so i'm a little bit complicated.
Thanks a lot.
Sorry my english
-
If I understand what you're asking:
You want to be able to manipulate a Date global Variable to where you can enter in the date (if the date isn't coming from the database). Just set up a global variable named "gv_Date" and you can do an ActiveX Script for that with the following:
Function Main()
dim a
a=inputbox("What is the date?")
if a <> "" then
DTSGlobalVariables("gv_Date").Value=a
end if
Main = DTSTaskExecResult_Success
End Function
-
no, actually i wanna use a globalvar in a sql script.
Thanks anyway.
sl3
-
What are you trying to achieve? In a sql script, you have local variables that you can use if writing a stored procedure.
-
hi dude, thanks 4 your answer.
but i need to do something like this:
select * from table where fieldvalue= <dtsglobalvar1>
u got it?
i need to use an external parameter to find data on my db.
thanks a lot.
sl3
-
put the SQL you want to execute into a stored procedure.
In the DTS Execute SQL step put something like:
EXEC mystoredprocedure @myglobalvar = ?
Then hit the "Parameters" button. Select the global variable you want to put in the parameter.
For some reason, the Execute SQL steps will only see the parameter marker (a question mark) when you are executing a sproc.
I wish it was easier!
Julian Kuiters
juliankuiters.id.au -
One way is to use the xp_cmdshell to call the DTS command line command. For instance, for the command line command...
DTS /Usa /Ppassword /Sserver /Npackage_name /Mpackage_password
/A"SQLCatalog:8=dist" /A"SQLTable:8=dist.dbo.co" '
which uses two global variables that are char type (SQLCatalog and SQLTable), you would wrap the above statement in
xp_cmdshell 'DTS /Usa ...etc.'
You can also query a package using...
SELECT * FROM OPENROWSET('DTSPackageDSO',
'/Usa /Ppassword /Sserver /Npackage_name /Mpackage_password
/A"SQLCatalog:8=dist" /A"SQLTable:8=dist.dbo.co", 'Select * from DTSStep_DTSDataPumpTask_1')
where the first argument ('DTSPackageDSO') is the provider info for DTS packages. It seems like performance is not great, but it may be due to the fact my packages refer to a non-sql database that is large.
smv929 -
This is a simple question with a simple answer.
1) Open DTS job in design mode.
2) Package -> Properties -> Global Variables tab
3) Add your variable and a default value
4) Open your data transform task (or wherever you have a SQL string)
5) Click Parameters and choose your global variable
6) ? is the parameter notation to use. Example:
WHERE
.field1 LIKE ?
I hope this helps!
-A
-
hi, I'm sorry but Parameters option dosn't exist. I'm using Sql Server 7(Enterprise Manager). Perhaps that option belongs to another version, but I don't have another available one.
thanks a lot.
-
We still had 1 SQL 7 box laying around so I checked. I don't see a way to do this in 7 so perhaps it was a new feature in 2000. I think you will have to use ActiveX scripts if you want to automate using the global parameters.
You can do an OLEDB/ADO recordset right within the ActiveX script if you want. Something like...
dim oConn, oRS, sql
set oConn = CreateObject("ADODB.connection")
set oRS = CreateObject("ADODB.recordset")
oConn.Open "Provider=SQLOLEDB;Data Source=YourDBServer;Initial Catalog=YourDBName;User Id=YourDBAccount;Password=YourDBPWD;"
Function Main()
sql = "select * from yourtable where yourfield = '" & DTSVar & "'"
oRS.Open sql
... code to do something with recordset ...
end function
-A
-
I haven't had a problem using the global variables with actual SQL statements. The stored procs are probably a better way to go anyhow.
-A
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply