Dynamic Table Name 1

[klaforce]

Hi all,
I am trying to write one DTS package that can be used for multiple databases. I have about 13 Databases with names called *_SGEP where * is a three letter combination. They all have the same Table TblSafety with the same column names.

What I am trying to do is get an extract to an excel spreadsheet from the databases (where the table name is passed in as a global variable), but I don't want to make a package for each database. Thus, in the Transform data task I am trying to do something like the following:

Select *
FROM         ?.dbo.TblSafety
ORDER BY SPSCaseNo

Is there a way to get the ? to match the table name that I pass in(the global variable)? Right now it just throws an error when it tries to parse the SQL (Syntax Error). Thanks for your help in advance.

Keith

 

[gradley]

Try this:

Declare @strDB as varchar(25)
Declare @strSQL as varchar(1000)

SET @strDB = ?

SET @StrSQL = 
'
Select *
FROM ' +   @strDB + '.dbo.TblSafety
ORDER BY SPSCaseNo
'
exec (@StrSQL)

It still won't parse because of DTS quirky-ness but it should run for you.

 

[klaforce]

Hi,
Thanks for the response, however since it does not parse, it won't let me click OK to save the Transformation.

Do I have to edit it somewhere else besides the Transform Data Package properties?

Thanks again.

Keith

 

[gradley]

Oh yeah, that's right. The Transform data task needs it to parse to determine the destination fields. You can fake out DTS in the Execute SQL task but not the Transform task. I recall a previous post on this very issue. I believe the solution was to wrap a stored procedure around the script. and to pass the ? to the stored procedure.



Here is another option that will probably work better for your situation:

1. Specify the Select statement in the Transform Task for one of the valid Databases
2. Create a global variable with the entire select statement to the DTS package in a Global Variable.

3. Add a Dynamic Properties Task to your DTS (This allows you to override task properties at runtime).
4. Click Add to add a dynamic property
5. Expand the Tasks\DTSTask_DTSDataPumpTask_1\Transformations\DTSTransformation_1\Transform Server Properties folder on the left
6. Double Click the "Text" property on the right
7. Change the Source to Global Variable
8. Specify the Global Variable created above.

I hope this makes sense to you!!

Good Luck.

 

[klaforce]

Thanks a lot, I actually wen the stored procedure route, it worked great.

Keith


Keith

http://www.clemson.edu/~davidla

The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents.