Is there a way to do the following in a dts package?
Using the pubs db -
INSERT
INTO db1.dbo.authors
(au_id, au_fname, au_lname, city, state, zip, phone, contract)
SELECT au_id, @FIRSTNAME, @LASTNAME, city, state, zip, phone,
contract)
FROM
db2.dbo.authors


Where @FIRSTNAME and @LASTNAME are global variables that I can call in
additional tasks? The values are static, and I have just been
declaring them in each of my tasks, but That means I need to update
this static value in each task when we go live.
I'd like to make it global so I only have to change it once.


Basically, I'm migrating a ton of data from one system to another, and
the destination system has 6 static fields that are in EVERY table.
So, I have to declare these for EVERY Insert.

Yep. Proceed as follows:

1) Create an ExecuteSQL task, using your SQL Server connection.
2) Make the code as follows:

INSERT
INTO db1.dbo.authors
(au_id, au_fname, au_lname, city, state, zip, phone, contract)
SELECT au_id, ?, ?, city, state, zip, phone,
contract)
FROM
db2.dbo.authors

3) Assign the first parm to the global for first name. (use the
Parameters button)
4) Assign the second parm to the global for last name. (same here)

Needless to say, be sure to set the values for the globals.

HTH

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Sharon McMillon" <smcmillon (AT) gmail (DOT) com> wrote

Is there a way to do the following in a dts package?
Using the pubs db -
INSERT
INTO db1.dbo.authors
(au_id, au_fname, au_lname, city, state, zip, phone, contract)
SELECT au_id, @FIRSTNAME, @LASTNAME, city, state, zip, phone,
contract)
FROM
db2.dbo.authors


Where @FIRSTNAME and @LASTNAME are global variables that I can call in
additional tasks? The values are static, and I have just been
declaring them in each of my tasks, but That means I need to update
this static value in each task when we go live.
I'd like to make it global so I only have to change it once.


Basically, I'm migrating a ton of data from one system to another, and
the destination system has 6 static fields that are in EVERY table.
So, I have to declare these for EVERY Insert.

What about

INSERT
INTO db1.dbo.authors (au_id, au_fname, au_lname, city, state, zip, phone,
contract)
SELECT au_id, ?, ? city, state, zip, phone,contract)
FROM
db2.dbo.authors

You can then map a Global variable onto the ? using the Parameters button.




"Sharon McMillon" <smcmillon (AT) gmail (DOT) com> wrote

To reference a global variable in an Exec SQL Task, use the placeholder ?.

e.g.

SELECT * FROM T WHERE x = ?

Then click Parameters, to map the parameters detected to global variables.

--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

I can successfully use the placeholder '?' in a where clause, but when
I use it in the FROM part, I get a syntax error.

whoops - sorry - when I use it in the SELECT, I get a syntax error.

How about showing us the exact SQL code?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Sharon McMillon" <smcmillon (AT) gmail (DOT) com> wrote

whoops - sorry - when I use it in the SELECT, I get a syntax error.

If the table name is going to be dynamice, you cannot pass a variable into
the from statement. To do this, unfortunately you have to use dynamic SQL.

"Tom Moreau" wrote:

ActiveX Script Task based dynamic SQL generally works best for this-

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)

"Andy" <Andy (AT) discussions (DOT) microsoft.com> wrote

The exact code is the same as above:

INSERT
INTO db1.dbo.Authors
(au_id, au_fname, au_lname, city, state, zip, phone, contract)
SELECT au_id, ?, ?, city, state, zip, phone, contract
FROM db2.dbo.Authors

This gives me a syntax error. If I remove the '?' place holders, it
works fine.