Parameters in a DTS ?

[StevenK]

I've written a complex query that I want to have users run on a client site.
However I need them to have as little interaction as possible with SQL Server Query Analyser - they're currently running my script in Analyser and saving the grid results as .csv / .xls and manipulating therein.


Ideally I'd like to be able to set something up that's automated - such that a file is output on their server on a daily / weekly basis as required.

However the query absorbs some parameters which the user will need to input (for example - a date range).
Furthermore I'd like to be able to specify the file location according to a datetime stamp - so two output files never have the same name.

Can this all be done ?

Thanks in advance.
Steve

 

[mrdenny]

Take a look at global paramaters which can be set via input switches.

You will also want to look at the dynamic properties task which can automatically reset the file name you are exporting to.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[StevenK]

I'm familiar with neither global parameters / input switches / dynamic properties.
Can you provide any starting points / sample ?

Thanks again.
Steve

 

[mrdenny]

Start with BOL.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[rac2]

mrdenny,

Would you agree that it is necessary to be a competent ActiveX programmer in order to effectively use DTS?

I have spent a few hours trying to produce a DTS package to export records from SQL Server to Access within a date range. As this is a daily task it would be great to automate changing the date range values within the DTS.

BOL has no entry for input switch and the material on Dynamic Properties task led to my question.

At this point I have the following DOS batch file which I edit each day for the export. Is there a simple DTS solution to replace manually editing a batch file like this?

dtsrun /Shost_name /E /NXYZ_TableA /AdateBegin:8=2006-03-29 /AdateEnd:8=2006-03-30

dtsrun /Shost_name /E /NXYZ_TableB /AdateBegin:8=2006-03-29 /AdateEnd:8=2006-03-30

pause

 

[mrdenny]

I know nothing of ActiveX and have never run into anything in DTS that I can't make it do (usually fairly easily).

From your code it appears that you are pulling data down for yesterday correct?

This can easily be done via the Dynamic Properties.

You've already got global variables setup, so you are one step ahead.

In the DTS package at the begining, add a Dynamic Properties Task (DPT). Now edit the DPT, and click add. Select Global Variables, select the first variable and click Set at the bottom. In the Window, select Query from the drop down menu, select a SQL Server for the connection.

For the Query put in select convert(varchar(10), getdate()-1, 101) (or how ever many days back you want).
Click refresh to very that it's beinf formatted the way you want. Clock OK, then Close.

Click Add again and repeat for your second global variable.

Your queries should already be setup to use the global variables.

Now you can run your package without having to pass the variables.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[rac2]

That was very helpful, thank you.

After defining that DPT following your excellent instructions I attempted to specify the order of execution so that the Global Variables are set before the Transform Data Task runs.

Looking at the Design Package diagram, I selected the DPT widget and the Connection1 icon. From the Workflow menu I selected On Completion which added a dashed arrow between the two icons.

Next I will schedule this as a Job for SQL Server Agent, and all will be automated.

I could not have done this without your help. Thanks again.

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com