DTS return

I am trying to execute a DTS package which will update four tables on a central server from various souces (each source will insert records into all four tables).

We are running this code by calling a stored procedure from VB, which includes code to execute the DTS package. The problem I am having is the VB code continues on even though the package has completed.

SET @.SQLStr = 'DTSRun /S CENTRAL1 /N DTS_TEST_HU_MoveCenterScreens /E'
EXEC @.Result = Master.dbo.xp_cmdshell @.SQLStr

I return @.Result. I would have thought that the return value from the execution of the sql package would not return until it was completed. However, it returns right away. The package takes about 10 mintutes to run, but the return variable is populated in less than a second.

The next step of the process relies upon the dts's completion.
I am using SQL 2000 as my DB

Any thoughts?You should take a look at this article:

Execute DTS via Stored Procedure (http://www.databasejournal.com/features/mssql/article.php/1459181)

Have you thought about executing DTS from VB?|||Thank you for the response. While I was still trying to figure out what to do I started to explore VB and found the DTS object. We are now using that object and it has solved my problems.
As for the link, I was trying to utilize the return variable but my code continued to execute even if the dts package was not complete.

Originally posted by achorozy
You should take a look at this article:

Execute DTS via Stored Procedure (http://www.databasejournal.com/features/mssql/article.php/1459181)

Have you thought about executing DTS from VB?sqlsql

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 package desined in server 2000, but update 2005 database

Hello
we have sql server 2000 for development environment and 2005 for
production. i have created a DTS package in 2000 to update 5 tables on
Production.
But my DTS package is not updating tables on production. i am getting
following error message:
"To connect to this server you must use SQL Server Management Studio
or SQL Server Managesment Objects (SMO)"
All the comments and suggestions are welcome and will be appreciated.
Thanks
AjitYou should use SSIS to work with SQL 2005. SSIS can connect with SQL 2000,
but DTS cannot do it withj SQL 2005.
In SQL 2005 you just can run DTS 2000 packages using the "Execute DTS 2000
Package" task. TO do it you should install the DTS 2000 run-time Engine.
See the following topic on Book on Line:
SQL Server Integration Services (SSIS) -> Integration Services Objects and
Concepts -> Control Flow Elements -> Integration Services Tasks -> Execute
DTS 2000 Package Task
Gilberto Zampatti
"The Bichoo" wrote:

> Hello
> we have sql server 2000 for development environment and 2005 for
> production. i have created a DTS package in 2000 to update 5 tables on
> Production.
> But my DTS package is not updating tables on production. i am getting
> following error message:
> "To connect to this server you must use SQL Server Management Studio
> or SQL Server Managesment Objects (SMO)"
> All the comments and suggestions are welcome and will be appreciated.
> Thanks
> Ajit
>

DTS package desined in server 2000, but update 2005 database

Hello
we have sql server 2000 for development environment and 2005 for
production. i have created a DTS package in 2000 to update 5 tables on
Production.
But my DTS package is not updating tables on production. i am getting
following error message:
"To connect to this server you must use SQL Server Management Studio
or SQL Server Managesment Objects (SMO)"
All the comments and suggestions are welcome and will be appreciated.
Thanks
Ajit
You should use SSIS to work with SQL 2005. SSIS can connect with SQL 2000,
but DTS cannot do it withj SQL 2005.
In SQL 2005 you just can run DTS 2000 packages using the "Execute DTS 2000
Package" task. TO do it you should install the DTS 2000 run-time Engine.
See the following topic on Book on Line:
SQL Server Integration Services (SSIS) -> Integration Services Objects and
Concepts -> Control Flow Elements -> Integration Services Tasks -> Execute
DTS 2000 Package Task
Gilberto Zampatti
"The Bichoo" wrote:

> Hello
> we have sql server 2000 for development environment and 2005 for
> production. i have created a DTS package in 2000 to update 5 tables on
> Production.
> But my DTS package is not updating tables on production. i am getting
> following error message:
> "To connect to this server you must use SQL Server Management Studio
> or SQL Server Managesment Objects (SMO)"
> All the comments and suggestions are welcome and will be appreciated.
> Thanks
> Ajit
>

DTS package desined in server 2000, but update 2005 database

Hello
we have sql server 2000 for development environment and 2005 for
production. i have created a DTS package in 2000 to update 5 tables on
Production.
But my DTS package is not updating tables on production. i am getting
following error message:
"To connect to this server you must use SQL Server Management Studio
or SQL Server Managesment Objects (SMO)"
All the comments and suggestions are welcome and will be appreciated.
Thanks
AjitYou should use SSIS to work with SQL 2005. SSIS can connect with SQL 2000,
but DTS cannot do it withj SQL 2005.
In SQL 2005 you just can run DTS 2000 packages using the "Execute DTS 2000
Package" task. TO do it you should install the DTS 2000 run-time Engine.
See the following topic on Book on Line:
SQL Server Integration Services (SSIS) -> Integration Services Objects and
Concepts -> Control Flow Elements -> Integration Services Tasks -> Execute
DTS 2000 Package Task
Gilberto Zampatti
"The Bichoo" wrote:
> Hello
> we have sql server 2000 for development environment and 2005 for
> production. i have created a DTS package in 2000 to update 5 tables on
> Production.
> But my DTS package is not updating tables on production. i am getting
> following error message:
> "To connect to this server you must use SQL Server Management Studio
> or SQL Server Managesment Objects (SMO)"
> All the comments and suggestions are welcome and will be appreciated.
> Thanks
> Ajit
>

DTS package behaviour when used by > 1 User?

Hi all

I have a DTS package that users of the database can run which basically acts like a 'live update' (as the database is based on a values produced from another system) and it takes roughly 30 or so seconds to run...

The dts package is not going to have a particularly large hit rate but i am interested in knowing what will happen if a user (user 1) attempts to run the package when it is in already in use by another user (user 2) ?

Will User 1 simply have to wait untill the package completes for User 1?

OR

Will a new seperate 'instance' of the package be used by User 1?

Any info would be very helpful

CheersYou will have problems
the second DTS will start before the first stop

(maybe it's not problem it depends what they do)|||That was the answer i was hoping for really.

Basically this shouldn't be a problem for my scenario. The Users of the System are accountants scattered at various locations around the country and each of them Updates the their own locations data (via the dts package)and so this will not conflict with any other user updating the DB as it is a different locations data.

I was worried that if the user had to wait for the dts package to finish then i may occasionally have timeout issues (not that i haven't got a backup for this).

Thanks for your help, mucho appreciated.

DTS job

Hi ,
During a DTS job what are the actions that cannot be
done on a table ? i.e DELETE/TRUNCATE , UPDATE , INSERT '
i am doing some migration using DTS job but the current
database is still being used by users
thks & rdgsYou can do anything in a DTS job that you have permission to do,,, select,
insert, update, delete, truncate, select int, create, drop, alter...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:337201c48f21$75909980$a301280a@.phx.gbl...
> Hi ,
> During a DTS job what are the actions that cannot be
> done on a table ? i.e DELETE/TRUNCATE , UPDATE , INSERT '
> i am doing some migration using DTS job but the current
> database is still being used by users
> thks & rdgs|||Hi Wayne ,
what i actually meant was during a DTS job that
transfers data from Table A in Location A to Location B.
What shld i not do to Table A so that DTS job will not
fail ?
thks & rdgs
>--Original Message--
>You can do anything in a DTS job that you have permission
to do,,, select,
>insert, update, delete, truncate, select int, create,
drop, alter...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:337201c48f21$75909980$a301280a@.phx.gbl...
INSERT '[vbcol=seagreen]
current[vbcol=seagreen]
>
>.
>

DTS job

Hi ,
During a DTS job what are the actions that cannot be
done on a table ? i.e DELETE/TRUNCATE , UPDATE , INSERT ?
i am doing some migration using DTS job but the current
database is still being used by users
thks & rdgs
You can do anything in a DTS job that you have permission to do,,, select,
insert, update, delete, truncate, select int, create, drop, alter...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:337201c48f21$75909980$a301280a@.phx.gbl...
> Hi ,
> During a DTS job what are the actions that cannot be
> done on a table ? i.e DELETE/TRUNCATE , UPDATE , INSERT ?
> i am doing some migration using DTS job but the current
> database is still being used by users
> thks & rdgs
|||Hi Wayne ,
what i actually meant was during a DTS job that
transfers data from Table A in Location A to Location B.
What shld i not do to Table A so that DTS job will not
fail ?
thks & rdgs
>--Original Message--
>You can do anything in a DTS job that you have permission
to do,,, select,
>insert, update, delete, truncate, select int, create,
drop, alter...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:337201c48f21$75909980$a301280a@.phx.gbl...
INSERT ?[vbcol=seagreen]
current
>
>.
>

DTS job

Hi ,
During a DTS job what are the actions that cannot be
done on a table ? i.e DELETE/TRUNCATE , UPDATE , INSERT '
i am doing some migration using DTS job but the current
database is still being used by users
thks & rdgsYou can do anything in a DTS job that you have permission to do,,, select,
insert, update, delete, truncate, select int, create, drop, alter...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:337201c48f21$75909980$a301280a@.phx.gbl...
> Hi ,
> During a DTS job what are the actions that cannot be
> done on a table ? i.e DELETE/TRUNCATE , UPDATE , INSERT '
> i am doing some migration using DTS job but the current
> database is still being used by users
> thks & rdgs|||Hi Wayne ,
what i actually meant was during a DTS job that
transfers data from Table A in Location A to Location B.
What shld i not do to Table A so that DTS job will not
fail ?
thks & rdgs
>--Original Message--
>You can do anything in a DTS job that you have permission
to do,,, select,
>insert, update, delete, truncate, select int, create,
drop, alter...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:337201c48f21$75909980$a301280a@.phx.gbl...
>> Hi ,
>> During a DTS job what are the actions that cannot be
>> done on a table ? i.e DELETE/TRUNCATE , UPDATE ,
INSERT '
>> i am doing some migration using DTS job but the
current
>> database is still being used by users
>> thks & rdgs
>
>.
>

DTS Insert or Update

Is there an easy way with DTS to pump data from one table to another so that it will update the row if it exists (the source and destination have the same value for the ID colum) or insert it if it doesn't.

I know this can be done with stored procedures/sql by doing IF EXISTS UPDATE ELSE INSERT but there are many tables and columns and this will be very tiime consuming.

i think your going to have to use VBScript in the transformation step of the DTS Package to evaluate if the record exists. Similar to what one would do in the stored proc.

hth,

mcm

DTS in Cluster environment!

Are there issues one should be aware of using running DTS in Clustered
environment?
For example, i need DTS read data from flat files and update tables in a
database.
I assume its not required to have this package(s) run on both servers in an
ACTIVE - ACTIVE environment.
All opinions are welcome.
Thanks
Tunji
You should have no problems. The DTS will exist inside the MSDB database so it will be in both of the Clustered servers. The flat file you need to read though needs to be in a stable location. (one that does not change owners in the clustered environmen
t).
|||Thanks Jeff!
Tunji Ogundeji
mcdba, ocp
www.geniant.com
"Jeff Duncan" <jduncan@.gtefc.org> wrote in message
news:2C6CE066-C7D8-4888-897D-06765ADDE53B@.microsoft.com...
> You should have no problems. The DTS will exist inside the MSDB database
so it will be in both of the Clustered servers. The flat file you need to
read though needs to be in a stable location. (one that does not change
owners in the clustered environment).

DTS in Cluster environment!

Are there issues one should be aware of using running DTS in Clustered
environment?
For example, i need DTS read data from flat files and update tables in a
database.
I assume its not required to have this package(s) run on both servers in an
ACTIVE - ACTIVE environment.
All opinions are welcome.
Thanks
TunjiYou should have no problems. The DTS will exist inside the MSDB database so
it will be in both of the Clustered servers. The flat file you need to rea
d though needs to be in a stable location. (one that does not change owners
in the clustered environmen
t).|||Thanks Jeff!
Tunji Ogundeji
mcdba, ocp
www.geniant.com
"Jeff Duncan" <jduncan@.gtefc.org> wrote in message
news:2C6CE066-C7D8-4888-897D-06765ADDE53B@.microsoft.com...
> You should have no problems. The DTS will exist inside the MSDB database
so it will be in both of the Clustered servers. The flat file you need to
read though needs to be in a stable location. (one that does not change
owners in the clustered environment).

DTS in Cluster environment!

Are there issues one should be aware of using running DTS in Clustered
environment?
For example, i need DTS read data from flat files and update tables in a
database.
I assume its not required to have this package(s) run on both servers in an
ACTIVE - ACTIVE environment.
All opinions are welcome.
Thanks
--
TunjiYou should have no problems. The DTS will exist inside the MSDB database so it will be in both of the Clustered servers. The flat file you need to read though needs to be in a stable location. (one that does not change owners in the clustered environment).|||Thanks Jeff!
--
Tunji Ogundeji
mcdba, ocp
www.geniant.com
"Jeff Duncan" <jduncan@.gtefc.org> wrote in message
news:2C6CE066-C7D8-4888-897D-06765ADDE53B@.microsoft.com...
> You should have no problems. The DTS will exist inside the MSDB database
so it will be in both of the Clustered servers. The flat file you need to
read though needs to be in a stable location. (one that does not change
owners in the clustered environment).

DTS Import Update table.

HI.. My name is Raman
I am the beginner in using DTS Import facility.

Can any one help me how to update the table while importing the Execl file using DTS import wizard
I imported an Excel Files that files having three records .I wont import once again same e file, which is modified in Excel file. Once I import one more files with same fields that should be updated. Instead of deleting or appending to the same table
plz any one can help me in this regard.Selecting "Delete rows in destination table" of "Column Mappings" tab in "Column Mappings and Transformations" page will solve your problem. To get to this page click "..." below "Transform" in "Select Source Tables and Views" page.|||No dear if i do like that.it will deletes all the rows and it will insert once aggain.i dont wont to do like that.just i need to update the table.
ok finally i come to know that there is no update table statement in DTS import option is it true??
ok any how thanx for u r replay byee..
Raman :)

DTS Help with Update?

I'd like to create a DTS package to update records within a table
leaving other information stored in those records as is. For example,
table a has a record with 5 columns where 3 are null. An external
source has the non-null values for those 3 columns. Can anyone help
with some general steps as to how I might fill those 3 columns in the
sql table?

ThanksIf the source is the text file. u must first import text source into
table.
then exec update statement in sql query task.

DTS Global Variables Behaviour with > 1 User

Hi all

I have a DTS package that users of the database can run which basically acts like a 'live update' (as the database is based on a values produced from another system) and it takes roughly 30 or so seconds to run...

The dts package is not going to have a particularly large hit rate but i am interested in knowing what will happen if a user (user 1) attempts to run the package when it is in already in use by another user (user 2) ?

The dts package is from a sp using the dtsrun utility (passes in global variables).

I know that a second DTS will start before the first has finished but what i am not sure about is what will happen to the Global Variables when the second DTS package starts - i.e. will starting a second instance of the package with different variables have an effect on the first DTS's global variables while it is running?

I hope the answer is "No Tom, it work fine!"DTS is a weird beast to describe, because there are so many different contexts. The short answer is that each of the instances of a DTS package will have their own global variables.

-PatP|||Pat

Thanks for the help. I really appreciate it.

DTS Global Variables

Hello,

In the properties for a DTS package, I have a string variable. I need to execute an update statement based on this variable. I was going to use Execute SQL, but I didn't know now to get a value from the variables section.

I store the value there because I will be using dtsrun or the DTS library to execute the package, which I can change this value upon execution. How do I execute an update based on the value?

BrianAre you saying that you need to use this string variable in your WHERE clause? If so, you should be able to use a question mark (?) where you need to use that string value, then use the Parameters button to tell DTS which global variable to use for the parameter.

Terri|||Hey,

That was correct. THanks.

Brian|||Cool! I have found DTS to be one of the most powerful yet frustrating tools to use. Could they make it any more obtuse? Took me 2 years to realize there was such thing as global variables, which then opened up a whole new world.

Anyway, Marcin Policht has written a nice series on SQL Server 2000 DTS. There are 12 parts to it, and part 5 is:SQL Server 2000 DTS Part 5 - DTS Designer Tasks and Global Variables. Definitely worth bookmarking.

Terri