DTS problem, please help!

I have yet another weired problem for which I have no answer.

I have a DTS process setup that needs to fireup at night, which uploads data from text file into upload table.
It runs perfact.

The problem started when I tried to schedule it and then reschedule it for another timeperiod.

I reschduled this process for about 3 times for different timeperiod. Meaning I had originally set it to fire at 1:00 am then later changed to 2:00 and then to 3:00.

Technically, it should overwrite the last schedule time and fire at the new scheduled time, ie. 3:00 am daily.

But somehow DTS is keeping the history of all the previous scheduled times and is now firing at 1:00 am, 2:00 am and also at 3:00 am.

I also looked at the Versions option in that DTS process's right click menu and removed all, but the latest version. But still the same behaviour.

Any help on this is much appreciated.

Many Thanks!Did you actually change the SQL Agent job or did you just right-click the DTS package and schedule it? Check the jobs in SQL Agent to see if there are three of them in there.

DTS packages not migrating completely

Hi, I upgraded several of my SQL 2000 database to SQL 2005 databases. I'm in the process of migrating over the DTS packages over to SSIS packages. I'm using the DTSMigrationWizard utilty to do this migration. When I started testing the SSIS packages I've discovered that not all the steps were being excuted. So I've noticed on several of the packages not everything migrated over. Such as connections to oracle and some of the task as well did not migrate over. Is there a way to migrate the entire DTS package to SSIS or do I have to re-create the entire package from scratch or is there a better way to get the SQL 2000 DTS packages over to SQL 2005 and have them run successfully?

You can have the SQL 2000 DTS packages in a SQL 2005 server and still keep running them (using the DTS runtime). If you chose to migrate, please be aware its a best-efforts migration as DTS and SSIS are different products. See http://technet.microsoft.com/en-us/library/ms143462.aspx for more details on known issues during migration.

DTS packages not migrating completely

Hi, I upgraded several of my SQL 2000 database to SQL 2005 databases. I'm in the process of migrating over the DTS packages over to SSIS packages. I'm using the DTSMigrationWizard utilty to do this migration. When I started testing the SSIS packages I've discovered that not all the steps were being excuted. So I've noticed on several of the packages not everything migrated over. Such as connections to oracle and some of the task as well did not migrate over. Is there a way to migrate the entire DTS package to SSIS or do I have to re-create the entire package from scratch or is there a better way to get the SQL 2000 DTS packages over to SQL 2005 and have them run successfully?

You can have the SQL 2000 DTS packages in a SQL 2005 server and still keep running them (using the DTS runtime). If you chose to migrate, please be aware its a best-efforts migration as DTS and SSIS are different products. See http://technet.microsoft.com/en-us/library/ms143462.aspx for more details on known issues during migration.sqlsql

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 to import a csv file on a web server

Im trying to schedule a dts package to import a database table from a csv
file.
In enterprise manager when i attempt to create the dts process, im not sure
what datasource should i be using to connect to my csv file that resides on
the web server?
where do i specify me server to connect to; eg: www.myserver.com\dbRight-click Local Packages and select New Package, Look on Connection and
select Text File (Source). Specify the file name location on the network.
Perhaps you will need to create a shared folder on your web server and give
permissions to the SQL Server service account.
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"bj" wrote:

> Im trying to schedule a dts package to import a database table from a csv
> file.
> In enterprise manager when i attempt to create the dts process, im not sur
e
> what datasource should i be using to connect to my csv file that resides o
n
> the web server?
> where do i specify me server to connect to; eg: www.myserver.com\db
>
>|||thanks ben
I think my biggest problem is working out what my UNC path to my web server
is to locate my text file
should it look something like this
\\syd2004\www.bradyaust.com.au\db\
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:118D97ED-21FC-4538-8670-53C25A1CF867@.microsoft.com...
> Right-click Local Packages and select New Package, Look on Connection and
> select Text File (Source). Specify the file name location on the network.
> Perhaps you will need to create a shared folder on your web server and
> give
> permissions to the SQL Server service account.
> Hope this helps,
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "bj" wrote:
>|||Go to your web server, select the folder containing the text file,
right-click and select Sharing and Security, Share this folder, give
Permissions (very careful here), maybe remove permissions to Everyone, Add
the SQL Server service account, etc.
Let us suppose you shared your folder as MyData, then SQL Server would be
able to access the file using \\webservername\MyData\MyTextFile.
Again, pay special attention to the permissions you give to the shared folde
r.
Ben Nevarez, MCDBA, OCP
Database Administrator
"bj" wrote:

> thanks ben
> I think my biggest problem is working out what my UNC path to my web serve
r
> is to locate my text file
> should it look something like this
> \\syd2004\www.bradyaust.com.au\db\
>
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:118D97ED-21FC-4538-8670-53C25A1CF867@.microsoft.com...
>
>

DTS package - Loop?

Is there a way to have a loop in a DTS package? Or at least to create a start and an end points? I have this process that I put in a DTS package and the process needs to be executed 13 times (involving the creation of different files based on a list of customers) and everything seems to work when I run it once but if I try to insert an "on success" arrow from the last process back to the begining it starts picking a different start point when I rerun it :( Does this make sense?
Any help would be greatly appreciated ;)
--mikeI'm not positive that this will work, but you can try it:

1. Add a global variable (package properties) called LoopCounter
2. Add an ActiveX script (call it "LoopChecker") BEFORE the DTS task and use it to check the value of LoopCounter. If greater than 13, then fail the task; if less than 13 then succeed the task.
3. Link this new ActiveX script to your existing DTS work path using the Success workflow.
4. Add a second ActiveX script to serve as the terminator for the failed "LoopChecker" task
5. Add a third ActiveX script (called "Loop Increment") to increment the LoopCounter variable AFTER the existing DTS task. Set it's workflow to point to "LoopChecker" on success (or even on completion).

You can probably figure out a way to eliminate one of the ActiveX scripts and combine features.

Best of luck, let us know what works.

Regards,

Hugh Scott

Originally posted by Mickael
Is there a way to have a loop in a DTS package? Or at least to create a start and an end points? I have this process that I put in a DTS package and the process needs to be executed 13 times (involving the creation of different files based on a list of customers) and everything seems to work when I run it once but if I try to insert an "on success" arrow from the last process back to the begining it starts picking a different start point when I rerun it :( Does this make sense?
Any help would be greatly appreciated ;)
--mike|||Thanks for the help. I actually found a solution by using the following activex script

IF DTSGlobalVariables("Counter").Value <= DTSGlobalVariables("MaxCount").Value THEN
Set oPkg = DTSGlobalVariables.Parent
oPkg.Steps("DTSStep_DTSDataPumpTask_1").ExecutionStatus = DTSStepExecStat_Waiting
Main = DTSStepScriptResult_DontExecuteTask
ELSE
Main = DTSStepScriptResult_ExecuteTask
END IF

so if the 'IF' statement is true then it reset the first step of my process to wait status and therefore starts again :)

Thanks again,
Mike

DTS package - FTP Download thru proxy

Hi,

I'm trying to set up an FTP process in a DTS package to download a file from an external site. There is nowhere to configure an HTTP proxy. It is getting blocked. I do have the internet options set up correctly but I guess it does not use them.

How do I get around this?

ThanksPlease ref. http://www.sqlteam.com/item.asp?ItemID=12408|||this isn't really what i need

i have to access an http proxy through activex code or somthing

can you show me where that is, this tutorial is for dealing with variable filenames, which i already know how to do

DTS Migration to SSIS

Good Day,

I am currently in the process of migrating my DTS packages to SSISone at a timeand have come accross the following error during the excecution of a "Copy SQL Server Objects Task". The exact error is:

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Version80 database compatibility level is not supported.".

What I am doing with this particular task is to copy a table(s) from another SQL Server, to the current SQL Server. The original location is a SQL2000 DB table. In the near future the source server for this table will be a SQL 2005 server but not until the current migration has been completed.

I have looked at all of the properties, but can not find anything relating to the db compatibilty level. Is there an easier way? Or a setting that I can change to accomplish what I am trying?

