Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

DTSGlobalVariables Expand / Collapse
Author
Message
Posted Monday, August 16, 2004 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 22, 2004 8:42 AM
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
Post #131980
Posted Tuesday, August 17, 2004 5:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 10, 2005 10:04 PM
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




Post #132229
Posted Tuesday, August 17, 2004 8:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 22, 2004 8:42 AM
Points: 8, Visits: 1
no, actually i wanna use a globalvar in a sql script.

Thanks anyway.

sl3
Post #132281
Posted Tuesday, August 17, 2004 9:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 10, 2005 10:04 PM
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.


Post #132301
Posted Tuesday, August 17, 2004 6:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 22, 2004 8:42 AM
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

 

Post #132439
Posted Wednesday, August 18, 2004 1:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, June 12, 2007 12:49 AM
Points: 419, 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
Post #132475
Posted Wednesday, August 18, 2004 12:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:35 AM
Points: 149, Visits: 1,054

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
Post #132645
Posted Thursday, August 19, 2004 7:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 30, 2006 2:07 AM
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



Post #132826
Posted Thursday, August 19, 2004 8:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 22, 2004 8:42 AM
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.
Post #132841
Posted Thursday, August 19, 2004 8:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 30, 2006 2:07 AM
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



Post #132855
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse