DTS Fails

Good Morning,

I've been running into a small problem. There are two people that usually create local packages to be executed by the DTS. Either my boss or I. The problem that I have been running into is, there are some local packages that will not execute when I try to run them. These packages are that my boss has created. When she runs them they work fine. But when I run them they just don't run. The only way I can run the local package is if I log in into the server and run it from there.

The error that I keep getting is:

Unspecified Error
[Microsoft][ODBC Driver Manager] Data Source Name not found and no default driver specified.

What would I be able to do so that I can execute this local package from my computer?Could try changing the owner of the DTS package.

Have a look at the following link and tell me if it helps your situation.

http://www.sqldts.com/?212|||What is the package doing exactly. Is it accessing an odbc datasource and if so do you have this set up locally?|||It is accessing a datasource, and it has been set up on my machine. We use the same data source to import information. I am able to run other DTS that have been created by her, but I am not successful at executing all of them.

I did go to the link listed by aldo_2003 and there was some helpful information. So if I am not apart of the sysadmin group, then that can be a reason for me not being able to execute the local package? Cause like i said, when I log on the our server, I am able to execute the package just fine. Just not from my machine.|||You need to be either ower or part of sysadmin to execute the package to run.|||Otherwise what you can do is open the query analyzer, connect to the remote server and EXECUTE master.dbo.xp_cmdshell 'dtsrun /S (local) /U {uid} /P {password} /N {name dts package}'

With this you can see whether the package executes well or not.

If you wish to schedule this package on the remote server you need to modify the DTSrun on each step from the schedule as well.
Modify the dts to read the same: dtsrun /S (local) /U etc... via the Tab Steps and the button Edit|||Thank you very much for all your help. I was listed as a sysadmin, but there were a few drivers that I had to add to my user dsn. That was the whole reason why I was getting the error. Everything works fine now.|||This is how I did this.

control panel -> administrative tools -> Data Sources -> User DSN -> Add -> Select Driver -> Finish

Then you will be prompted to type in the Data Source, Host, and Port.

Then you should be able to run the local package.

DTS Export

Is it possible to export DTS packages to another server? If so, how?
Thanks much in advance,
KellyOpen the package in the designer and select Package / Save As... You can
save to any SQL Server or to a .DTS file.
David Portas
SQL Server MVP
--|||It will not be easy.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/defaul...;en-us;314546#6
AMB
"scorpion53061" wrote:

> Is it possible to export DTS packages to another server? If so, how?
> Thanks much in advance,
> Kelly
>
>

DTS drawbacks

I am trying to compile a case of why DTS packages need to be migrated to SSIS packages. Is there any document, whitepaper which can provide me drawbacks in DTS?

Thanks,

Vijay.

This will be completely situation dependent, and my reasons are different from yours... For us, it was simply the upgrade to SQL Server 2005. Some things continued to work, others didn't, so we converted packages.

But have you looked at the feature list of SSIS and compared it to DTS to build your own conclusions?

Phil|||I am aware of new features in SSIS like control-flow, data-flow demarcation, new transforms like SCD etc. But knowing some drawbacks in DTS like performance, scalability, support for XML can help. If there is any inputs on specific drawbacks in DTS as compared to SSIS, it will be helpful.|||

Vijay Thirugnanam wrote:

I am aware of new features in SSIS like control-flow, data-flow demarcation, new transforms like SCD etc. But knowing some drawbacks in DTS like performance, scalability, support for XML can help. If there is any inputs on specific drawbacks in DTS as compared to SSIS, it will be helpful.

SSIS performs and scales better than DTS by an order of magnitude.

As opposed to SSIS, DTS very much relies on the staging of data in order to transform it into something suitable for populating a data warehouse. SSIS very much relies on in-memory buffers to transform data, obviating the much of need to stage such data.

Also, DTS typically requires a significantly greater amount of custom coding to accomplish that which can be accomplished with SSIS.

|||

Try performing a loop in DTS (where you loop among various tasks not just inside and ActiveX script task) then try it in SSIS.

Also take a look at all of the work required to make a DTS package portable (able to move from test to prod without making changes to it) and compare to SSIS.

On this last see http://www.mutuallybeneficial.com/index_files/dts_ssis_packages_portable.htm for more info.