Thanks for any help/suggestions.

I have the same error . I'm copying from SQL 2005 to SQL 2005, and selecting all tables. Also having it copy all indexes, triggers, etc. Sometimes I can actually get it to execute successfully, however; there are no tables that have been copied when this happens. I'm betting this is another bug.

Really need this problem resolved since there already is a bug that have been recently report using the Transfer Database Object. Especially if we need to move a SQL 2005 database back to 2000, which my the situation in a case for me the other day. The only way was to export all the tables and then go back into the 2000 db and re-create the indexes, and relationships - not good.

We need the SSIS tools working correctly - DBA's rely on them!.

Hoping for quick resolutions.

Update: There is a solution. Check the Properties\Options of the database and look at the compatibility level. Most likely it is set to 8.0 as was mine. Change to 9.0 and it should now run. - Unfortunately when I ran mine, it led me to believe that everthing ran fine, but when I looked at the database, no user tables, etc. were there.

|||Try changing the provider to Microsoft OLE DB Provider for SQL server. The default provider probably does not work for SQL server 2000.

DTS Migration to SSIS

Good Day,

I am currently in the process of migrating my DTS packages to SSISone at a timeand have come accross the following error during the excecution of a "Copy SQL Server Objects Task". The exact error is:

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Version80 database compatibility level is not supported.".

What I am doing with this particular task is to copy a table(s) from another SQL Server, to the current SQL Server. The original location is a SQL2000 DB table. In the near future the source server for this table will be a SQL 2005 server but not until the current migration has been completed.

I have looked at all of the properties, but can not find anything relating to the db compatibilty level. Is there an easier way? Or a setting that I can change to accomplish what I am trying?

Thanks for any help/suggestions.

I have the same error . I'm copying from SQL 2005 to SQL 2005, and selecting all tables. Also having it copy all indexes, triggers, etc. Sometimes I can actually get it to execute successfully, however; there are no tables that have been copied when this happens. I'm betting this is another bug.

Really need this problem resolved since there already is a bug that have been recently report using the Transfer Database Object. Especially if we need to move a SQL 2005 database back to 2000, which my the situation in a case for me the other day. The only way was to export all the tables and then go back into the 2000 db and re-create the indexes, and relationships - not good.

We need the SSIS tools working correctly - DBA's rely on them!.

Hoping for quick resolutions.

Update: There is a solution. Check the Properties\Options of the database and look at the compatibility level. Most likely it is set to 8.0 as was mine. Change to 9.0 and it should now run. - Unfortunately when I ran mine, it led me to believe that everthing ran fine, but when I looked at the database, no user tables, etc. were there.

|||Try changing the provider to Microsoft OLE DB Provider for SQL server. The default provider probably does not work for SQL server 2000.

DTS Migration to SSIS

Good Day,

I am currently in the process of migrating my DTS packages to SSISone at a timeand have come accross the following error during the excecution of a "Copy SQL Server Objects Task". The exact error is:

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Version80 database compatibility level is not supported.".

What I am doing with this particular task is to copy a table(s) from another SQL Server, to the current SQL Server. The original location is a SQL2000 DB table. In the near future the source server for this table will be a SQL 2005 server but not until the current migration has been completed.

I have looked at all of the properties, but can not find anything relating to the db compatibilty level. Is there an easier way? Or a setting that I can change to accomplish what I am trying?

Thanks for any help/suggestions.

I have the same error . I'm copying from SQL 2005 to SQL 2005, and selecting all tables. Also having it copy all indexes, triggers, etc. Sometimes I can actually get it to execute successfully, however; there are no tables that have been copied when this happens. I'm betting this is another bug.

Really need this problem resolved since there already is a bug that have been recently report using the Transfer Database Object. Especially if we need to move a SQL 2005 database back to 2000, which my the situation in a case for me the other day. The only way was to export all the tables and then go back into the 2000 db and re-create the indexes, and relationships - not good.

We need the SSIS tools working correctly - DBA's rely on them!.

Hoping for quick resolutions.

