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 package help

I'm creating my first dts package. I've specified my sql server connection and my excel file connection. I've added a bulk insert to import my excel data into a work table.

I've specified the row delimiter format as {LF} and Tab for column, but when I try to execute the dts package it fails and I get the following message:

Bulk Insert fails: Column is too long in the data file for row 1, column 3. Make sure the field terminator area specified correctly.
Bulk Insert data conversion error (truncation) for row 1, column 2 (LastName).

I don't understand why I'm getting a truncation error, there should be plenty of space for the insertion? I'm missing something simple I'm sure.

Any help is appreciated.Why are you specifying bulk insert with {LF} and Tab delimiters? Excel does not store data in that format, and that is why your DTS package can't import it using that format.|||This is the first DTS package I'm attempting to create, so I apologize if my questions are novice. When I added the bulk insert it asks to the specify a format for the row and column delimiter. Isn't the row delimiter a carriage return and the column delimiter a tab in an Excel file? What should I be using? I'm using DTS designer in SQL Server 2000.

I appreciate the help. Thank you.|||You probably want to create a connection to your Excel file as an Excel file, rather than as a text file. Excel files do not have row or column delimiters because the file format itself logically provides those delimiters.

-PatP|||I double checked and I did specify an Excel file as the connection, not a text file.

It's when I specify the Bulk Inset task that I have the option of setting a format type or format file. I've tried both options, but still get the same error?|||Assuming you are using SQL 2000, create an "idiot" spreadsheet with two or three columns and then create a DTS job to import it into a table using the Import Export Wizard. Save that job, and look at it to see how the Wizard did the import.

-PatP|||You want to use an Excel connection as the source, a Microsoft OLE DB Provider for SQL Server as the destination, and a Transform Data Task to move your records.

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 import new record can be replicated

I had merge replication setup. The replication is work fine if I insert new records
from enterprise manager. But, if use DTS to import new record from Access,
those new records can not be replicated. However, I updated one of those new record content, this updated new record will be replicated.
I appreicate you input.
James Cheng
James,
I have seen this type of behaviour before when if you bulk insert the rows
and choose the defaults, then FIRE_TRIGGERS is false and consequently the
rows are not added to MSmerge_contents. Disabling Fast Load may cause the
triggers to fire on new inserts or run sp_addtabletocontents to include the
rows already existing and then resynchronise.
Regards,
Paul Ibison
|||check out
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"James Cheng" <JamesCheng@.discussions.microsoft.com> wrote in message
news:3FF3AE3C-F66D-468F-92A3-EA3E2F879599@.microsoft.com...
> I had merge replication setup. The replication is work fine if I insert
new records
> from enterprise manager. But, if use DTS to import new record from
Access,
> those new records can not be replicated. However, I updated one of those
new record content, this updated new record will be replicated.
> I appreicate you input.
> James Cheng

DTS import data fail

I try to import data from other database ( that data base is in other country ) and insert into my table .It's succeeded normally but a few day ago it sometime fail .It's seem the data were selected but can't insert into my table . I have already checked filed name and field properties of both source and destination table .It''s same .The error message is below.

Executed as user: SCTINET1\sqlservice. ...t: Create Table TBL_ASCP_PLAN Step DTSRun OnFinish: Create Table TBL_ASCP_PLAN Step DTSRun OnStart: Copy Data from Results to TBL_ASCP_PLAN Step DTSRun OnProgress: Copy Data from Results to TBL_ASCP_PLAN Step; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress: Copy Data from Results to TBL_ASCP_PLAN Step; 2000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000 DTSRun OnProgress: Copy Data from Results to TBL_ASCP_PLAN Step; 3000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun OnProgress: Copy Data from Results to TBL_ASCP_PLAN Step; 4000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress: Copy Data from Results to TBL_ASCP_PLAN Step; 5000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 5000 DTSRun OnProg... Process Exit Code 1. The step failed.

Please help me.Sounds like it could be a network problem.

Have you spoke to your network admin staff to see if there are any issues that have not been brought to your attention.

Cheers|||Originally posted by aldo_2003
Sounds like it could be a network problem.

Have you spoke to your network admin staff to see if there are any issues that have not been brought to your attention.

Cheers

Thank you so much "Cheers" . I think it may not concern with the network problem because there are many jobs like this run at the sam time and it 's complete normally.

Waiting for reply|||Ok Mate,

We have elimainated the network issues as the cause. Good.

We can also eliminate the design of the package because becuse it worked before.

What do we have left ? The Data ......

