Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DTSGlobalVariables


DTSGlobalVariables

Author
Message
disagree
disagree
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 1
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
ltuch
ltuch
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 1

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





disagree
disagree
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 1
no, actually i wanna use a globalvar in a sql script.

Thanks anyway.

sl3
ltuch
ltuch
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 1
What are you trying to achieve? In a sql script, you have local variables that you can use if writing a stored procedure.



disagree
disagree
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 1

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


Julian Kuiters
Julian Kuiters
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 1

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
smv929
smv929
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 1129

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
Aaron Myers
Aaron Myers
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 1
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 [table].field1 LIKE ?


I hope this helps!

-A



disagree
disagree
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 1
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.
Aaron Myers
Aaron Myers
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 1
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search