Update: There is a solution. Check the Properties\Options of the database and look at the compatibility level. Most likely it is set to 8.0 as was mine. Change to 9.0 and it should now run. - Unfortunately when I ran mine, it led me to believe that everthing ran fine, but when I looked at the database, no user tables, etc. were there.

|||Try changing the provider to Microsoft OLE DB Provider for SQL server. The default provider probably does not work for SQL server 2000.

DTS Legacy and SQL 2005

I am in the process of migrating to a new SQL 2005 server. I have a number of DTS packages on my SQL2000 server, approximately 200, that are used on a daily basis. I used the migration wizard to migrate the packages from the 2000 server to the new 2005 server however there are issues with the way some were brought over. I would like to have all of the packages moved from the 2000 to the 2005 server and appear under Legacy DTS so that I can run them as 2000 DTS packages unitl I have a chance to correct the issues.

Here is where my question lies. The migration wizard migrates upgrades all of the packages. How do I move them from the one server to the other and perserve their 2000 DTS format? The servers are on 2 separate boxes with different instance names. Everything I've read tells you how to run the legacy packages but nothing seems to explain on to move them.

Any help would be appreciated

Pete

As you have found the migration wizard attempts to create new SSIS 2005 packages from your DTS packages. Due to the fundamentally different architecture this can be limited in its success.

Moving your DTS 2000 packages to a new server, should be effectively the same as moving DTS packages between 2000 servers, so the following information may still be valid: http://www.sqldts.com/default.aspx?204

However, note that I have not tested this scenario - it is only a suggestion and would need some testing and experimentation.

Donald

|||

Maybe not very polite, but what the heck! why you want migrate them to Sql25k instead of make them from the beginning taking advantatge of all that native stuff?

We've got around 500 and most of them on-daily basis. We have come to the conclusion that left the old ones inside a rubbish.

|||

Although, of course we're talking about bank sector...Thing going here slower

I suppose that if you have mainly DTS packages with the usual tasks (pumping, sql tasks, vbScript and nothing else) migration could be successful from Sql25k otherwise if you have DTS with custom tasks it might not easier.

DTS in SQL Server 2005

Hi,
I'm in the process of migrating several systems from SQL Server 2000 to SQL
Server 2005.
On the existing SQL Server 2000 server, I have several DTS packages, one of
which uses the Copy Database feature to synchronise data on the live box
with my development box. This is an ad-hoc function e.g. a user reports an
error, so I use the DTS package to copy the live data from the particular
database in question down to my development machine so that I can compare
"like for like", if you see what I mean. Often, errors like this are
data-specific, so I need to see exactly what the user is seeing.
This DTS package contained as many Copy Database steps as there are
databases on the server(s). This means that I could open the package and
execute one or more of them as required.
I migrated this (and the other) DTS packages from SQL Server 2000 into SQL
Server 2005 without error. In SQL Server 2005 Management Studio when I
connect to Integration Services and open up Stored Packages\MSDB\DEV, all of
the packages are there. However, when I open the one in question, I don't
appear to see the individual Copy Database components so that I can run them
individually.
I tried to use the SQL Server 2005 Copy Database Wizard, but was unable to
because the live server is actually hosted by my ISP. This means that the
Copy Database Wizard errors when it tries to iterate through the various
databases on my ISP's SQL Server box, because the first one doesn't belong
to me.
Is this not the way SQL Server 2005 does things? Am I going about this the
wrong way?
Any assistance gratefully received.
Mark"Mark Rae" <mark@.markN-O-S-P-A-M.co.uk> wrote in message
news:uACp2i4NGHA.2816@.TK2MSFTNGP15.phx.gbl...
No-one got any ideas...?

DTS in SQL Server 2005

