can i create dynamic execute SQL task ?

XJ,

The idea is possible. Your code will be implemented as dynamic-SQL when you
do this, with the attendant issues of rights, code safety, and so forth.
So, you should review the standard text by Erland Sommarskog:
http://www.sommarskog.se/dynamic_sql.html

Since your code will actually be stored in a table, then you will probably
wind up doing something like this:

Declare @CodeToRun NVARCHAR(1000)

SELECT @CodeToRun = CodeColumn
FROM MyCodeTable
WHERE Key = 1

EXEC sp_executesql @CodeToRun

RLF



"XJ" wrote in message

> Hi All,
> i have a existing DTS package, however the execute SQL task
> keep changing, instead, i will create one table to store those execute
> SP name and using query to run for it. do you think the idea is
> possible?
>
 >> Stay informed about: can i create dynamic execute SQL task ? 

On Dec 28, 10:05 pm, wrote:
> On Dec 28, 4:03 am, XJ wrote:
>
> > Hi All,
> >          i have a existingDTSpackage, however the execute SQLtask
> > keep changing, instead, i willcreateone table to store those execute
> > SP name and using query to run for it. do you think the idea is
> > possible?
>
> Yes, use adynamicpropertiestaskto set the SQLStatement property of
> the Execute SQLTask. Thedynamicpropertiestaskcan be configured to
> use a query by choosing Query in the Source drop down list.
>
> Nice step by step how to herehttp://www.sqlservercentral.com/articles/DTS/dynamicproperties/1912/
>
> NormanDTSPackage Searchhttp://www.dtspackagesearch.com/

Hi, thanks for ur reply, do u think is possible to create some dynamic
"execute SQL task" from T-SQL? base on different kind of the scenarios
we may also add some task quantity dynamically.
 >> Stay informed about: can i create dynamic execute SQL task ? 

On Dec 28, 10:18 pm, "Russell Fields"
wrote:
> XJ,
>
> The idea is possible.  Your code will be implemented asdynamic-SQL when you
> do this, with the attendant issues of rights, code safety, and so forth.
> So, you should review the standard text by Erland Sommarskog:http://www.sommarskog.se/dynamic_sql.html
>
> Since your code will actually be stored in a table, then you will probably
> wind up doing something like this:
>
> Declare @CodeToRun NVARCHAR(1000)
>
> SELECT @CodeToRun = CodeColumn
>   FROM MyCodeTable
>   WHERE Key = 1
>
> EXEC sp_executesql @CodeToRun
>
> RLF
>
> "XJ" wrote in message
>
>
>
>
>
> > Hi All,
> >         i have a existingDTSpackage, however the execute SQLtask
> > keep changing, instead, i willcreateone table to store those execute
> > SP name and using query to run for it. do you think the idea is
> > possible?- Hide quoted text -
>
> - Show quoted text -

yes, this is a good artitcle, however i still got a same question, is
possible to execute query at parallel time?
 >> Stay informed about: can i create dynamic execute SQL task ? 

> yes, this is a good artitcle, however i still got a same question, is
> possible to execute query at parallel time?

XJ, Sorry, but I don't understand your latest question. You did not
mention parallel time in your original post.

1) Yes, you can generate and execute dynamic SQL from a combination of table
values and other logic.

2) TSQL executes synchronously with the calling source.. Any parallel
threads from DTS come from the design of the package, not of the dynamic SQL
query. http://technet.microsoft.com/en-us/library/aa933475(sql.80).aspx

RLF
 >> Stay informed about: can i create dynamic execute SQL task ?