Import Legacy DTS Packages into SQL Server 2005 using SMO

I was hoping someone can point me to a

resource for SMO and importing legacy DTS packages from SQL Server

2000.

We are getting ready to upgrade our

SQL Servers from 2000 to 2005. We have a lot of DTS packages that we plan on

continuing to use in 2005. I found a script I used to export all of the old DTS

packages out of our 2000 servers;

DECLARE @.TARGETDIR varchar(1000)

SET @.TARGETDIR = 'C:\DTSTest\'

SELECT distinct

'DTSRUN.EXE /S '

+ CONVERT(varchar(200), SERVERPROPERTY('servername'))

+ '

/E '

+ '

/N '

+ '"' + name + '"'

+ '

/F '

+ '"' + @.TARGETDIR + name + '.dts"'

+ '

/!X'

FROM msdb.dbo.sysdtspackages P

Now I need to write a script to

import them into 2005.

I have been reading that I should be

using SMO to do my database scripting in 2005. As I have been going through the

libraries, almost everything seems to be geared for importing SSIS packages, and

not legacy DTS packages.

Does anyone know of someone or some

resource that might be able to help me out.

Thank

you,

dfpelican

Our developers here found that there were sufficient problems in upgrading DTS packages that it was worth rebuilding the packages in SSIS directly, taking advantage of the new control flow processes in SSIS.|||

+1

I can second that from my experience.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Migration business is not good. Just buy the new stuff and redo. Actually that is what the software vendor really wants you to do.|||

did you have any luck with your DTS upgrading?

or did you rewrite?, it sounded like you had a lot of them to upgrade

or i guess you would have already re-written instead.

I'm in a similar boat with out flagship salon software product.

Any spockish tips would be appreciated.

Craig Kelly-Soens

http://www.salonsoftwaresystem.com

Import Legacy DTS Packages into SQL Server 2005 using SMO

I was hoping someone can point me to a resource for SMO and importing legacy DTS packages from SQL Server 2000.

We are getting ready to upgrade our SQL Servers from 2000 to 2005. We have a lot of DTS packages that we plan on continuing to use in 2005. I found a script I used to export all of the old DTS packages out of our 2000 servers;

DECLARE @.TARGETDIR varchar(1000)

SET @.TARGETDIR = 'C:\DTSTest\'

SELECT distinct

'DTSRUN.EXE /S '

+ CONVERT(varchar(200), SERVERPROPERTY('servername'))

+ ' /E '

+ ' /N '

+ '"' + name + '"'

+ ' /F '

+ '"' + @.TARGETDIR + name + '.dts"'

+ ' /!X'

FROM msdb.dbo.sysdtspackages P

Now I need to write a script to import them into 2005.

I have been reading that I should be using SMO to do my database scripting in 2005. As I have been going through the libraries, almost everything seems to be geared for importing SSIS packages, and not legacy DTS packages.

Does anyone know of someone or some resource that might be able to help me out.

Thank you,

dfpelican

Our developers here found that there were sufficient problems in upgrading DTS packages that it was worth rebuilding the packages in SSIS directly, taking advantage of the new control flow processes in SSIS.|||

+1

I can second that from my experience.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Migration business is not good. Just buy the new stuff and redo. Actually that is what the software vendor really wants you to do.|||

did you have any luck with your DTS upgrading?

or did you rewrite?, it sounded like you had a lot of them to upgrade

or i guess you would have already re-written instead.

I'm in a similar boat with out flagship salon software product.

Any spockish tips would be appreciated.

Craig Kelly-Soens

http://www.salonsoftwaresystem.com

sql

Import from 2005 into 2000

Hi, we have a 2005 database which we want to export information from
back into a legacy 2000 database.
What is the best way to do this?
Thanks
CatherineOn Jan 29, 8:44=A0am, cather...@.myoddjobs.info wrote:
> Hi, we have a 2005 database which we want to export information from
> back into a legacy 2000 database.
> What is the best way to do this?
> Thanks
> Catherine
If the compatibility level is set to 2000 then I don't see any problem
here. you can use DTS and all...
Thanks
Ajay|||It depends. To transfer data only once it is easy to use the Export and
Import Wizard. To transfer data periodically you could create SSIS packages.
Or you can use SQL Server replication.
Hope this helps,
Ben Nevarez
"catherine@.myoddjobs.info" wrote:
> Hi, we have a 2005 database which we want to export information from
> back into a legacy 2000 database.
> What is the best way to do this?
> Thanks
> Catherine
>

Import ASCII Data

I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the data
into tables.

What I would like to do is create an automated import function in SQL.

I am new to SQL, can anyone point me in the direction I should look to find
out how I could perform this task?

Using SQL 2005.

Thanks
Matt

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via http://www.sqlmonster.commattc66 via SQLMonster.com (u16013@.uwe) writes:

Quote:

Originally Posted by

I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the
data into tables.
>
What I would like to do is create an automated import function in SQL.
>
I am new to SQL, can anyone point me in the direction I should look to
find out how I could perform this task?
>
Using SQL 2005.

There are a couple of alternatives. There is BCP (command-line tool)
and BULK INSERT (T-SQL statement) which work very similarily. Their
good as long as the files have one entry for each file in each record,
and there are no headers.

You can use the Import Wizard in SQL Server Management Studio. As with
all graphical tools, it's good for a one-off, but it's really a good
place if you need to do this on a regular basis. The Import Wizard requires
that SSIS (see below) is installed.

And then there is SQL Server Integration Service (SSIS), the member of the
SQL Server family that is all about importing and exporting data and
transforming it on the way. I have not used SSIS or its predecessor
myself, so I don't really know what it's so fantastic. (Being an old-
timer, I get by very well with BCP and BULK INSERT.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||To add to Erland's response, you can use a SQL Agent job to schedule the
import process. In the case of a package created with SSMS or the BI dev
studio, there is a specialized SQL Server Integration Services step type
that allows you to specify the desired package and run time settings via a
GUI.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"mattc66 via SQLMonster.com" <u16013@.uwewrote in message
news:65826d5d02090@.uwe...

Quote:

Originally Posted by

>I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the
data
into tables.
>
What I would like to do is create an automated import function in SQL.
>
I am new to SQL, can anyone point me in the direction I should look to
find
out how I could perform this task?
>
Using SQL 2005.
>
Thanks
Matt
>
--
Matt Campbell
mattc (at) saunatec [dot] com
>
Message posted via http://www.sqlmonster.com
>
>