sql server dts: 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.

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.

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
..
[quoted text, click to view]
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.

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
..
[quoted text, click to view]
whoops - sorry - when I use it in the SELECT, I get a syntax error.

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.




[quoted text, click to view]

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

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)

[quoted text, click to view]

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.

[quoted text, click to view]