Try this - In your DTS use a SELECT TOP from your source and see if you can get say 100 rows to work without fail.

If you can then you know that somewhere in the data is something that is making your insert fail. Dodgy character , pish data - have a look around and see - esp around the 5000th row.

Hope this helps|||Use DTS Package Logs to know in-depth details about package failure.
Also take help from this KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074) to resolve the dts when scheduled as a job.

HTH|||Originally posted by aldo_2003
Ok Mate,

We have elimainated the network issues as the cause. Good.

We can also eliminate the design of the package because becuse it worked before.

What do we have left ? The Data ......

Try this - In your DTS use a SELECT TOP from your source and see if you can get say 100 rows to work without fail.

If you can then you know that somewhere in the data is something that is making your insert fail. Dodgy character , pish data - have a look around and see - esp around the 5000th row.

Hope this helps

Thank you so much for many help. Today My DTS package is run complete normally . Yesterday I added some condition "where" in my sql package the run duration is used around 1 hours .( last time I selected all data without any condition ,the run duration was used around 2.00 hours )

Is it possible the set DTS waiting time until the job complete ( no limited ) ? Because the network traffic may busy in some time .

Jupiter_111

DTS import CSV record to multiple tables

Hi,

I am using DTS in SQL server2k

What is the best way to insert CSV records, where 1 record maps onto multiple tables with parent/child or PK/FK relationship.

eg.
CSV record
(ID, param1, param2, param3)

ParentTable(ID as PK)
Param1Table(ID as FK in ParentTable, param1)
Param2Table(ID as FK in ParentTable, param2)
Param3Table(ID as FK in ParentTable, param3)

Currently i am using multiple Transform Data tasks with ActiveX script. But it seems to work slow.

Thanks and regards,
HenryI think I would use a staging table, then use a sproc to slice and dice...

DTS Help

I have around 400 different Select -- Insert queries getting data from 200+
tbles. Data in each table can vary from 1 million to 3 million. All the data
is being inserted in the same table.
What's the best way to do this? Insert statement obviously wouldn't work. Is
there a way I can use all the queries inside one DTS package?XXX,
BPC out and BCP or BULK INSERT in might be a faster option. Also, do you
need to bring over all of the records or can you restrict the data being
transfered i.e., date range? One other thing, you might consider a data
archival policy and indexing strategies to make your queries run faster.
HTH
Jerry
"XXX" <sa@.nomail.com> wrote in message
news:OXyD%23P6vFHA.2792@.tk2msftngp13.phx.gbl...
>I have around 400 different Select -- Insert queries getting data from 200+
>tbles. Data in each table can vary from 1 million to 3 million. All the
>data is being inserted in the same table.
> What's the best way to do this? Insert statement obviously wouldn't work.
> Is there a way I can use all the queries inside one DTS package?
>|||No, I have a client who does registeration and Profile building in
PepopleSoft.
They are building a new system, where data from several tables (200) gets
merged into 3 tables. I have to get the initial load before the new system
goes live, after that this will be done differently (through PeopleSoft).
To answer your question all the data needs to be pulled out. I was hoping
there was something easier than BCP...
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eyU93T6vFHA.708@.TK2MSFTNGP10.phx.gbl...
> XXX,
> BPC out and BCP or BULK INSERT in might be a faster option. Also, do you
> need to bring over all of the records or can you restrict the data being
> transfered i.e., date range? One other thing, you might consider a data
> archival policy and indexing strategies to make your queries run faster.
> HTH
> Jerry
> "XXX" <sa@.nomail.com> wrote in message
> news:OXyD%23P6vFHA.2792@.tk2msftngp13.phx.gbl...
>|||Before you do the initial loads be sure to increase the transaction log
space and max setting. You might also want to begin with a small load and
monitor the t-log usage using DBCC SQLPERF(LOGSPACE) to ensure adequate log
space. Be sure to perform a backup and shrink the t-log when the loads are
complete (or as you increment the loads if needed).
HTH
Jerry
"XXX" <sa@.nomail.com> wrote in message
news:upR0zk6vFHA.464@.TK2MSFTNGP15.phx.gbl...
> No, I have a client who does registeration and Profile building in
> PepopleSoft.
> They are building a new system, where data from several tables (200) gets
> merged into 3 tables. I have to get the initial load before the new system
> goes live, after that this will be done differently (through PeopleSoft).
> To answer your question all the data needs to be pulled out. I was hoping
> there was something easier than BCP...
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eyU93T6vFHA.708@.TK2MSFTNGP10.phx.gbl...
>