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 question?????????

Hi,
I use DTS import/export to import data from DB2 into SQL 2000 tables.
It works find. However , I want to truncate the tables first before the
copy.
What is the best way to do this?
Thank you for all your suggestions.Do you have ajob set up for the DTS package?|||I have the DTS save under local PAckages, but I don't know how to
create a job for it.|||From EM right click the package, select 'schedule package'|||once you schedule the package, go to managment -> Jobs. Find the Job that you just created for the DTS. Right click on the job and go to the steps tab. There will be a step already in there for the data that you are transfering. Click on the insert button to insert a new step. A new step box will apear for you to enter info in. Name the step whatever you want. Fot Type, make sure its t-sql script. Make sure you select the right database. In the command text box type truncate table (and the name of the table yo want to clear). Click Apply. THen you will be back in steps tab. There are some arrows to rearrange the steps. Make sure that the truncate step is first. There is also a drop down box to where you select which step goes first.

Hope this helps.|||Thank you all for helping. I'll try that.

Thanks again|||No problem. Let me know if you run into a snag.

DTS question

Ok, i have a set of foxpro tables and a SQL 2000 db.

i'm trying to move a table of data from the fox to the sql2000.

I'm using dts to point a connection to a particular table in fox, and setting the destination to my sql database.

i select 'create table' and it makes the table in sql for me.

i click on the transformations tab, and it sets up the transforms. no problem.

when i execute, i'm getting these generic errors ('cannot convert data type') or some such bs. the error does not tell me WHAT TRANSFORMATION or what source field is causing the problem.

so i manually went and eliminated each dts transform (one at a time) and ran till i found the offending dts transform.

so i figured this one out.

here's my question:

WHAT A PAIN IN THE BUTT! what can i do to get dts to tell me what friggin transform is the offending transform?

I've got several HUNDRED tables to convert yet, and if i have to do this again i'm going to jump off a tall building.

there's got to be a better way...When we moved our data from Visual FoxPro into SQL Server we used the Upsize Wizard from within Visual FoxPro. It was pretty painless (although I believe we had to add all of the free tables to a database). Would this work for you?

Terri

DTS Problem

I have a DTS package which copies rows from 150 tables using Union ALL.
Around 30 million rows are involved. However package fails after 8.6 million
rows with the error:
Error at Source for Row number 8697091. Cannot convert nvarchar value of
('XXX ') to smallint. Errors encountered so far in this task: 1.
The column on which this fails is a Varchar. It looks like the DTS package
is treating the source package as a Smallint. Is there a way aroun this?I would have thought it is trying to convert a varchar (source value)
to a smallint (destination value) '|||I don't think that is the case. My Source (SQL query) column has values like
2, 'XXX' and the column in the destination table is defined to be varchar.
The Source tables have this column defn as smallint and varchar.
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1127930452.777681.80430@.g43g2000cwa.googlegroups.com...
>I would have thought it is trying to convert a varchar (source value)
> to a smallint (destination value) '
>|||How do you have a column as Smallint & Varchar ?|||This is one time data transfer and this is my staging table. I am bringing
data from various tables which may be int or varchar.
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1127930934.509640.143400@.z14g2000cwz.googlegroups.com...
> How do you have a column as Smallint & Varchar ?
>|||Hmm strange...
Maybe you could replace 'XXX' with zero in your Select statement.
I know if you extract Data to a text file you skip the exceptions and
write them to an exceptions file.
If you used this approach you could have several txt files, which would
be from your source tables, and then import these in to new table.
Sorry I can't be of any more help
Barry|||I have tried exporting this to a text file, but same issue.
Anyone? All I need I think is a way to tell the DTS that the source column
is varchar.
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1127931933.066339.209980@.g49g2000cwa.googlegroups.com...
> Hmm strange...
> Maybe you could replace 'XXX' with zero in your Select statement.
> I know if you extract Data to a text file you skip the exceptions and
> write them to an exceptions file.
> If you used this approach you could have several txt files, which would
> be from your source tables, and then import these in to new table.
> Sorry I can't be of any more help
> Barry
>|||You need to specifically convert the value to varchar for the column that
has integer value.
try this:
select '0e'
Union
Select 1
It will give you the error mesage.
But this one won't:
select '0e'
Union
Select Convert(varchar(5), 1).
Perayu
"XXX" <sa@.nomail.com> wrote in message
news:%23Ji7dqFxFHA.2008@.TK2MSFTNGP10.phx.gbl...
>I have tried exporting this to a text file, but same issue.
> Anyone? All I need I think is a way to tell the DTS that the source column
> is varchar.
> "Barry" <barry.oconnor@.singers.co.im> wrote in message
> news:1127931933.066339.209980@.g49g2000cwa.googlegroups.com...
>

DTS Package: Data Loading ?

Hi,
Can someone help me with the following
I have to automate the process of loading the files from source to target.
I have to use partitioned tables which should be created automatically to
load the data. These tables should have different name each week(for e.g
customers40, customers48 etc)
Using Northwind Example for Customers table
The syntax is
declare @.tablestmt nvarchar(2555)
set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)'
exec sp_executesql @.tablestmt
The problem is each week I get a file with different name. Using Nothwind
Example. Suppose I get a file CustomersA1. Next week I get the file with
the name CustomersA8. This file that I get each week has to go in a table
that is created at run time with different name(as described above). How do I
tell DTS that the file name changes every week. What tasks should I use.
All the task have specified path for the filenames. How do I tell the task
that filename changes every week.
Any example with syntax(for Northwind/Customers in this case) will be a
great help.
Thanks
Steve
Hi Steve,
well the only way I see is to use dynamic properties. Define a global
variable, assign the global to the specific data transformation task and use
a ActiveX task at the beginning of your packge to calculate the name of your
import table and store it in the global variable.
Regards,
Meinhard
"Steve" <Steve@.discussions.microsoft.com> schrieb im Newsbeitrag
news:20AE8726-8E58-4373-BF88-D06736C8FBAF@.microsoft.com...
> Hi,
> Can someone help me with the following
> I have to automate the process of loading the files from source to target.
> I have to use partitioned tables which should be created automatically to
> load the data. These tables should have different name each week(for e.g
> customers40, customers48 etc)
> Using Northwind Example for Customers table
> The syntax is
> declare @.tablestmt nvarchar(2555)
> set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
> getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )'
> exec sp_executesql @.tablestmt
>
> The problem is each week I get a file with different name. Using Nothwind
> Example. Suppose I get a file CustomersA1. Next week I get the file with
> the name CustomersA8. This file that I get each week has to go in a table
> that is created at run time with different name(as described above). How
> do I
> tell DTS that the file name changes every week. What tasks should I use.
> All the task have specified path for the filenames. How do I tell the
> task
> that filename changes every week.
> Any example with syntax(for Northwind/Customers in this case) will be a
> great help.
>
> Thanks
> Steve

DTS Package: Data Loading ?

Hi,
Can someone help me with the following
I have to automate the process of loading the files from source to target.
I have to use partitioned tables which should be created automatically to
load the data. These tables should have different name each week(for e.g
customers40, customers48 etc)
Using Northwind Example for Customers table
The syntax is
declare @.tablestmt nvarchar(2555)
set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)'
exec sp_executesql @.tablestmt
The problem is each week I get a file with different name. Using Nothwind
Example. Suppose I get a file CustomersA1. Next week I get the file with
the name CustomersA8. This file that I get each week has to go in a table
that is created at run time with different name(as described above). How do
I
tell DTS that the file name changes every week. What tasks should I use.
All the task have specified path for the filenames. How do I tell the task
that filename changes every week.
Any example with syntax(for Northwind/Customers in this case) will be a
great help.
Thanks
SteveHi Steve,
well the only way I see is to use dynamic properties. Define a global
variable, assign the global to the specific data transformation task and use
a ActiveX task at the beginning of your packge to calculate the name of your
import table and store it in the global variable.
Regards,
Meinhard
"Steve" <Steve@.discussions.microsoft.com> schrieb im Newsbeitrag
news:20AE8726-8E58-4373-BF88-D06736C8FBAF@.microsoft.com...
> Hi,
> Can someone help me with the following
> I have to automate the process of loading the files from source to target.
> I have to use partitioned tables which should be created automatically to
> load the data. These tables should have different name each week(for e.g
> customers40, customers48 etc)
> Using Northwind Example for Customers table
> The syntax is
> declare @.tablestmt nvarchar(2555)
> set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
> getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
> [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )'
> exec sp_executesql @.tablestmt
>
> The problem is each week I get a file with different name. Using Nothwind
> Example. Suppose I get a file CustomersA1. Next week I get the file with
> the name CustomersA8. This file that I get each week has to go in a table
> that is created at run time with different name(as described above). How
> do I
> tell DTS that the file name changes every week. What tasks should I use.
> All the task have specified path for the filenames. How do I tell the
> task
> that filename changes every week.
> Any example with syntax(for Northwind/Customers in this case) will be a
> great help.
>
> Thanks
> Steve

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 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
AlHarag
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...
>> 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:14:08 +0100, Harag
<haragREMOVECAPITALS@.softhome.net> wrote:
>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.
>
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
>>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...
>> 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

DTS package execution time

Hi - I currently have a DTS package that takes raw data from a SQL
table and inserts records into several tables via a custom ActiveX
transformation. The package uses logic to determine which table to
insert into and then calls DTSlookups to perform the inserts. The #
of records I'm working with is small (3,000-10,000). If I perform a
"test, the rows are actually inserted, and rather quickly. If I
execute the package through normal methods, the execution takes several
hours and often times out?
Any ideas'Running a DTS package within Enterprise Manager executes it locally on
the machine where EM is running. Running it using a schedule runs it
on the server where the agent service is running. Different machines
have different resources and are under different loads, and have
different distances from the data. Look for data traveling over the
network vs remaining local.
As you describe it, both the source data and the final destination of
the data are in SQL Server. Are they on the same SQL Server? If so,
did you consider using stored procedures? Keeping all the work
withing SQL Server itself has some performance advantages.
Roy Harvey
Beacon Falls, CT
On 27 Jul 2006 10:37:26 -0700, clawdaddy@.gmail.com wrote:

>Hi - I currently have a DTS package that takes raw data from a SQL
>table and inserts records into several tables via a custom ActiveX
>transformation. The package uses logic to determine which table to
>insert into and then calls DTSlookups to perform the inserts. The #
>of records I'm working with is small (3,000-10,000). If I perform a
>"test, the rows are actually inserted, and rather quickly. If I
>execute the package through normal methods, the execution takes several
>hours and often times out?
>Any ideas'|||clawdaddy@.gmail.com wrote:
> Hi - I currently have a DTS package that takes raw data from a SQL
> table and inserts records into several tables via a custom ActiveX
> transformation. The package uses logic to determine which table to
> insert into and then calls DTSlookups to perform the inserts. The #
> of records I'm working with is small (3,000-10,000). If I perform a
> "test, the rows are actually inserted, and rather quickly. If I
> execute the package through normal methods, the execution takes several
> hours and often times out?
> Any ideas'
>
There's really not enough info to come up with a cause, but if this is a
SQL-to-SQL process (reading from SQL/writing to SQL), I'd question why
you used DTS to do this. I think you'd get better performance, not to
mention easier debugging, by doing this a series of INSERT INTO/SELECT
statements.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

DTS package execution time

Hi - I currently have a DTS package that takes raw data from a SQL
table and inserts records into several tables via a custom ActiveX
transformation. The package uses logic to determine which table to
insert into and then calls DTSlookups to perform the inserts. The #
of records I'm working with is small (3,000-10,000). If I perform a
"test, the rows are actually inserted, and rather quickly. If I
execute the package through normal methods, the execution takes several
hours and often times out?
Any ideas'Running a DTS package within Enterprise Manager executes it locally on
the machine where EM is running. Running it using a schedule runs it
on the server where the agent service is running. Different machines
have different resources and are under different loads, and have
different distances from the data. Look for data traveling over the
network vs remaining local.
As you describe it, both the source data and the final destination of
the data are in SQL Server. Are they on the same SQL Server? If so,
did you consider using stored procedures? Keeping all the work
withing SQL Server itself has some performance advantages.
Roy Harvey
Beacon Falls, CT
On 27 Jul 2006 10:37:26 -0700, clawdaddy@.gmail.com wrote:
>Hi - I currently have a DTS package that takes raw data from a SQL
>table and inserts records into several tables via a custom ActiveX
>transformation. The package uses logic to determine which table to
>insert into and then calls DTSlookups to perform the inserts. The #
>of records I'm working with is small (3,000-10,000). If I perform a
>"test, the rows are actually inserted, and rather quickly. If I
>execute the package through normal methods, the execution takes several
>hours and often times out?
>Any ideas'|||clawdaddy@.gmail.com wrote:
> Hi - I currently have a DTS package that takes raw data from a SQL
> table and inserts records into several tables via a custom ActiveX
> transformation. The package uses logic to determine which table to
> insert into and then calls DTSlookups to perform the inserts. The #
> of records I'm working with is small (3,000-10,000). If I perform a
> "test, the rows are actually inserted, and rather quickly. If I
> execute the package through normal methods, the execution takes several
> hours and often times out?
> Any ideas'
>
There's really not enough info to come up with a cause, but if this is a
SQL-to-SQL process (reading from SQL/writing to SQL), I'd question why
you used DTS to do this. I think you'd get better performance, not to
mention easier debugging, by doing this a series of INSERT INTO/SELECT
statements.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

DTS Package Error: Login failed for user sa

I am trying to edit a DTS package which is a transfer of data between two tables (connections)

i can view the properties of either connection but i get this error when i try to edit the transformation section of a pakcage that is supposed to transfer data from one table to another.

Login failed for user 'sa'

i am running enterprise manager on the server itself so i shouldn't have any connection issues.

if i try to change the authentication on either connection from "use SQL authentication" to "use windows NT authentication" i get this error:

' Cannot generate SSPI context 'Note: There are too many unknowns here to intelligently troubleshoot the situation:

S1 [I am trying to edit a DTS package which is a transfer of data between two tables (connections)]
Q1 Are both tables on the same server or on different servers?

S2 [i can view the properties of either connection but i get this error when i try to edit the transformation section of a pakcage that is supposed to transfer data from one table to another.
Login failed for user 'sa' i am running enterprise manager on the server itself so i shouldn't have any connection issues.]
Q2 i Have you tried entering the login password for sa
ii What is the result then?
iii Can you sucessfully login using say, Query Analyzer as sa?

S3 if i try to change the authentication on either connection from "use SQL authentication" to "use windows NT authentication" i get this error:' Cannot generate SSPI context '
Q3 Are your Sql Server(s) set to use integrated, standard, mixed, etc. authentication?

DTS Package error

Hi,
I am using a DTS package to retrieve data from one set of tables /flat files and load into another set of tables. The package is scheduled to run daily. After the failur/success of every step I get an email notification.
But sometimes the package starts, i get the email notification for that ..and then the package never proceeds. Neither I get a failure message nor the success message for the next step. When i checked in the log files I got the attached file, with the error code
8004043B.

This package calls some other packages. But what I feel is that its not completing even the first step.Hence the child packages are not being called.

Please help!!!!Enable logging for this package, so that the error message will be recorded in the log file.

Try setting the "Execute on main package thread" option. Right-click the
task, Workflow Properties, Options tab.|||Are you calling an external VB executable through your DTS ?

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 and ASP.NET

Hi all,

I need to export tables out of a Pervasive DB and into SQL Server 2K. I
have set up a DTS Package to do this when a user visits a web page
(which will then allow them to view a up to date report using MS
Reporting Services).

Currently my DTS package checks to see if the table exists in SQL
Server and then drops it, creates it, and then does the import of data
from Pervasive.

I am wondering if there is a way to get only the new records? There are
currently no PK defined (Pervasive does not make use of them). However,
I noticed that the DTS package can assign PK.

Does anyone have any advice/code snippets?

I greatly appreciate your help.

Thanks,
TonyHi

What do you do if the existing record has been updated? Even if there is no
primary key there should (hopefully!) be a unique way (set of columns) to
identify them, this should be the PK in the SQL Server database.

One method is to load into a staging table then work from there. If you
drive the transformation using a SQL command in the form
SELECT S.Col1, S.Col2, ...
FROM StageTable S
WHERE NOT EXISTS ( SELECT 1 FROM DestinationTable D WHERE D.PK = S.PK )

You will not insert the existing rows. You can also use an additional SQL
Command step before the insert to update existing rows.

UPDATE D
SET Col1 = S.Col1,
Col2 = S.Col2
FROM DestinationTable D
JOIN StageTable S ON D.PK = S.PK
WHERE D.Col1 <> S.Col1
OR D.Col2 <> S.Col2

John

"Tony" <tcarcieri@.rihousing.com> wrote in message
news:1109857630.729309.129570@.l41g2000cwc.googlegr oups.com...
> Hi all,
> I need to export tables out of a Pervasive DB and into SQL Server 2K. I
> have set up a DTS Package to do this when a user visits a web page
> (which will then allow them to view a up to date report using MS
> Reporting Services).
> Currently my DTS package checks to see if the table exists in SQL
> Server and then drops it, creates it, and then does the import of data
> from Pervasive.
> I am wondering if there is a way to get only the new records? There are
> currently no PK defined (Pervasive does not make use of them). However,
> I noticed that the DTS package can assign PK.
> Does anyone have any advice/code snippets?
> I greatly appreciate your help.
> Thanks,
> Tony|||Hello John,

Thanks for the reply.

Is this scenario possible (psuedo code)?

If table exists
1)run update on exisiting data
2)select from pervasive db where not exists
Else
1)create table
2)run select

Your thoughts?

Thanks so much!
Tony

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Tony

I am not sure why you check the table existance, if you are in control
of the site then it should be know to exist or be part of an
installation. As previously stated, you can drive the data population
from a SQL statement, but doing this over your network may mean that
loading into a staging table may be quicker.

A different way to do this would be though a stored procedure and a
linked server.

John

Tony Carcieri wrote:
> Hello John,
> Thanks for the reply.
> Is this scenario possible (psuedo code)?
> If table exists
> 1)run update on exisiting data
> 2)select from pervasive db where not exists
> Else
> 1)create table
> 2)run select
> Your thoughts?
> Thanks so much!
> Tony
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

DTS of data between two tables, same field names but differnt datatypes

I need to migrate data from one sql database to another. The second DB
is a newer version of the "old" database with mostly the same tables
and fieldnames. In order support some reporting queries in the "new"
version I needed to change the datatype of a few fields from varchar to
int(the data stored was integers already as they were lookup tables).

DTS works great except in the cases of about 10 fields which I changed
the datatypes on from varchar to int.

DTS seems to drop the data if the fieldname and datatype are not an
exact match. Is there any way to use DTS and have it copy data from a
field call subsid type varchar to a field call subsid type int?<tdmailbox@.yahoo.com> wrote in message
news:1115388735.044389.231150@.g14g2000cwa.googlegr oups.com...
>I need to migrate data from one sql database to another. The second DB
> is a newer version of the "old" database with mostly the same tables
> and fieldnames. In order support some reporting queries in the "new"
> version I needed to change the datatype of a few fields from varchar to
> int(the data stored was integers already as they were lookup tables).
> DTS works great except in the cases of about 10 fields which I changed
> the datatypes on from varchar to int.
> DTS seems to drop the data if the fieldname and datatype are not an
> exact match. Is there any way to use DTS and have it copy data from a
> field call subsid type varchar to a field call subsid type int?

This should work fine - I tested it quickly on two tables with the same
column name but the source being varchar(10) and the destination int. Using
a Transform Data Task worked correctly, and the mapping was handled
automatically.

I'm not sure what you mean by DTS seems to "drop the data". Perhaps you can
give some more information - your MSSQL version, which type of task you're
using to move the data, the DDL for the tables, any error messages etc.

Simon|||DTS copies all the columns except the ones with datatype changes. My
varchars to ints end up with empty ints in the desitnation table.

SQL 2000, DTS export, no error messages.|||I tested it again with the export wizard (I'd used the package designer
before), and it worked fine. I have no idea why it's not working for
you, unless you have a column transformation of some sort. One other
guess would be that your values are too big for an int, and you've also
set ANSI_WARNINGS OFF, which would ignore the overflow. But since
that's ON for ODBC/OLE DB by default, it's very unlikely.

You could specify a source query instead of a source table and use CAST
to force the conversion:

select col1, col2, cast(col3 as int), col4...
from dbo.SourceTable

Even if that doesn't work, you might get a clue of some sort from an
error. Finally, you could also copy the data into a staging table which
has exactly the same structure as the source table, then use SQL to
check and INSERT the data. Or just INSERT directly, if both databases
are on the same server.

Simon

dts migration

Hi,

How to migrate scheduled jobs from 2000 to 2005. Is this possible by doing bcp out and bcp in system tables which stores sql agent jobs from msdb database ?

