MS SQL Server DTS - DTS Select and global variables / parameters

Author

DTS Select and global variables / parameters

bnhcomputing

2005-09-28, 8:24 pm

I have a DTS package. The select for one of the transform data tasks look
like:

SELECT * FROM UDF1('1/1/2005','12/31/2009')

right now the dates are hard coded. Is there a way to pass parameters to
the DTS package to change the dates? Can I use DTS global variables?

Any other ideas how to do this?

Thanks
--
bnhcomputing

Allan Mitchell

2005-09-28, 8:24 pm

You may be able to replace the dates in the Query with ? Placeholders.
You can then use the parameters button to map onto them a Global
Variable. If not then you can build the statement yourself which is the
long way around like this

[url]http://www.google.com/search?sourceid=navclient&ie=UTF-8& q=%22A+Connection+wi
th+the+transaction+m
anager+was+lost%22+[
/url]

Allan

"bnhcomputing" < bnhcomputing@discuss
ions.microsoft.com> wrote in message
news:475036B2-A472-44C1-BABF- 63ABC88BE13A@microso
ft.com:

> I have a DTS package. The select for one of the transform data tasks look
> like:
>
> SELECT * FROM UDF1('1/1/2005','12/31/2009')
>
> right now the dates are hard coded. Is there a way to pass parameters to
> the DTS package to change the dates? Can I use DTS global variables?
>
> Any other ideas how to do this?
>
> Thanks
> --
> bnhcomputing

bnhcomputing

2005-09-29, 3:24 am

Allan:

I'm knew to DTS, so where is the parameters button? Is there an example
somewhere of what you are talking about?

Additionally, could you explain what "Connection with transaction manager
was lost" has to do with my original post? I read the articles, and couldn't
see how they applied to my original question.
--
bnhcomputing

"Allan Mitchell" wrote:

> You may be able to replace the dates in the Query with ? Placeholders.
> You can then use the parameters button to map onto them a Global
> Variable. If not then you can build the statement yourself which is the
> long way around like this
>
>
> [url]http://www.google.com/search?sourceid=navclient&ie=UTF-8& q=%22A+Connection+wi
th+the+transaction+m
anager+was+lost%22+[
/url]
>
> Allan
>
>
> "bnhcomputing" < bnhcomputing@discuss
ions.microsoft.com> wrote in message
> news:475036B2-A472-44C1-BABF- 63ABC88BE13A@microso
ft.com:
>
>
>

Allan Mitchell

2005-09-29, 7:24 am

Oops. I must have posted the answer to another Q in addition to the
answer to your Q.

The Parameters button is locted on both the ExecuteSQL task and the
TransformData task Source definition tab. It looks a lot like this.

How to Use Lookups in DTS
(http://www.sqldts.com/default.aspx?277,8)

That being said not everything supports parametere and even when they do
the designtime syntax cannot be too complicated otherwise things do not
work.
The article I meant to post you the first time round was this

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

My initial testing just now says that the less roundabout way of using ?
Placeholders will not work in this instance so you may want to use the
article instead.

Allan

"bnhcomputing" < bnhcomputing@discuss
ions.microsoft.com> wrote in message
news:BDF85517-31B0-4514-92C6- 8E5D4C79F85E@microso
ft.com:
[color=darkred]
> Allan:
>
> I'm knew to DTS, so where is the parameters button? Is there an example
> somewhere of what you are talking about?
>
> Additionally, could you explain what "Connection with transaction manager
> was lost" has to do with my original post? I read the articles, and couldn't
> see how they applied to my original question.
> --
> bnhcomputing
>
>
> "Allan Mitchell" wrote:
>

Sponsored Links