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 + ActiveX (cross)

Hey,
1)
I'm doing a lot of importing (by DTS packages) from commaseparated files
into tables where i empty/truncate the table _before_ import of ALL info in
the file.
BUT - how do I import data from such a file by an UPDATE command...
Meaning if the table has an ID col and a NAME col, and the file has an ID
col and a NAME col - then I would like to be able to UPDATE all NAME-cols in
the table by using the info from the file. The NAME in the file might have
changed, but the ID stays the same... Also, i new ID's are in the file, they
should be INSERTed into the table (+ the NAME).
2)
I have been using ActiveX Data Trans for some imports - but it would be
great if I could perform SQL-taks (like above perhaps...? or is the
another way) - meaning: how do I make SQL call from within an ActiveX task?
Any help appreciated - Thanx!
Best regards
Jakob H. Heidelberg
Denmark> BUT - how do I import data from such a file by an UPDATE command...
> Meaning if the table has an ID col and a NAME col, and the file has an ID
> col and a NAME col - then I would like to be able to UPDATE all NAME-cols
in
> the table by using the info from the file. The NAME in the file might have
> changed, but the ID stays the same... Also, i new ID's are in the file,
they
> should be INSERTed into the table (+ the NAME).
We do this every morning, or more accurately we have a scheduled task that
does this every morning. These are the general steps we use:
Select everything from the CSV file into a temporary table.
Update the rows that need updating in the target from the temp table, then
delete the source rows. You can do updates using joined tables, or create a
cursor and do them one record at a time.
Insert whatever's left in the temp table
Destroy the temp table.
Can't really help you with your other question; I could guess, but I'd
likely be wrong and sound more an idiot than normal. I leave that to the
rest of our colleagues.
William Morris
Product Development, Seritas LLC
Kansas City, Missouri|||Thanx.
can you tell me how to create the TEMP table, and how to INSERT into it?
Also, when will the table "die"?
Thanx in advance
Jakob
""Sokrates"" <somebody@.somewhere.earth> skrev i en meddelelse
news:c29sc1$1rlv$1@.news.cybercity.dk...
> Hey,
> 1)
> I'm doing a lot of importing (by DTS packages) from commaseparated files
> into tables where i empty/truncate the table _before_ import of ALL info
in
> the file.
> BUT - how do I import data from such a file by an UPDATE command...
> Meaning if the table has an ID col and a NAME col, and the file has an ID
> col and a NAME col - then I would like to be able to UPDATE all NAME-cols
in
> the table by using the info from the file. The NAME in the file might have
> changed, but the ID stays the same... Also, i new ID's are in the file,
they
> should be INSERTed into the table (+ the NAME).
> 2)
> I have been using ActiveX Data Trans for some imports - but it would be
> great if I could perform SQL-taks (like above perhaps...? or is the
> another way) - meaning: how do I make SQL call from within an ActiveX
task?
>
> Any help appreciated - Thanx!
> Best regards
> Jakob H. Heidelberg
> Denmark
>
>
>

Import + ActiveX (cross)

