November 15, 2001 at 6:50 pm
I am working on a project that is transfering data beween an AS400 and a flat file using SQL 2000 DTS. The company wants me to set up variables so that the servername and database name can be changed within the DTS package. This way they can use one DTS package for several different servers and databases. I have been doing some reading and I believe that the way to do this is to set up global variables and create an ActiveX Stripting task. How do I get the Execute SQL Task to see the global variable? Can someone please walk me through the steps of how to set this up?
Thanks-
Elizabeth
Elizabeth
Elizabeth
November 16, 2001 at 3:25 am
In the SQL statement Task use a ? in the where clause. Then click on the parameters button to map this to a global variable. Once the Global Variable has been created, it can be refered to in the ActiveX script via the DTSglobalvariables collection (you'll see it in the browser).
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
November 16, 2001 at 7:07 pm
The only problem is that the variable is the database and server name. I tried putting ? but it would not take it if it was before the where clause.
Elizabeth Hague
Elizabeth
Elizabeth
November 19, 2001 at 2:47 am
It sounds like the whole sql statement needs to be treated as dynamic. In this case, I'd look at using a AcxiveX task to reassign the sql text before the executesql task runs. This sql text is a property of the executesql task: "SQLStatement".
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
November 19, 2001 at 10:12 am
In SQL 2000, you can use the Dynamic Properties task to change any value in the package. This can some from a db, ini file, global variable, etc.
Steve Jones
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply