DTS QUERY AND HELP

I’ve not had much experience with DTS packages before.I am using SQL Server 2000 and I have a table I wish to update.I have a spreadsheet with the updated records – this does not include all records and does not include all columns within the table,

I just wanted to know if it is still possible to update certain fields without having to include all the records and all the columns e.g.

Table 1

IDNAMESURNAME

1TEDSMITH

2FREDPERRY

3JOEYOUNG

Spreadsheet

IDSURNAME

2CASH

3WILLIS

Result

IDNAMESURNAME

1TEDSMITH

2FREDCASH

3JOEYOUNG

And the million-dollar question – please could you provide the steps to do this?

Take a look at the Data Driven Query Component|||

Can anyone provide any more information on this for me?

Thanks

|||If you need th enext level of help thn its probably best to move this thread to the SSIS forum|||

Look for "Building a Data Driven Query" in SQL Server 2000 Book Online, also found here: http://msdn2.microsoft.com/en-us/library/aa933507(sql.80).aspx

You'll want to follow the first example: "In the first example, every source row leads to the update of a destination row. Only one query type, an Update query, is used."

sqlsql

Dts Problem....help....optimization

Hello everyone..

I would like to request your precious help!!

Im having a little problem of performance in the package DTS of SQL SERVER 2000!

Ive made the mapping between the data with DDQ and ive made the inserts and the updates with Stored Procedures.. but the performance is very weak and is too slow!!!

How can i optimize this?

Thank you all!Hello LULU,
Have you tried to implement query parallelism?..it's a good method to improve the performance!..

Tom B.

SAS International Headquarters
Tom Berranger, Manager
SAS Institute GmbH
P.O. Box 105340
Neuenheimer Landstr. 28-30
D-69043 Heidelberg
GERMANY

ps: I see that you work with SQL Server?...i work for a multinational that works with this tool and we are looking for junior consultant in this area.
So if you are interested..please let me know...you have just to send a mail for this forum.
thanks.

Originally posted by LULU
Hello everyone..

I would like to request your precious help!!

Im having a little problem of performance in the package DTS of SQL SERVER 2000!

Ive made the mapping between the data with DDQ and ive made the inserts and the updates with Stored Procedures.. but the performance is very weak and is too slow!!!

How can i optimize this?

Thank you all!

DTS package runs slow when run from SQLAgent

So I've seen some posts about slow DTS performance. Many of them seem
to be related to the package execution times increasing over time.
That's not my situation.
I have this one DTS package that I had been running from SERVERA. It
normally runs in under 5 minutes. I had the package scheduled in a SQL
Server Agent 2000 job. The package was responsible for truncating a
table in a database on SERVERB and then copying data from a table in
an Oracle daabase on SERVERC.
I'm working on consolidating some SQL Server instances, so I figured
I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
and recreate the job on SERVERA as well. So the DTS package runs
perfectly fine when I'm terninal serviced into the server and
executing the package from either the DTS designer or from the command
line. However, the package seems to hang when running the job from a
SQL Server Agent job.
I'm using the same user account when executing the job from terminal
session and job. The only difference I've seen so far is that SERVERB
is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
SERVERA is setup to run the services as sqlservices@.domain.com. I
can't imagine that would be the problem, though.
Do you have any suggestions as to what the problem could be. I can't
put the job on SERVERA into production until I figure why it doesn't
run like it does on SERVERB.
Thanks in advance,
Osolage
Hi
"Osolage" wrote:

> So I've seen some posts about slow DTS performance. Many of them seem
> to be related to the package execution times increasing over time.
> That's not my situation.
> I have this one DTS package that I had been running from SERVERA. It
> normally runs in under 5 minutes. I had the package scheduled in a SQL
> Server Agent 2000 job. The package was responsible for truncating a
> table in a database on SERVERB and then copying data from a table in
> an Oracle daabase on SERVERC.
> I'm working on consolidating some SQL Server instances, so I figured
> I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
> and recreate the job on SERVERA as well. So the DTS package runs
> perfectly fine when I'm terninal serviced into the server and
> executing the package from either the DTS designer or from the command
> line. However, the package seems to hang when running the job from a
> SQL Server Agent job.
> I'm using the same user account when executing the job from terminal
> session and job. The only difference I've seen so far is that SERVERB
> is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
> SERVERA is setup to run the services as sqlservices@.domain.com. I
> can't imagine that would be the problem, though.
> Do you have any suggestions as to what the problem could be. I can't
> put the job on SERVERA into production until I figure why it doesn't
> run like it does on SERVERB.
> Thanks in advance,
> Osolage
It is not clear if you have specified SERVERB as part of the DTSRun command
or whether you have modified the job to run with distributed transactions. If
you have done the former then I would expect similar times and any
degredation would be network related.
John

DTS Package problem...

Hi all
SQL 2k (dev.ed.)
I've got a problem with a DTS package. I have it running at 9am every
morning to copy DB tables/stored procs etc from a server other side of
the world to my local server. This has been working well for a couple
months now...
I had to reinstall windows and the local DB server and now I can't get
the DTS package working again.
I have it set up to copy from src to dest as normal - it seems to copy
all the tables, index, stored procs, functions, triggers... the only
thing that it is NOT coping is the actual DATA in the TABLES.
I have checked & double checked and the check box IS marked to copy
data, I've tried both sub options of replace & Append data, but no
luck...
Anyone got ANY ideas on why this is happening?
Thanks
Al
Harag
First, you can SQL Server Profiler to see what is going on during the
package's execution.
Second, would it be much better to backup/restore the database on
destination server rather to copy tables/store procedures?
"Harag" <haragREMOVECAPITALS@.softhome.net> wrote in message
news:ohftk0toad0rska9eaktlgjhflsq5bi84d@.4ax.com...
> Hi all
> SQL 2k (dev.ed.)
> I've got a problem with a DTS package. I have it running at 9am every
> morning to copy DB tables/stored procs etc from a server other side of
> the world to my local server. This has been working well for a couple
> months now...
> I had to reinstall windows and the local DB server and now I can't get
> the DTS package working again.
> I have it set up to copy from src to dest as normal - it seems to copy
> all the tables, index, stored procs, functions, triggers... the only
> thing that it is NOT coping is the actual DATA in the TABLES.
> I have checked & double checked and the check box IS marked to copy
> data, I've tried both sub options of replace & Append data, but no
> luck...
> Anyone got ANY ideas on why this is happening?
> Thanks
> Al
|||On Mon, 20 Sep 2004 14:44:07 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:

>Harag
>First, you can SQL Server Profiler to see what is going on during the
>package's execution.
Hmm, How do I do that with the profiler? I'm kinda new to that and not
really sure on what to do with it.

>Second, would it be much better to backup/restore the database on
>destination server rather to copy tables/store procedures?
the DB on the other side of the world is a 3rd party host (they host
the website) so they have they own backups etc. But I like to take a
daily copy of the tables and a weekly copy of the stored procs for my
own backups... I can also use "live" data for further testing of new
features to the site rather than made up data.
Thanks again.
Al.

>
>"Harag" <haragREMOVECAPITALS@.softhome.net> wrote in message
>news:ohftk0toad0rska9eaktlgjhflsq5bi84d@.4ax.com.. .
>
|||On Mon, 20 Sep 2004 14:14:08 +0100, Harag
<haragREMOVECAPITALS@.softhome.net> wrote:

>On Mon, 20 Sep 2004 14:44:07 +0200, "Uri Dimant" <urid@.iscar.co.il>
>wrote:
>
>Hmm, How do I do that with the profiler? I'm kinda new to that and not
>really sure on what to do with it.
>
ok had a quick play with Profiler - setting for standard then ran the
DTS package... all seems ok to me in the profiler except for one area
where I think the problem is:
it has the following lines:
use master exec sp_dboption N'OnlineBackup', N'select into/bulkcopy',
N'true' use [OnlineBackup] checkpoint
In between these 2 lines there is 7 rows that all say "use
OnlineBackup"... thats it, nothing else. (btw there is currently 7
tables)
use master exec sp_dboption N'OnlineBackup', N'select into/bulkcopy',
N'false' use [OnlineBackup] checkpoint
[vbcol=seagreen]
>the DB on the other side of the world is a 3rd party host (they host
>the website) so they have they own backups etc. But I like to take a
>daily copy of the tables and a weekly copy of the stored procs for my
>own backups... I can also use "live" data for further testing of new
>features to the site rather than made up data.
>Thanks again.
>Al.

DTS Package

I know you can save a DTS file off as a vb file. I've used that quite often.
But is there any way to run a DTS package from T-SQL code?
Btw, I've been playing around with dtsrun for the last half hour and can't
get it to work. I keep getting the following error msg whether I run it
under the domain service account, my own account or the sa password:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
"CLM" wrote:

> I know you can save a DTS file off as a vb file. I've used that quite often.
> But is there any way to run a DTS package from T-SQL code?
|||Post your code and maybe we'll see the problem. I use xp_cmdshell with dtsrun
and it works.
TIA,
ChrisR
"CLM" wrote:
[vbcol=seagreen]
> Btw, I've been playing around with dtsrun for the last half hour and can't
> get it to work. I keep getting the following error msg whether I run it
> under the domain service account, my own account or the sa password:
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
>
> "CLM" wrote:
|||Thx. I just got it to work!
"ChrisR" wrote:
[vbcol=seagreen]
> Post your code and maybe we'll see the problem. I use xp_cmdshell with dtsrun
> and it works.
> --
> TIA,
> ChrisR
>
> "CLM" wrote:

DTS job is not copying all the data but still the Job History shows Successful?

Hello everybody.
I've got 4 servers running Windows 2003 Standard edition with SQL 2000 SP3a.
On each SQL server there is a database that is copied to all the other
servers every night using a DTS scheduled job.
The database sizes are ranging from 50 Mb to 520 Mb.
Now the problem is that sometimes the job fails but it still reports
Successful in the Job History. The typical problem is that one ore more of
the tables on one of the destination server are empty. The table is there
but none of the records are there. The rest of the tables and their records
are fine.
Can anybody give me some hints as to how I can get a more detailed logging
of the process so we can investigate what is going wrong?
Martin Moustgaard
Job Shows Successfull of the Job ;
not the each data copy process..
Check ur DTS pack. is it well build. i mean the work flow of the package.. ?
What happens when a step in DTS raise Error , u handle this ?
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Hello everybody.
> I've got 4 servers running Windows 2003 Standard edition with SQL 2000
SP3a.
> On each SQL server there is a database that is copied to all the other
> servers every night using a DTS scheduled job.
> The database sizes are ranging from 50 Mb to 520 Mb.
> Now the problem is that sometimes the job fails but it still reports
> Successful in the Job History. The typical problem is that one ore more of
> the tables on one of the destination server are empty. The table is there
> but none of the records are there. The rest of the tables and their
records
> are fine.
> Can anybody give me some hints as to how I can get a more detailed logging
> of the process so we can investigate what is going wrong?
> Martin Moustgaard
>
|||Martin
I'd run DTS from Data Transformation Services.
See what is going on , what task is failed.
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Hello everybody.
> I've got 4 servers running Windows 2003 Standard edition with SQL 2000
SP3a.
> On each SQL server there is a database that is copied to all the other
> servers every night using a DTS scheduled job.
> The database sizes are ranging from 50 Mb to 520 Mb.
> Now the problem is that sometimes the job fails but it still reports
> Successful in the Job History. The typical problem is that one ore more of
> the tables on one of the destination server are empty. The table is there
> but none of the records are there. The rest of the tables and their
records
> are fine.
> Can anybody give me some hints as to how I can get a more detailed logging
> of the process so we can investigate what is going wrong?
> Martin Moustgaard
>
|||The DTS is actually very simple.
I only have a Copy SQL Server Objects Task in the package that is set up to
create destination objects (drop destination object first, include all
dependent objects, include extended properties), copy data (replace existing
data), use collation, copy all objects. Under options I've selected
everything except the 3 selections under secirity options.
The only error handeling I have is that the DTS jopb is set to retry 2 time
with 30 minutes interval if it fails.
Martin Moustgaard
"Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
> Job Shows Successfull of the Job ;
> not the each data copy process..
> Check ur DTS pack. is it well build. i mean the work flow of the package..
?[vbcol=seagreen]
> What happens when a step in DTS raise Error , u handle this ?
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> SP3a.
of[vbcol=seagreen]
there[vbcol=seagreen]
> records
logging
>
|||That's the problem :-)
Every time I run it manually it works fine. And 90% of the time it also runs
fine at night at the scheduled job. So I haven't been able to reproduce it
by running the jobs manually.
Martin Moustgaard
"Uri Dimant" <urid@.iscar.co.il> skrev i en meddelelse
news:uSMRBykwEHA.1984@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Martin
> I'd run DTS from Data Transformation Services.
> See what is going on , what task is failed.
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> SP3a.
of[vbcol=seagreen]
there[vbcol=seagreen]
> records
logging
>
|||May be a Table Lock while dropping this tables?
Or.. Check the Owner Of the Tables...
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:eAL0hYlwEHA.3620@.TK2MSFTNGP09.phx.gbl...
> The DTS is actually very simple.
> I only have a Copy SQL Server Objects Task in the package that is set up
to
> create destination objects (drop destination object first, include all
> dependent objects, include extended properties), copy data (replace
existing
> data), use collation, copy all objects. Under options I've selected
> everything except the 3 selections under secirity options.
> The only error handeling I have is that the DTS jopb is set to retry 2
time[vbcol=seagreen]
> with 30 minutes interval if it fails.
> Martin Moustgaard
>
> "Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
> news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
package..[vbcol=seagreen]
> ?
more
> of
> there
> logging
>
|||A table lock is also what I'm thinking. But since the jobs are run at 2
o'clock in the morning there shouldn't be anybody working at that hour :-)
But the only way to find out if it is a table lock is to increase the
logging level on the DTS job and that is my problem since I don't know where
to do this.
Owner of the table shouldn't be a problem since it works fine most of the
time.
Martin Moustgaard
"Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
news:%23XB1zylwEHA.3376@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> May be a Table Lock while dropping this tables?
> Or.. Check the Owner Of the Tables...
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:eAL0hYlwEHA.3620@.TK2MSFTNGP09.phx.gbl...
> to
> existing
> time
> package..
2000[vbcol=seagreen]
other
> more
>