Hey,
1)
I'm doing a lot of importing (by DTS packages) from commaseparated files
into tables where i empty/truncate the table _before_ import of ALL info in
the file.
BUT - how do I import data from such a file by an UPDATE command...
Meaning if the table has an ID col and a NAME col, and the file has an ID
col and a NAME col - then I would like to be able to UPDATE all NAME-cols in
the table by using the info from the file. The NAME in the file might have
changed, but the ID stays the same... Also, i new ID's are in the file, they
should be INSERTed into the table (+ the NAME).
2)
I have been using ActiveX Data Trans for some imports - but it would be
great if I could perform SQL-taks (like above perhaps...? or is the
another way) - meaning: how do I make SQL call from within an ActiveX task?
Any help appreciated - Thanx!
Best regards
Jakob H. Heidelberg
Denmark> BUT - how do I import data from such a file by an UPDATE command...
> Meaning if the table has an ID col and a NAME col, and the file has an ID
> col and a NAME col - then I would like to be able to UPDATE all NAME-cols
in
> the table by using the info from the file. The NAME in the file might have
> changed, but the ID stays the same... Also, i new ID's are in the file,
they
> should be INSERTed into the table (+ the NAME).
We do this every morning, or more accurately we have a scheduled task that
does this every morning. These are the general steps we use:
Select everything from the CSV file into a temporary table.
Update the rows that need updating in the target from the temp table, then
delete the source rows. You can do updates using joined tables, or create a
cursor and do them one record at a time.
Insert whatever's left in the temp table
Destroy the temp table.
Can't really help you with your other question; I could guess, but I'd
likely be wrong and sound more an idiot than normal. I leave that to the
rest of our colleagues.
William Morris
Product Development, Seritas LLC
Kansas City, Missouri|||What I will do is create a temporary table in the same
database with the data from the file.
Next, I will write a script to (import) insert from the
temp table to where you want the data to go.
Mary
>--Original Message--
>Hey,
>1)
>I'm doing a lot of importing (by DTS packages) from
commaseparated files
>into tables where i empty/truncate the table _before_
import of ALL info in
>the file.
>BUT - how do I import data from such a file by an UPDATE
command...
>Meaning if the table has an ID col and a NAME col, and
the file has an ID
>col and a NAME col - then I would like to be able to
UPDATE all NAME-cols in
>the table by using the info from the file. The NAME in
the file might have
>changed, but the ID stays the same... Also, i new ID's
are in the file, they
>should be INSERTed into the table (+ the NAME).
>2)
>I have been using ActiveX Data Trans for some imports -
but it would be
>great if I could perform SQL-taks (like above
perhaps...? or is the
>another way) - meaning: how do I make SQL call from
within an ActiveX task?
>
>Any help appreciated - Thanx!
>Best regards
>Jakob H. Heidelberg
>Denmark
>
>
>
>.
>|||You can do this without using Activex. As others
mentioned, you should import the text file into a temp or
staging table, then you have two choices:
1) delete from <main table> where id in (select id from
temptable);
insert into <main table> select * from temptable
2) update <main table> set name = b.name
from <main table> a inner join temptable b
on a.id = b.id;
insert into <main table> select * from temptable b
where not exists (select * from <main table>
where id = b.id
First option is easier and quick to implement but is not
appropriate in all situations. Second option will work in
all situations if you pk, but it could be little more
involved depending on number of columns you have.
My two cents...
>--Original Message--
>Hey,
>1)
>I'm doing a lot of importing (by DTS packages) from
commaseparated files
>into tables where i empty/truncate the table _before_
import of ALL info in
>the file.
>BUT - how do I import data from such a file by an UPDATE
command...
>Meaning if the table has an ID col and a NAME col, and
the file has an ID
>col and a NAME col - then I would like to be able to
UPDATE all NAME-cols in
>the table by using the info from the file. The NAME in
the file might have
>changed, but the ID stays the same... Also, i new ID's
are in the file, they
>should be INSERTed into the table (+ the NAME).
>2)
>I have been using ActiveX Data Trans for some imports -
but it would be
>great if I could perform SQL-taks (like above
perhaps...? or is the
>another way) - meaning: how do I make SQL call from
within an ActiveX task?
>
>Any help appreciated - Thanx!
>Best regards
>Jakob H. Heidelberg
>Denmark
>
>
>
>.
>|||Ah, allright - does somebody have code examples I can use?
Best regards
Jakob
"Mary Lou Friend" <anonymous@.discussions.microsoft.com> skrev i en
meddelelse news:4d9301c402bf$698bdf80$a601280a@.phx.gbl...
> What I will do is create a temporary table in the same
> database with the data from the file.
> Next, I will write a script to (import) insert from the
> temp table to where you want the data to go.
> Mary
> >--Original Message--
> >Hey,
> >
> >1)
> >I'm doing a lot of importing (by DTS packages) from
> commaseparated files
> >into tables where i empty/truncate the table _before_
> import of ALL info in
> >the file.
> >
> >BUT - how do I import data from such a file by an UPDATE
> command...
> >
> >Meaning if the table has an ID col and a NAME col, and
> the file has an ID
> >col and a NAME col - then I would like to be able to
> UPDATE all NAME-cols in
> >the table by using the info from the file. The NAME in
> the file might have
> >changed, but the ID stays the same... Also, i new ID's
> are in the file, they
> >should be INSERTed into the table (+ the NAME).
> >
> >2)
> >I have been using ActiveX Data Trans for some imports -
> but it would be
> >great if I could perform SQL-taks (like above
> perhaps...? or is the
> >another way) - meaning: how do I make SQL call from
> within an ActiveX task?
> >
> >
> >Any help appreciated - Thanx!
> >
> >Best regards
> >
> >Jakob H. Heidelberg
> >Denmark
> >
> >
> >
> >
> >
> >
> >.
> >|||Thanx.
can you tell me how to create the TEMP table, and how to INSERT into it?
Also, when will the table "die"?
Thanx in advance
Jakob
""Sokrates"" <somebody@.somewhere.earth> skrev i en meddelelse
news:c29sc1$1rlv$1@.news.cybercity.dk...
> Hey,
> 1)
> I'm doing a lot of importing (by DTS packages) from commaseparated files
> into tables where i empty/truncate the table _before_ import of ALL info
in
> the file.
> BUT - how do I import data from such a file by an UPDATE command...
> Meaning if the table has an ID col and a NAME col, and the file has an ID
> col and a NAME col - then I would like to be able to UPDATE all NAME-cols
in
> the table by using the info from the file. The NAME in the file might have
> changed, but the ID stays the same... Also, i new ID's are in the file,
they
> should be INSERTed into the table (+ the NAME).
> 2)
> I have been using ActiveX Data Trans for some imports - but it would be
> great if I could perform SQL-taks (like above perhaps...? or is the
> another way) - meaning: how do I make SQL call from within an ActiveX
task?
>
> Any help appreciated - Thanx!
> Best regards
> Jakob H. Heidelberg
> Denmark
>
>
>