DTS document or ebook

Hi,

I need deep knowledge about DTS tasks. Can anyone send me an e-book or
related document about DTS packages. (Links on the Internet site are
generally advertisement or not working links).

Thanks,

Veysel Can DemirSQL Server Books Online is a good place to start. Then check out the
following sites:

www.sqldts.com
www.sqldev.net
http://vyaskn.tripod.com/sql_server...t_practices.htm

Here are some SQL Server DTS books:
http://vyaskn.tripod.com/sqlbooks.htm#dts
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/

"Veysel" <veysel_can_demir@.yahoo.co.uk> wrote in message
news:7d1e7961.0408110845.3d69a25d@.posting.google.c om...
> Hi,
> I need deep knowledge about DTS tasks. Can anyone send me an e-book or
> related document about DTS packages. (Links on the Internet site are
> generally advertisement or not working links).
> Thanks,
> Veysel Can Demir|||
Hi,

First of all thanks for answer. But there is a little problem, I think
that you did not understand me. I do not want book advertisements. I
need free e-books or pdf documents that I can teach me DTS.

Thanks,

Veysel Can

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||www.sqldts.com is a good place for info

DTS disaster recovery

Can anyone recommend any best practices for restoring local packages? In a
disaster recovery situation I will have 1 SQL Server box rather than 4 so I
won't be able to restore all of my local packages by restoring MSDB. Any
thoughts?
In the same situation, I would restore the MSDB databases from each server
as MSDB1, MSDB2, etc, then move the data into the main MSDB database...
Also, save out each DTS package as a Structured Storage file and dump that
into a version control application such as Source Safe.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Terri" <terri@.cybernets.com> wrote in message
news:dml9p8$vqu$1@.reader2.nmix.net...
> Can anyone recommend any best practices for restoring local packages? In a
> disaster recovery situation I will have 1 SQL Server box rather than 4 so
> I
> won't be able to restore all of my local packages by restoring MSDB. Any
> thoughts?
>
|||"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:evifdFg9FHA.2792@.TK2MSFTNGP11.phx.gbl...
> In the same situation, I would restore the MSDB databases from each server
> as MSDB1, MSDB2, etc, then move the data into the main MSDB database...
Would anyone have any code that would select and insert this data.

> Also, save out each DTS package as a Structured Storage file and dump that
> into a version control application such as Source Safe.
Does this need to be done with the GUI?
More generally would named instances help here?

DTS disaster recovery

Can anyone recommend any best practices for restoring local packages? In a
disaster recovery situation I will have 1 SQL Server box rather than 4 so I
won't be able to restore all of my local packages by restoring MSDB. Any
thoughts?In the same situation, I would restore the MSDB databases from each server
as MSDB1, MSDB2, etc, then move the data into the main MSDB database...
Also, save out each DTS package as a Structured Storage file and dump that
into a version control application such as Source Safe.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Terri" <terri@.cybernets.com> wrote in message
news:dml9p8$vqu$1@.reader2.nmix.net...
> Can anyone recommend any best practices for restoring local packages? In a
> disaster recovery situation I will have 1 SQL Server box rather than 4 so
> I
> won't be able to restore all of my local packages by restoring MSDB. Any
> thoughts?
>|||"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:evifdFg9FHA.2792@.TK2MSFTNGP11.phx.gbl...
> In the same situation, I would restore the MSDB databases from each server
> as MSDB1, MSDB2, etc, then move the data into the main MSDB database...
Would anyone have any code that would select and insert this data.

> Also, save out each DTS package as a Structured Storage file and dump that
> into a version control application such as Source Safe.
Does this need to be done with the GUI?
More generally would named instances help here?

DTS disaster recovery

Can anyone recommend any best practices for restoring local packages? In a
disaster recovery situation I will have 1 SQL Server box rather than 4 so I
won't be able to restore all of my local packages by restoring MSDB. Any
thoughts?In the same situation, I would restore the MSDB databases from each server
as MSDB1, MSDB2, etc, then move the data into the main MSDB database...
Also, save out each DTS package as a Structured Storage file and dump that
into a version control application such as Source Safe.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Terri" <terri@.cybernets.com> wrote in message
news:dml9p8$vqu$1@.reader2.nmix.net...
> Can anyone recommend any best practices for restoring local packages? In a
> disaster recovery situation I will have 1 SQL Server box rather than 4 so
> I
> won't be able to restore all of my local packages by restoring MSDB. Any
> thoughts?
>|||"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:evifdFg9FHA.2792@.TK2MSFTNGP11.phx.gbl...
> In the same situation, I would restore the MSDB databases from each server
> as MSDB1, MSDB2, etc, then move the data into the main MSDB database...
Would anyone have any code that would select and insert this data.
> Also, save out each DTS package as a Structured Storage file and dump that
> into a version control application such as Source Safe.
Does this need to be done with the GUI?
More generally would named instances help here?

DTS deployment issues

Hello!

I'm trying to deploy an application that runs DTS packages from the local machine.

I've read some articles that it would be enough to install the SQL Client Tools on the local machine to make the DTS work . I did this, the package executes a few steps (it shows message boxes and writes data into the database), then (before executing some validation tasks on the data) it throws an exception 'Execution was canceled by user'. I've set the pbCancel flag and the package runs fine on some computers, but on an empty system that only contains my application and the Sql Client Tools I get this exception.

Do I have to install anything else or do I have to make some changes to DTS package to make it work?

Thank you in advance!

this is the wrong forum; Unless your are using SSIS (SQL SERVER 2005).|||sorry, my mistake

DTS completion time jump

Hi,
I have a DTS package that runs 4 other packages on a nightly basis. This
package usually takes about 2 hours to run, but ocasionally it jumps to
around 5 hours to complete. There have not been any changes to the
parameters or coding. It is the same process that causes this each time
which is a cursor run strored procedure. There have not been any huge jumps
in the number of records in the table or changes to the source data that
would cause the cursor to take longer to run through. This seems to be
intermittent, I can not easily check to see if something else in the network
on that server is happening since it is a customer site. Would you think
that something outside of the package is affecting this since it only happen
s
once in a while (about once a w or so), like I mentioned - backups, serve
r
processes, other jobs etc.? I just don't know where to look if I have not
changed anything within my package.
Thanks,
PatriceThe are many reasons why performance can change over time. One is job
contention, where multiple jobs are running at the same time. The following
will dump a listing of jobs, when they started and ended out of the
msdb.dbo.sysjobhistory system table, and store it in a temporary table:
select t2.[name], t2.job_start, DATEADD(ss,DATEDIFF(ss,'1980-01-01',
'1980-01-01 ' + t2.job_duration),t2.job_start) AS job_end
INTO #jobsched
FROM (
select t1.[name],
CAST(
start_date + ' ' + LEFT(t1.start_time,2) + ':' +
SUBSTRING(t1.start_time,3,2) + ':' + RIGHT(t1.start_time,2) AS datetime
) AS "job_start",
LEFT(t1.job_duration,2) + ':' + SUBSTRING(t1.job_duration,3,2) + ':' +
RIGHT(t1.job_duration,2) AS job_duration
FROM
(
select
j.[name],
LEFT(CAST(h.run_date AS char(8)), 4) + '-' + SUBSTRING(CAST(h.run_date AS
char(8)),5,2) + '-' + RIGHT(CAST(h.run_date AS char(8)), 2) AS "start_date",
REPLICATE('0', 6 - LEN(h.run_time)) + CAST(h.run_time AS varchar(6)) AS
"start_time",
REPLICATE('0', 6 - LEN(h.run_duration)) + CAST(h.run_duration AS varchar(6))
"job_duration"
from msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobschedules j
ON h.job_id = j.job_id
WHERE h.step_id = 0
) t1
) t2
SELECT * FROM #jobsched
You could take a look at the output of the above and "eyeball" it for job
conflicts, or you could try (*untested*)
SELECT t1.[name], t1.job_start, t1.job_end, t2.[name] AS "conflicting job",
t2.job_start, t2.job_end
FROM
( SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] = 'Job to analyze for conflicts'
) t1
RIGHT JOIN
(
SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] <> 'Job to analyze for conflicts'
) t2
ON (t2.job_start BETWEEN t1.job_start AND t1.job_end
OR t2.job_end BETWEEN t1.job_start AND t1.job_end)
to get a listing of jobs that are conflicting with the job named 'Job to
analyze for conflicts' .
It's also possible that the additional time for the DTS task to process is
due to locking. You could try adding another stop to the DTS job that dumbs
the output of sp_who2 into a table for further analyses.
Hope that the above helps.
--
"Patrice" wrote:

> Hi,
> I have a DTS package that runs 4 other packages on a nightly basis. This
> package usually takes about 2 hours to run, but ocasionally it jumps to
> around 5 hours to complete. There have not been any changes to the
> parameters or coding. It is the same process that causes this each time
> which is a cursor run strored procedure. There have not been any huge jum
ps
> in the number of records in the table or changes to the source data that
> would cause the cursor to take longer to run through. This seems to be
> intermittent, I can not easily check to see if something else in the netwo
rk
> on that server is happening since it is a customer site. Would you think
> that something outside of the package is affecting this since it only happ
ens
> once in a while (about once a w or so), like I mentioned - backups, ser
ver
> processes, other jobs etc.? I just don't know where to look if I have not
> changed anything within my package.
> Thanks,
> Patrice|||Sorry, that last query should have been
SELECT t1.[name], t1.job_start, t1.job_end, t2.[name] AS "conflicting job",
t2.job_start, t2.job_end
FROM
( SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] = 'Job to analyze for conflicts'
) t1
INNER JOIN --INNER replaced RIGHT for the JOIN
(
SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] <> 'Job to analyze for conflicts'
) t2
ON (t2.job_start BETWEEN t1.job_start AND t1.job_end
OR t2.job_end BETWEEN t1.job_start AND t1.job_end)
"Mark Williams" wrote:
> The are many reasons why performance can change over time. One is job
> contention, where multiple jobs are running at the same time. The followin
g
> will dump a listing of jobs, when they started and ended out of the
> msdb.dbo.sysjobhistory system table, and store it in a temporary table:
>
> select t2.[name], t2.job_start, DATEADD(ss,DATEDIFF(ss,'1980-01-01',
> '1980-01-01 ' + t2.job_duration),t2.job_start) AS job_end
> INTO #jobsched
> FROM (
> select t1.[name],
> CAST(
> start_date + ' ' + LEFT(t1.start_time,2) + ':' +
> SUBSTRING(t1.start_time,3,2) + ':' + RIGHT(t1.start_time,2) AS datetime
> ) AS "job_start",
> LEFT(t1.job_duration,2) + ':' + SUBSTRING(t1.job_duration,3,2) + ':' +
> RIGHT(t1.job_duration,2) AS job_duration
> FROM
> (
> select
> j.[name],
> LEFT(CAST(h.run_date AS char(8)), 4) + '-' + SUBSTRING(CAST(h.run_date AS
> char(8)),5,2) + '-' + RIGHT(CAST(h.run_date AS char(8)), 2) AS "start_date
",
> REPLICATE('0', 6 - LEN(h.run_time)) + CAST(h.run_time AS varchar(6)) AS
> "start_time",
> REPLICATE('0', 6 - LEN(h.run_duration)) + CAST(h.run_duration AS varchar(6
))
> "job_duration"
> from msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobschedules j
> ON h.job_id = j.job_id
> WHERE h.step_id = 0
> ) t1
> ) t2
> SELECT * FROM #jobsched
> You could take a look at the output of the above and "eyeball" it for job
> conflicts, or you could try (*untested*)
> SELECT t1.[name], t1.job_start, t1.job_end, t2.[name] AS "conflicting job",
> t2.job_start, t2.job_end
> FROM
> ( SELECT [name], job_start, job_end FROM #jobsched
> WHERE [name] = 'Job to analyze for conflicts'
> ) t1
> RIGHT JOIN
> (
> SELECT [name], job_start, job_end FROM #jobsched
> WHERE [name] <> 'Job to analyze for conflicts'
> ) t2
> ON (t2.job_start BETWEEN t1.job_start AND t1.job_end
> OR t2.job_end BETWEEN t1.job_start AND t1.job_end)
> to get a listing of jobs that are conflicting with the job named 'Job to
> analyze for conflicts' .
> It's also possible that the additional time for the DTS task to process is
> due to locking. You could try adding another stop to the DTS job that dumb
s
> the output of sp_who2 into a table for further analyses.
> Hope that the above helps.
> --
> "Patrice" wrote:
>

DTS Classes Not Showing in Visual Studio

I am trying to use VB.NET to run SSIS packages. However I don't have the various dts namespaces available. When I attempt to import them I only have Microsoft.SqlServer.Server in intellisense.

I am running on XP sp2, VS 2005 (full install) and even went so far as to install sql 2005 sp1 full install on my local machine.

What gives with only having Microsoft.SqlServer.Server available?

thanks,

Scott

Did you install SSIS?|||

Yes I did a full install of both vs and sql (including books and samples).

Some more info:

I'm trying to do this from an asp.net web service project. Do I need to add the assemblies to web.config? If so, does anyone have the assembly details (PublicKeyToken, etc)?

thanks--Scott

|||

But did you specifically install Integration Services? You can have the development tools without having all of SSIS.

However there's no reason I can think of that the Add Reference dialog in Visual Studio wouldn't show a long list of Microsoft.SqlServer... assemblies, unless they weren't there. You may need to tweak Web permissions later to use them successfully in the deployed app, but you should at least see them.

-Doug

|||You need to reference SSIS assemblies, like Microsoft.SQLServer.ManagedDTS.dll.
They are by default in C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies.|||

Doug,

Thanks for the reply. Yes, SSIS is installed. I had originally installed sql tools and books online. After doing a number or reinstalls of the tools and VS (rebooting here and there) I installed sql server dev ed (including the database engine, SSRS, SSIS, SSNS). I then set the services to manual (to not bog my machine down).

|||

Michael,

Thank you for your reply. That's my problem...I can't reference the assemblies. The imports statement only show Microsoft.SqlServer.Server.

If I add a reference to web.config like:

<add assembly="Microsoft.SqlServer.Dts.Runtime, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>

I receive an error, "Could not load assembly...<listed above>...system cannot find the file specified.

If I have a full install of sql server (all services installed locally), why can't my system find the assembly?

thanks again for your help

|||The assembly name is Microsoft.SqlServer.ManagedDTS, not Microsoft.SqlServer.Dts.Runtime (which is one of the namespaces defined in this assembly).|||

Ah, I was not aware of that. I added the following to my web.config and it works fine.

thanks for your help.

<add assembly="Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>

sql

DTS Classes Not Showing in Visual Studio

I am trying to use VB.NET to run SSIS packages. However I don't have the various dts namespaces available. When I attempt to import them I only have Microsoft.SqlServer.Server in intellisense.

I am running on XP sp2, VS 2005 (full install) and even went so far as to install sql 2005 sp1 full install on my local machine.

What gives with only having Microsoft.SqlServer.Server available?

thanks,

Scott

Did you install SSIS?|||

Yes I did a full install of both vs and sql (including books and samples).

Some more info:

I'm trying to do this from an asp.net web service project. Do I need to add the assemblies to web.config? If so, does anyone have the assembly details (PublicKeyToken, etc)?

thanks--Scott

|||

But did you specifically install Integration Services? You can have the development tools without having all of SSIS.

However there's no reason I can think of that the Add Reference dialog in Visual Studio wouldn't show a long list of Microsoft.SqlServer... assemblies, unless they weren't there. You may need to tweak Web permissions later to use them successfully in the deployed app, but you should at least see them.

-Doug

|||You need to reference SSIS assemblies, like Microsoft.SQLServer.ManagedDTS.dll.
They are by default in C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies.|||

Doug,

Thanks for the reply. Yes, SSIS is installed. I had originally installed sql tools and books online. After doing a number or reinstalls of the tools and VS (rebooting here and there) I installed sql server dev ed (including the database engine, SSRS, SSIS, SSNS). I then set the services to manual (to not bog my machine down).

|||

Michael,

Thank you for your reply. That's my problem...I can't reference the assemblies. The imports statement only show Microsoft.SqlServer.Server.

If I add a reference to web.config like:

<add assembly="Microsoft.SqlServer.Dts.Runtime, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>

I receive an error, "Could not load assembly...<listed above>...system cannot find the file specified.

If I have a full install of sql server (all services installed locally), why can't my system find the assembly?

thanks again for your help

|||The assembly name is Microsoft.SqlServer.ManagedDTS, not Microsoft.SqlServer.Dts.Runtime (which is one of the namespaces defined in this assembly).|||

Ah, I was not aware of that. I added the following to my web.config and it works fine.

thanks for your help.

<add assembly="Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>

DTS backup

What is the best way to backup DTS packages?
Won LeeIf you are storing them in SQL Server, you can backup msdb.
This is where the DTS packages are stored on the server.
-Sue
On Thu, 25 Sep 2003 13:30:10 -0400, Won Lee
<noemail@.nospam.com> wrote:
>What is the best way to backup DTS packages?
>
>Won Lee|||Thank you.
Sue Hoegemeier wrote:
> If you are storing them in SQL Server, you can backup msdb.
> This is where the DTS packages are stored on the server.
> -Sue
> On Thu, 25 Sep 2003 13:30:10 -0400, Won Lee
> <noemail@.nospam.com> wrote:
>
>>What is the best way to backup DTS packages?
>>
>>Won Lee
>

DTS and processing order

I have a set up DTS packages that I have set up to run every night.
Each package copys a table from a foreign database to our SQL Server
2000 system.
There are about 60 packages and some of the files have over half a
million records and take a little time. I have a package that calls all
of these as sub tasks. Is there a problem with just letting them run -
with no precedence constraints set.?
I have heard that you shouldn't run more that about 4 packages or so
packages at a time in parallel. If this is the case, do I have to set
them up one after another serially?
Also, if I have a one processer system, will running them in parallel
help me at all?
Thanks,
Tom.
Hi Tom,
There should not be any problems executing multiple Packages at the same
time.
SQL Server would process the individual tasks appropriately, It would not
start processing more packages, then what it can handle, depending upon the
available resources.
Regarding precedence of the packages : This mainly depends upon the design
of your database. If you have defined constraints on the tables, for which
you are importing the data, then you may have to keep the constraints in
mind and then set the precedence for importing the data. So that the import
process of the dependent table does not fail, due to non availability of
the records in the master table.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

DTS and processing order

I have a set up DTS packages that I have set up to run every night.
Each package copys a table from a foreign database to our SQL Server
2000 system.
There are about 60 packages and some of the files have over half a
million records and take a little time. I have a package that calls all
of these as sub tasks. Is there a problem with just letting them run -
with no precedence constraints set.?
I have heard that you shouldn't run more that about 4 packages or so
packages at a time in parallel. If this is the case, do I have to set
them up one after another serially?
Also, if I have a one processer system, will running them in parallel
help me at all?
Thanks,
Tom.Hi Tom,
There should not be any problems executing multiple Packages at the same
time.
SQL Server would process the individual tasks appropriately, It would not
start processing more packages, then what it can handle, depending upon the
available resources.
Regarding precedence of the packages : This mainly depends upon the design
of your database. If you have defined constraints on the tables, for which
you are importing the data, then you may have to keep the constraints in
mind and then set the precedence for importing the data. So that the import
process of the dependent table does not fail, due to non availability of
the records in the master table.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

DTS and processing order

I have a set up DTS packages that I have set up to run every night.
Each package copys a table from a foreign database to our SQL Server
2000 system.
There are about 60 packages and some of the files have over half a
million records and take a little time. I have a package that calls all
of these as sub tasks. Is there a problem with just letting them run -
with no precedence constraints set.?
I have heard that you shouldn't run more that about 4 packages or so
packages at a time in parallel. If this is the case, do I have to set
them up one after another serially?
Also, if I have a one processer system, will running them in parallel
help me at all?
Thanks,
Tom.Hi Tom,
There should not be any problems executing multiple Packages at the same
time.
SQL Server would process the individual tasks appropriately, It would not
start processing more packages, then what it can handle, depending upon the
available resources.
Regarding precedence of the packages : This mainly depends upon the design
of your database. If you have defined constraints on the tables, for which
you are importing the data, then you may have to keep the constraints in
mind and then set the precedence for importing the data. So that the import
process of the dependent table does not fail, due to non availability of
the records in the master table.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

DTS and Client Routing

Our shop executes "on the fly" DTS packages that copy (with a few
transformations, etc.) from one database to another database all on the same
server. One of the problems we have is that DTS routes the data stream
through the client which gives quite slow response time even for simple DTS
packages. Is there any way around this?
Surely, there's got to be a way to keep the data stream on the server
itself. All help is appreciated.
Run the package as a job on the server. That's the simplest way to achieve
this. Also, you could terminal service onto the server and then invoke the
DTS package from the server itself, instead of running it from a client
machine.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F98323F3-1D6A-4819-B160-376A37C024DA@.microsoft.com...
> Our shop executes "on the fly" DTS packages that copy (with a few
> transformations, etc.) from one database to another database all on the
> same
> server. One of the problems we have is that DTS routes the data stream
> through the client which gives quite slow response time even for simple
> DTS
> packages. Is there any way around this?
> Surely, there's got to be a way to keep the data stream on the server
> itself. All help is appreciated.

DTS and Client Routing

Our shop executes "on the fly" DTS packages that copy (with a few
transformations, etc.) from one database to another database all on the same
server. One of the problems we have is that DTS routes the data stream
through the client which gives quite slow response time even for simple DTS
packages. Is there any way around this?
Surely, there's got to be a way to keep the data stream on the server
itself. All help is appreciated.Run the package as a job on the server. That's the simplest way to achieve
this. Also, you could terminal service onto the server and then invoke the
DTS package from the server itself, instead of running it from a client
machine.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F98323F3-1D6A-4819-B160-376A37C024DA@.microsoft.com...
> Our shop executes "on the fly" DTS packages that copy (with a few
> transformations, etc.) from one database to another database all on the
> same
> server. One of the problems we have is that DTS routes the data stream
> through the client which gives quite slow response time even for simple
> DTS
> packages. Is there any way around this?
> Surely, there's got to be a way to keep the data stream on the server
> itself. All help is appreciated.

DTS and Client Routing

Our shop executes "on the fly" DTS packages that copy (with a few
transformations, etc.) from one database to another database all on the same
server. One of the problems we have is that DTS routes the data stream
through the client which gives quite slow response time even for simple DTS
packages. Is there any way around this?
Surely, there's got to be a way to keep the data stream on the server
itself. All help is appreciated.Run the package as a job on the server. That's the simplest way to achieve
this. Also, you could terminal service onto the server and then invoke the
DTS package from the server itself, instead of running it from a client
machine.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F98323F3-1D6A-4819-B160-376A37C024DA@.microsoft.com...
> Our shop executes "on the fly" DTS packages that copy (with a few
> transformations, etc.) from one database to another database all on the
> same
> server. One of the problems we have is that DTS routes the data stream
> through the client which gives quite slow response time even for simple
> DTS
> packages. Is there any way around this?
> Surely, there's got to be a way to keep the data stream on the server
> itself. All help is appreciated.

DTs and Buisness Intellegence

i have a bunch of dts packages..from sql2k
is there a utility to convert them to sql2005

any suggestions
daveOn Jan 30, 10:01 am, "DAVE" <dvs_...@.sbcglobal.netwrote:

Quote:

Originally Posted by

i have a bunch of dts packages..from sql2k
is there a utility to convert them to sql2005
>
any suggestions
dave

NO.
Minimal conversion is built in...depends on complexity

SQL Server 2005 Books Online:
Using the Package Migration Wizard
Known Package Migration Issues

Dts 2000 packages with SSIS

I need help,

I've got 51 pretty complex Dts packages that are running on Sql 2000. I'm
trying to make them run on Sql 2005 without at first migrating them with the
migration wizard, since i know that many of them cannot be migrated.

I'm using the "Execute DTS 2000 Package Task Editor" to make them run in an
SSIS package, some of them are running fine, but many of them, especially
those with a "Data Driven Query Task" are returning this error:

Error: System.Runtime.InteropServices.COMException (0x80040427): Execution
was canceled by user. at DTS.PackageClass.Execute() at
Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()

After many research on the web, i've reinstalled the Sql 2000 tools on the
Sql 2005 server, but i'm still receiving the same error. Also, i've tried to
repair the "Microsoft Sql Server 2005 backward compatibility" in the
Add/remove program of the Sql 2005 server without much success.

For your info, i've installed the hotfix Build 2153 on both the server and
the client and i'm still having the same problem......

Any idea?

Thanks.What happens when you just run them via dtsrun and/or the DTS designer?|||

They run fine without any errors!