DTS import

ive successfully imported the data i need into a table, but now i need to
append to that data another field from the original source into data thats
already there
eg original source was poor - had a diary function that could be either
ID,Account Manager,Diary or ID,Senior Account Manager,Diary
in sql i have ID,EnteredBy,Diary
i imported the data from Access no problem but i could only import the
account manager field - now i need to append in senior account manager to
the data thats already there
heh not sure if im making myself clear
mark
Could you supply a DDL of the source table and the SQL Server table and a
sample of the data and what you want it to look like?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:dwHqc.24$pz2.23@.newsfe3-win.server.ntli.net...
> ive successfully imported the data i need into a table, but now i need to
> append to that data another field from the original source into data thats
> already there
> eg original source was poor - had a diary function that could be either
> ID,Account Manager,Diary or ID,Senior Account Manager,Diary
> in sql i have ID,EnteredBy,Diary
> i imported the data from Access no problem but i could only import the
> account manager field - now i need to append in senior account manager to
> the data thats already there
> heh not sure if im making myself clear
> mark
>
|||"mark" <mark@.remove.com> wrote in message news:<dwHqc.24$pz2.23@.newsfe3-win.server.ntli.net>...
> ive successfully imported the data i need into a table, but now i need to
> append to that data another field from the original source into data thats
> already there
> eg original source was poor - had a diary function that could be either
> ID,Account Manager,Diary or ID,Senior Account Manager,Diary
> in sql i have ID,EnteredBy,Diary
> i imported the data from Access no problem but i could only import the
> account manager field - now i need to append in senior account manager to
> the data thats already there
> heh not sure if im making myself clear
> mark
Well, I don't understand the detail of what you're trying to do.
There are three options spring to mind though.
All start with you adding your new field to the table.
1) With DTS you can do all sorts of things in script.
There's probably a way of updating the data directly via a DTS
package, but I reckon if you have to ask then this ain't the route you
want mate.
2) Whack the data into a new table. Put a bit of T-SQL together to
update
with this and then drop the new table.
3) Connect access to the SQL database using a dsn and attached tables
and write the update in access. This may be slow depending on your
connection and amount of data but I suggest it because maybe you're
more used to working in Access.
HTH

DTS import

ive successfully imported the data i need into a table, but now i need to
append to that data another field from the original source into data thats
already there
eg original source was poor - had a diary function that could be either
ID,Account Manager,Diary or ID,Senior Account Manager,Diary
in sql i have ID,EnteredBy,Diary
i imported the data from Access no problem but i could only import the
account manager field - now i need to append in senior account manager to
the data thats already there
heh not sure if im making myself clear
markCould you supply a DDL of the source table and the SQL Server table and a
sample of the data and what you want it to look like?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:dwHqc.24$pz2.23@.newsfe3-win.server.ntli.net...
> ive successfully imported the data i need into a table, but now i need to
> append to that data another field from the original source into data thats
> already there
> eg original source was poor - had a diary function that could be either
> ID,Account Manager,Diary or ID,Senior Account Manager,Diary
> in sql i have ID,EnteredBy,Diary
> i imported the data from Access no problem but i could only import the
> account manager field - now i need to append in senior account manager to
> the data thats already there
> heh not sure if im making myself clear
> mark
>|||"mark" <mark@.remove.com> wrote in message news:<dwHqc.24$pz2.23@.newsfe3-win.server.ntli.net>
..
> ive successfully imported the data i need into a table, but now i need to
> append to that data another field from the original source into data thats
> already there
> eg original source was poor - had a diary function that could be either
> ID,Account Manager,Diary or ID,Senior Account Manager,Diary
> in sql i have ID,EnteredBy,Diary
> i imported the data from Access no problem but i could only import the
> account manager field - now i need to append in senior account manager to
> the data thats already there
> heh not sure if im making myself clear
> mark
Well, I don't understand the detail of what you're trying to do.
There are three options spring to mind though.
All start with you adding your new field to the table.
1) With DTS you can do all sorts of things in script.
There's probably a way of updating the data directly via a DTS
package, but I reckon if you have to ask then this ain't the route you
want mate.
2) Whack the data into a new table. Put a bit of T-SQL together to
update
with this and then drop the new table.
3) Connect access to the SQL database using a dsn and attached tables
and write the update in access. This may be slow depending on your
connection and amount of data but I suggest it because maybe you're
more used to working in Access.
HTH

DTS import

ive successfully imported the data i need into a table, but now i need to
append to that data another field from the original source into data thats
already there
eg original source was poor - had a diary function that could be either
ID,Account Manager,Diary or ID,Senior Account Manager,Diary
in sql i have ID,EnteredBy,Diary
i imported the data from Access no problem but i could only import the
account manager field - now i need to append in senior account manager to
the data thats already there
heh not sure if im making myself clear
markCould you supply a DDL of the source table and the SQL Server table and a
sample of the data and what you want it to look like?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:dwHqc.24$pz2.23@.newsfe3-win.server.ntli.net...
> ive successfully imported the data i need into a table, but now i need to
> append to that data another field from the original source into data thats
> already there
> eg original source was poor - had a diary function that could be either
> ID,Account Manager,Diary or ID,Senior Account Manager,Diary
> in sql i have ID,EnteredBy,Diary
> i imported the data from Access no problem but i could only import the
> account manager field - now i need to append in senior account manager to
> the data thats already there
> heh not sure if im making myself clear
> mark
>|||"mark" <mark@.remove.com> wrote in message news:<dwHqc.24$pz2.23@.newsfe3-win.server.ntli.net>...
> ive successfully imported the data i need into a table, but now i need to
> append to that data another field from the original source into data thats
> already there
> eg original source was poor - had a diary function that could be either
> ID,Account Manager,Diary or ID,Senior Account Manager,Diary
> in sql i have ID,EnteredBy,Diary
> i imported the data from Access no problem but i could only import the
> account manager field - now i need to append in senior account manager to
> the data thats already there
> heh not sure if im making myself clear
> mark
Well, I don't understand the detail of what you're trying to do.
There are three options spring to mind though.
All start with you adding your new field to the table.
1) With DTS you can do all sorts of things in script.
There's probably a way of updating the data directly via a DTS
package, but I reckon if you have to ask then this ain't the route you
want mate.
2) Whack the data into a new table. Put a bit of T-SQL together to
update
with this and then drop the new table.
3) Connect access to the SQL database using a dsn and attached tables
and write the update in access. This may be slow depending on your
connection and amount of data but I suggest it because maybe you're
more used to working in Access.
HTH