Hello,

I have created a real quick DTS package from the import wizard. It is
an ugly package not very optimized, but it works for what I need. The
problem is I had to go in and make some manual changes, I made those
changes and it took some time (over 250 tables, change needed in each
transform, and each execute sql task).. Now I need to move this to a
new server.

When I do that, SQL has hard coded t he drop and create table to
reference the old server and database name.

So I need to use VB6 and the DTS Package object to change the query, if
possible. I just don't know how to go about it, I am not a real strong
VB guy.. I can't just bring the entire package into VB because it is
far too large of a package and it kills VB and my system..

Can I edit a package in this manner, and how would I go about doing so?
I basically need to do a search and replace on words in the actual
query.

Thanks

Mike Walsh

Hello Mike,

Sure you can do this by saving the package out to VB.bas and then using a
search and replace routine to do the replacing using a text editor. In the
exported code is the ability to either execute the package there and then
or you can save the package back to a SQL Server. Another way you can do
this is to open the package in the object model.

The properties you will then need to change are

The SQLStatement property of the ExecuteSQLtask
The DestinationObjectName of the Data Pump Task.


The way you would do this is to loop through the package finding all taks
of these two types. You then grab their respective tasks and modify the
properties. You then save the package with a different name.


You can do this in the programming language of your choice.


An example piece of code had I been doing this in an Active Script task in
a package would be like this




Function Main()

dim tsk

FOR EACH tsk in DTSGlobalVariables.Parent.Tasks

IF tsk.CustomTaskID = "DTSDataPumpTask" THEN

Msgbox tsk.CustomTask.DestinationObjectName

END IF

IF tsk.CustomTaskID = "DTSExecuteSQLTask" THEN

Msgbox tsk.CustomTask.SQLStatement

END IF


NEXT

Main = DTSTaskExecResult_Success
End Function

Allan,

Thanks for your reply. I am a moron when it comes to vbScript for some
stupid reason. I like your code and follow what you are doing so I
tried to use the replace function of vbscript to perform what I need
but I can directly set the value of the sqlstatement it seems, how do I
go about doing the actual update? The following gives me an error
regarding needing to set an object:

dim tsk
dim str1
dim str2



FOR EACH tsk in DTSGlobalVariables.Parent.Tasks


IF tsk.CustomTaskID = "DTSExecuteSQLTask" THEN

str1 = tsk.customtask.sqlstatement
str2 =Replace(str1,"VzCover","mwareBellData")
set tsk.customtask.sqlstatement = str2
END IF

NEXT
.....


Allan Mitchell wrote:

Hello mike,

What about

dim tsk
dim str1
dim str2

FOR EACH tsk in DTSGlobalVariables.Parent.Tasks

IF tsk.CustomTaskID = "DTSExecuteSQLTask" THEN

str1 = tsk.customtask.sqlstatement
str2 =Replace(str1,"VzCover","mwareBellData")
tsk.customtask.sqlstatement = str2
END IF

NEXT
.....

That worked perfect. My bad regarding the Set, thank you very much!

Is there a good web reference out there to the objects in the DTS
object library that I can use in ActiveX scripts within my package? For
instance I also would like to do a similar process to increase the max
error count on all of my data Transform tasks and need to find out what
property of what object that would be, and also make some changes to
the destination..

Normally when I build a package I start off with global variables and
use a totally different architecture from the beginning to make changes
like this easier, but I have a couple older packages created through
the Data Import wizard which are functioning fine, but need some
details changed, and it would be a lot easier to change them like this
than to recreate the packages.

Hello Mike,

To tell you the truth BOL should be your friend here but it is a little cryptic
and can sometimes only be useful if you already know what it is you are looking
for. GVs are fine but in DTS you still sometimes need to get dirty with
the object model. A very useful site for DTS is this www.SQLDTS.com In
it we do a lot of playing with the features of DTS and we use the object
model extensively.

This NG is also very useful. There are good people always willing to offer
help.


Allan

Hi,

It sounds like you have a solution now, but just thought I'd give a quick
alternative.

In your DTS package you could have set up several global variables such as
database
name, server name, max transaction count etc... and then mapped these global
variables to the properties of the tasks in your package.

Then when you run that package use something like the DTSRun utility and
pass in
the values you want for the global variables. This way it doesn't matter if
you
move the package to another server or, given the above example, which
database you
use it on within the same server.

A litter bit of bitter cyncism next....
If you want to do anything remotely advanced, or something as simple as
importing
data from an Excel file and these packages could be installed on multiple
different
SQL Servers and use repeatedly not just as one offs or occasionally used
then whatever
you do DO NOT use DTS packages. They offer a world of hurt, pain and
suffering
(and as Yoda might say "lead to the dark side"). And don't get me started on
MS
service packs, SP4 and SQL Agent proxy account bug, damn it I hate DTS!

Sorry.... rant over.

Regards,
Peter
"Mike" <mwalsh9815 (AT) yahoo (DOT) com> wrote

Peter - Thank you for your interesting response. I won't get you
started on SP4 and the SQL Agent Proxy account bug. I happen to enjoy
DTS for several applications.

Yes for simple imports, I tend to use other technologies as well, but
for this particular usage, DTS was what was to be used. DTS has been
used for this in the past, and there already was a package set up to do
this. Since it was a huge package and various people had taken time to
manually change option flags within on each task (and it was saved from
the import wizard therefore there were a lot of connections and a lot
of tasks), I figured I would just reuse it. Hence, no global variables.

I absolutely agree about using GVs, if I create a package that has a
potential to be used again, then I normally work GVs and dynamic
properties into the task so I don't have to find a way to make changes
with the object model that I am not so familiar with. I think I even
mentioned that as above.

Yes, DTS does give some headaches, but in SQL Server 2000 it is a great
interface for programming various processes that rely on reading data
or moving data. So as for me and my department, we believe in DTS.

Hi,

Sounds like for your applications that use SQL Server DTS packages are
working fine.

For me unfortunately this is not the case. See the posts in this very group
by me with the subjects "DTS Package failing on clients server, tried
everything,
please help" and "DTS Package fails from one SQL Server but not on 28
other SQL Servers, help debugging?"

If you have any time to look at the above subjected posts and could offer
any insights I would be most appreciated as it sounds like you have a lot
of experience with DTS.

The only reason I am currently still using DTS is because the time/cost
for implementing my own replacement for my applications use is not
feasible at the present time, but the problems my clients are experience
are mounting up so hopefully the opportunity will be given by management
to replace it.

Regards,
Peter
"Mike" <mwalsh9815 (AT) yahoo (DOT) com> wrote