Regards

Nit

This question is not related to SSIS. I'd try another forum if I were you. https://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=19&SiteID=1

-Jamie

DTS load to multiple tables

I have SQL Server 2003 Standard and am attempting to use DTS for a data load/transformation and I’m not sure if I am using the right tool for the job.I have a somewhat denormalized Access database that has to be loaded into a normalized SQL Server database.Values from one row in any of the source tables generally need to be separated and inserted into several destination (SQL Server) tables.There are no unique ids in the source data since it is coming from a 3^rd party and the tables are not related to others.I’ve created a DTS Package and have the beginnings of several Transform Data Tasks.Each destination table has an Identity id column, which is calculated automatically.The roadblock I’ve run into is that I can’t figure out how to take each newly created ID and insert it into a new row in another table as a foreign key.Basically, I have to move data from a single input row to new rows in multiple destination tables and create ids (PK, FK) that tie these tables together.The Transform Data Task only allows me to reference one source and one destination, not multiple destinations.I hope this makes sense.Any suggestions would be appreciated.

Here’s a simple example that may help illustrate the problem.My database is much more complex.

Input table is called parcels and each row has three columns:Address, Owner, and Legal_Description.

Output database has two tables that will receive this data:Parcel table will have the Address, Owner and Parcel_ID (auto calculated).Legal table will have Legal_Description, Legal_Desc_ID (auto calculated), and Parcel_ID.

When the row is inserted into the Parcel table, the newly auto calculated Parcel_ID has to be captured.Next create a row into the Legal table and insert the Parcel_ID so the two rows are related.

How can I do this through DTS?Thanks for any suggestions, code snippets, or references.

Due to the complexity of this task, I would suggest maybe doing this in a .NET winforms application. Set up ODBC connections to the two databases. Now write some queries in the Access database to divide the data up appropriately. Next, write insert procedures in the SQL Server database, which return an outparameter which is the id field. To get this, in the insert proc, use the @.@.IDENTITY or the SCOPE_IDENTITY calls to get the id value of the inserted row. Capture this in the .NET application, and pass this in to the insert proc in the related table. Alternatively, keep a cache of the data mappings, maybe in a temporary table or a dataset, and do the inserts as bulk inserts. Then run an update procedure which sets the foreign key based on the database mappings in the original database. So, for example, based on the Legal_Description in the legal table, update the Parcel_Id in the legal table using the owner and address values that used to share a row with the Legal_Description. Either approach should work.

let me know if you need more guidance here. Some of the SQL database engine people, or the SSIS people may be able to point you to a DTS solution that can do this. Alternatively, you could create a DTS solution that inserts into one table, inserts into the other, then calls the update procedure as described above. There is a separate SSIS forum (the new DTS), you may be better posting the question there.

HTH

For more T-SQL tips, check out my blog:

DTS load master file to different tables

We need to load a "master" flat file to SQL Server tables. The file is a dump from mainframe. Based on a field called "record_type", each record in the file has different columns. I would use the following as an example (the real file is much more complicated than this, but you get the idea):

For instance, my file has:

20M02221984PAPepsi1000
23F11121987MD1000
01M09182003TXCocacola1100
34F03041970DC900

If "M", the fields are "age", "gender", "birthdate", "state", "salary"

If "F", the fields are "age", "gender", "birthdate", "state", "company", "salary"

We need to load the file (only one file) into two different tables, M_table, and F_table. But I have researched and discovered in DTS the source (TEXT file) can not be queried against to filter on the gender field.

Since each record may have different number of fields, I cannot really load the flat file into a "staging" table.

Does anyone has any idea on how to achieve this? Thanks in advance!!!It should be:

If "F", the fields are "age", "gender", "birthdate", "state", "salary"

If "M", the fields are "age", "gender", "birthdate", "state", "company", "salary"|||Since your data sets are obviously not fixed-length, you could use a temporary master table into which you import all data sets, e.g. into only one column of varchar(nnn).

Then use queries with string-functions to split the data into the correct number and type of columns for the respective tables M_table and F_table.|||kbk's solution is probably simpler (and therefore usually better). You may also consider a Data Driven Query task. It won't be entirely straightforward and it will require a substantial amount of work in the ActiveX script component (and it's performance will be slower).

But other than these drawbacks, it may work!

Regards,

hmscott