Hi,
I'm in the process of migrating several systems from SQL Server 2000 to SQL
Server 2005.
On the existing SQL Server 2000 server, I have several DTS packages, one of
which uses the Copy Database feature to synchronise data on the live box
with my development box. This is an ad-hoc function e.g. a user reports an
error, so I use the DTS package to copy the live data from the particular
database in question down to my development machine so that I can compare
"like for like", if you see what I mean. Often, errors like this are
data-specific, so I need to see exactly what the user is seeing.
This DTS package contained as many Copy Database steps as there are
databases on the server(s). This means that I could open the package and
execute one or more of them as required.
I migrated this (and the other) DTS packages from SQL Server 2000 into SQL
Server 2005 without error. In SQL Server 2005 Management Studio when I
connect to Integration Services and open up Stored Packages\MSDB\DEV, all of
the packages are there. However, when I open the one in question, I don't
appear to see the individual Copy Database components so that I can run them
individually.
Is this not the way SQL Server 2005 does things? Am I going about this the
wrong way?
Any assistance gratefully received.
Mark"Mark Rae" <mark@.markN-O-S-P-A-M.co.uk> wrote in message
news:%23z5LgPsNGHA.2920@.TK2MSFTNGP10.phx.gbl...
No-one got any ideas...?

DTS in SQL Server 2005

Hi,
I'm in the process of migrating several systems from SQL Server 2000 to SQL
Server 2005.
On the existing SQL Server 2000 server, I have several DTS packages, one of
which uses the Copy Database feature to synchronise data on the live box
with my development box. This is an ad-hoc function e.g. a user reports an
error, so I use the DTS package to copy the live data from the particular
database in question down to my development machine so that I can compare
"like for like", if you see what I mean. Often, errors like this are
data-specific, so I need to see exactly what the user is seeing.
This DTS package contained as many Copy Database steps as there are
databases on the server(s). This means that I could open the package and
execute one or more of them as required.
I migrated this (and the other) DTS packages from SQL Server 2000 into SQL
Server 2005 without error. In SQL Server 2005 Management Studio when I
connect to Integration Services and open up Stored Packages\MSDB\DEV, all of
the packages are there. However, when I open the one in question, I don't
appear to see the individual Copy Database components so that I can run them
individually.
I tried to use the SQL Server 2005 Copy Database Wizard, but was unable to
because the live server is actually hosted by my ISP. This means that the
Copy Database Wizard errors when it tries to iterate through the various
databases on my ISP's SQL Server box, because the first one doesn't belong
to me.
Is this not the way SQL Server 2005 does things? Am I going about this the
wrong way?
Any assistance gratefully received.
Mark
"Mark Rae" <mark@.markN-O-S-P-A-M.co.uk> wrote in message
news:uACp2i4NGHA.2816@.TK2MSFTNGP15.phx.gbl...
No-one got any ideas...?

DTS in SQL Server 2005

Hi,
I'm in the process of migrating several systems from SQL Server 2000 to SQL
Server 2005.
On the existing SQL Server 2000 server, I have several DTS packages, one of
which uses the Copy Database feature to synchronise data on the live box
with my development box. This is an ad-hoc function e.g. a user reports an
error, so I use the DTS package to copy the live data from the particular
database in question down to my development machine so that I can compare
"like for like", if you see what I mean. Often, errors like this are
data-specific, so I need to see exactly what the user is seeing.
This DTS package contained as many Copy Database steps as there are
databases on the server(s). This means that I could open the package and
execute one or more of them as required.
I migrated this (and the other) DTS packages from SQL Server 2000 into SQL
Server 2005 without error. In SQL Server 2005 Management Studio when I
connect to Integration Services and open up Stored Packages\MSDB\DEV, all of
the packages are there. However, when I open the one in question, I don't
appear to see the individual Copy Database components so that I can run them
individually.
I tried to use the SQL Server 2005 Copy Database Wizard, but was unable to
because the live server is actually hosted by my ISP. This means that the
Copy Database Wizard errors when it tries to iterate through the various
databases on my ISP's SQL Server box, because the first one doesn't belong
to me.
Is this not the way SQL Server 2005 does things? Am I going about this the
wrong way?
Any assistance gratefully received.
Mark"Mark Rae" <mark@.markN-O-S-P-A-M.co.uk> wrote in message
news:uACp2i4NGHA.2816@.TK2MSFTNGP15.phx.gbl...
No-one got any ideas...?

DTS in parallel

I have a DTS package I created that is dadabase driven to process source data from multiple sources.

Can I run several instances of this package in parallel for each source or would I need to create a copy for each source ?

JavaWaba

Hello,

Firstly, you can execute DTS packages in parallel by creating a new DTS package and and creating several Execute DTS Package tasks (each of these tasks can be created from the same DTS package). You can then specify the number of these tasks (DTS Packages in this case) to execute in parallel in the package properties (set to 4 by default).

You can then set the data source of each of these packages (now just tasks within a parent dts package) either via global variables, dynamic properties or similar.

Cheers,

Rob

DTS import does not import all rows / records

Hi,

I am having trouble importing data from an excel spreadsheet into MS
SQL Server 2000 using DTS Wizard. The DTS import process is
successfull, no errors, but only 50 rows of approx. 1500 rows of data
are imported. I tried to remove 20 rows in the excel spreadsheet in
the interval row 0-50. When i later ran the import, only 30 rows were
imported. I deleted almost every row in the interval 0-50, with the
result of the import having 0 rows imported (but job ran
successfully). I decided to delete rows 0-100 in the spreadsheet in
order to see if the resolved the problem, but it didn't. As I
suspected something in the excel file to be the cause, I exported the
excel spreadsheeet to a tab delimited textfile, with only one row. A
DTS import resulted in importing approx 100 rows, double the amount of
the textfile, but the other 1400 rows were not imported. The data in
the column is containing numeric values only.

Please help me! What could possibly be the cause of DTS skipping rows
like that. DTS doesn't feel reliable at all :/

Regards,
BjrnYou may want to check your spreadsheet to see if it has a named range
and if that is what you are using in DTS. If the named range only
includes rows 1-50 then DTS will only import those rows.

I'm not sure what you were doing with the text file, since you said
that you exported one row and it imported 100.

HTH,
-Tom.|||Maybe it would be a better idea to avoid the DTS Wizard. Make a local
package on your server and you will have much more control over it.
And if an error occures, DTS will tell you what kind of error that is.

Elaksomfan wrote:
> Hi,
> I am having trouble importing data from an excel spreadsheet into MS
> SQL Server 2000 using DTS Wizard. The DTS import process is
> successfull, no errors, but only 50 rows of approx. 1500 rows of data
> are imported. I tried to remove 20 rows in the excel spreadsheet in
> the interval row 0-50. When i later ran the import, only 30 rows were
> imported. I deleted almost every row in the interval 0-50, with the
> result of the import having 0 rows imported (but job ran
> successfully). I decided to delete rows 0-100 in the spreadsheet in
> order to see if the resolved the problem, but it didn't. As I
> suspected something in the excel file to be the cause, I exported the
> excel spreadsheeet to a tab delimited textfile, with only one row. A
> DTS import resulted in importing approx 100 rows, double the amount of
> the textfile, but the other 1400 rows were not imported. The data in
> the column is containing numeric values only.
> Please help me! What could possibly be the cause of DTS skipping rows
> like that. DTS doesn't feel reliable at all :/
> Regards,
> Bjrn|||First save the excel sheet in csv format.
Then use DTS to import form the csv file.

Tawfiq.

Elaksomfan wrote:
> Hi,
> I am having trouble importing data from an excel spreadsheet into MS
> SQL Server 2000 using DTS Wizard. The DTS import process is
> successfull, no errors, but only 50 rows of approx. 1500 rows of data
> are imported. I tried to remove 20 rows in the excel spreadsheet in
> the interval row 0-50. When i later ran the import, only 30 rows were
> imported. I deleted almost every row in the interval 0-50, with the
> result of the import having 0 rows imported (but job ran
> successfully). I decided to delete rows 0-100 in the spreadsheet in
> order to see if the resolved the problem, but it didn't. As I
> suspected something in the excel file to be the cause, I exported the
> excel spreadsheeet to a tab delimited textfile, with only one row. A
> DTS import resulted in importing approx 100 rows, double the amount
of
> the textfile, but the other 1400 rows were not imported. The data in
> the column is containing numeric values only.
> Please help me! What could possibly be the cause of DTS skipping rows
> like that. DTS doesn't feel reliable at all :/
>
> Regards,
> Bjrn