Hi,
I have a dts package that has 23 ExecuteSqlTasks.
I know how to loop through each task. What I want to do
is change the Sql Statement. The problem is the sql script I have is about
800 lines long. How do I assign the script.sql file into a global variable
and then assign it to "oTask.customTask.SqlStatement = ".

Here is the code.

Dim oPackage
Dim oTask

set oPackage = DTSGlobalVariables.Parent

For Each oTask in oPackage.tasks
If oTask.CustomTaskID = "DTSExecuteSQLTask" then
msgbox oTask.Name
msgbox oTask.Description
oTask.customTask.SqlStatement =




End IF
Next

Main = DTSTaskExecResult_Success

Thanks


Ron Sissons, DBA
Information Technology Services
Riverside County Office of Education
3939 Thirteenth Street, Riverside, CA 92502-0868
Telephone: (909) 826-6471; FAX: [909] 826-6451

Why not use an ExecuteProcess task and call osql instead and you can pass
the name of the file(s)

How to manipulate the Execute Process task.
(http://www.sqldts.com/default.aspx?251)

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Ron Sissons" <RSISSONS (AT) rcoe (DOT) k12.ca.us> wrote

Since I have the script execute on 23 different databases, I don't want to
create a SP. I want to be able to change the sql statement of the executesql
task by using a script.sql file. That way if I change the script, i can
update all the executesql tasks in my package.

SO my qwuestion is: how do I assing the file to a global variable and then
assign it to "oTask.customTask.SqlStatement = gv_sqlscript".



Ron Sissons, DBA
Information Technology Services
Riverside County Office of Education
3939 Thirteenth Street, Riverside, CA 92502-0868
Telephone: (909) 826-6471; FAX: [909] 826-6451


and have the text file assigned to the sql execute task. I am assuming using
you are using a lot of dynamic sql. Or else, you could probably just put the
800 line sql in a stored proc and call it from the execute sql task, no?hope
this helps,K ----- Ron Sissons wrote: ----- Hi, I
have a dts package that has 23 ExecuteSqlTasks. I know how to loop
through each task. What I want to do is change the Sql Statement. The
problem is the sql script I have is about 800 lines long. How do I
assign the script.sql file into a global variable and then assign it to
"oTask.customTask.SqlStatement = ". Here is the code. Dim
oPackage Dim oTask set oPackage = DTSGlobalVariables.Parent
For Each oTask in oPackage.tasks If oTask.CustomTaskID =
"DTSExecuteSQLTask" then msgbox oTask.Name msgbox
oTask.Description oTask.customTask.SqlStatement =
End IF Next Main = DTSTaskExecResult_Success
Thanks Ron Sissons, DBA Information Technology Services
Riverside County Office of Education 3939 Thirteenth Street, Riverside,
CA 92502-0868 Telephone: (909) 826-6471; FAX: [909] 826-6451

In message <#$fOX7ILEHA.332 (AT) TK2MSFTNGP11 (DOT) phx.gbl>, Ron Sissons
<RSISSONS (AT) rcoe (DOT) k12.ca.us> writes
the Dynamic Properties Task.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org