Migration of SQL 7.0 package to SQL 2005

Hi,
We are migrating from SQL server 7.0 to SQL server 2005.
In 7.0 I have made a DTS package that does a complex task on pre-scheduled
times, started by a SQL job.
This is a brief description of the complex task:
-Copy files from several computers in the network to a local "working
directory"
-Verify the contents of the files (pre-formatted plain text files)
-Move files with size or content errors to a different directory
-Load the files that have no errors into a SQL server table (tblImport)
-Mark records in tblImport that already exist in tblHistory
-Gather user information about the files that have been succesfully loaded
into tblImport
-Create several reports (plain text files) based on the information in the
files
-Print the reports to different printers
-Add the non-marked records in tblImport to tblHistory
-Delete the non-marked records in tblImport
-Send e-mails to several recipients with statistical information about the
task
This package is made with a number of activex scripts and SQL tasks.
Because the server this package is running on now is outdated, I have the
challenge to make this task work in SQL server 2005.
The database it is using is already transferred to SQL 2005.
I have seen that creating packages in 2005 is totally different than
creating packages in 7.0.
In SQL 7.0 I can save the packages into a .dts file, but in 2005 I can only
load a .dtsx file.
To me it is ok if I have to build the package in 2005 from scratch or build
it in a totally different way, just as long as it is not taking me too much
time. A different way could be that I copy and paste the scripts and SQL
statements into several steps of a 2005 job, with some minor changes, but
I'm not sure if that is a good solution. Especially when something goes
wrong, I would like to know exactly where things went wrong and what steps
have to be executed to finish the job.
Any help or ideas are appreciated!Did you try to migrate this package to SQL 2005. The migration process will
migrate the DTS package into SSIS, and encapsulate all functionality it
can't upgrade into mini DTS 2000 packages which will run as subpackages of
the SSIS Package. You can then at your leisure redesign the mini DTS 2000
packages into your SSIS package.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"a" <a@.b.c> wrote in message news:%23smBc8wMHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We are migrating from SQL server 7.0 to SQL server 2005.
> In 7.0 I have made a DTS package that does a complex task on pre-scheduled
> times, started by a SQL job.
> This is a brief description of the complex task:
> -Copy files from several computers in the network to a local "working
> directory"
> -Verify the contents of the files (pre-formatted plain text files)
> -Move files with size or content errors to a different directory
> -Load the files that have no errors into a SQL server table (tblImport)
> -Mark records in tblImport that already exist in tblHistory
> -Gather user information about the files that have been succesfully loaded
> into tblImport
> -Create several reports (plain text files) based on the information in the
> files
> -Print the reports to different printers
> -Add the non-marked records in tblImport to tblHistory
> -Delete the non-marked records in tblImport
> -Send e-mails to several recipients with statistical information about the
> task
> This package is made with a number of activex scripts and SQL tasks.
> Because the server this package is running on now is outdated, I have the
> challenge to make this task work in SQL server 2005.
> The database it is using is already transferred to SQL 2005.
> I have seen that creating packages in 2005 is totally different than
> creating packages in 7.0.
> In SQL 7.0 I can save the packages into a .dts file, but in 2005 I can
> only
> load a .dtsx file.
> To me it is ok if I have to build the package in 2005 from scratch or
> build
> it in a totally different way, just as long as it is not taking me too
> much
> time. A different way could be that I copy and paste the scripts and SQL
> statements into several steps of a 2005 job, with some minor changes, but
> I'm not sure if that is a good solution. Especially when something goes
> wrong, I would like to know exactly where things went wrong and what steps
> have to be executed to finish the job.
> Any help or ideas are appreciated!
>
>|||Thanks for your response Hilary.
Unfortunately the Migration Wizard is unable to connect to SQL 7.0. It gives
me an error saying "This SQL Server version (7.0) is not supported."
Hilary Cotter <hilary.cotter@.gmail.com> wrote in message
news:O9525GxMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> Did you try to migrate this package to SQL 2005. The migration process
will
> migrate the DTS package into SSIS, and encapsulate all functionality it
> can't upgrade into mini DTS 2000 packages which will run as subpackages of
> the SSIS Package. You can then at your leisure redesign the mini DTS 2000
> packages into your SSIS package.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "a" <a@.b.c> wrote in message
news:%23smBc8wMHHA.4000@.TK2MSFTNGP06.phx.gbl...
pre-scheduled[vbcol=seagreen]
loaded[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
but[vbcol=seagreen]
steps[vbcol=seagreen]
>|||Can you save the dts packages as structured storage and then open them up in
SQL 2000 Package Designed and save them there and then try to use the
migration wizard?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"a" <a@.b.c> wrote in message news:uf19BVxMHHA.5016@.TK2MSFTNGP04.phx.gbl...
> Thanks for your response Hilary.
> Unfortunately the Migration Wizard is unable to connect to SQL 7.0. It
> gives
> me an error saying "This SQL Server version (7.0) is not supported."
>
> Hilary Cotter <hilary.cotter@.gmail.com> wrote in message
> news:O9525GxMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> will
> news:%23smBc8wMHHA.4000@.TK2MSFTNGP06.phx.gbl...
> pre-scheduled
> loaded
> the
> the
> the
> but
> steps
>|||I saved the package as a DTS file and then tried to open it with right
clicking on the Data Transformations Services Folder.
It opened succesfully and an icon was created in DTS folder.
When I right clicked on the icon I had the options to Open, Export, Migrate
or Delete the package.
The Open option showed me a messagebox telling me that the SQL Server 2000
DTS Designer Component need to be installed to edit the package. I
downloaded this designer and installed it. Now I am able to view and edit
the package the way I did in SQL 7.0. But I have not found a way to start
this package on scheduled times with the SQL Jobs. The package can be
started manually from the menu or with the button on the toolbar, so I think
there is a possibillity to start it with an Operating System command.
In SQL 7.0 you can right click a package to schedule it and then an
Operating System command is created to start the package.
The command (for example) then looks like this: (between the dashes)
--
DTSRun /~S 0xDEC29263B482BF0654C6653D6E68736D /~N
0x1787C8739479E8EEE3EDD35BDD12403EAF86F1
19B7E6BB7D71C055D523BCAF9B80C874A881
ADB6E23669940A342D8A5C13A8FCD425B3C8FA1F
469E8326E6839D2B482D22143EC03E /E
--
I don't know exactly what this command does and I also do not know how to
create a similar command in SQL 2005.
Unfortunately in SQL 2005 the option to schedule a package is not available.
I also tried to migrate the package with the Migrate option. It did the
migration job without any errors. So now I can see in the Integration
Services an icon representing the migrated package. But...
When I right click on the icon I do not have an option to view or modify the
package. I can Run the package, but because I cannot check if the migration
is succesfull in my opinion and I can not modify the package, I don't think
I am going to use the migrated package.
So 3 questions are still open:
1 How can schedule the execution of the (original) package? (Probably with
Job running a complex operating System Command, but I don't know how to
create that command)
2 Is there a way to view, modify and schedule the migrated package?
3 How do I create a new package (or something else) that can do similar
tasks like the one I described below. (because there are more packages I
need to migrate to SQL 2005)
Thanks,
John.
Hilary Cotter <hilary.cotter@.gmail.com> wrote in message
news:#ULCMy1MHHA.4912@.TK2MSFTNGP02.phx.gbl...
> Can you save the dts packages as structured storage and then open them up
in
> SQL 2000 Package Designed and save them there and then try to use the
> migration wizard?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "a" <a@.b.c> wrote in message news:uf19BVxMHHA.5016@.TK2MSFTNGP04.phx.gbl...
2000[vbcol=seagreen]
(tblImport)[vbcol=seagreen]
in[vbcol=seagreen]
about[vbcol=seagreen]
can[vbcol=seagreen]
too[vbcol=seagreen]
goes[vbcol=seagreen]
>|||I have found all the answers myself:
1)
Create a job with the following activex script:
Const DTSSQLStgFlag_UseTrustedConnection = 256
Dim dts
Set dts = CreateObject("dts.Package")
dts.LoadFromSQLServer "MyServer", , , DTSSQLStgFlag_UseTrustedConnection, ,
, , "MyPackage"
dts.Execute
Set dts = Nothing
Make sure that logging is enabled in the package and specify an error log
file.
Actually quite simple!
2 and 3)
In the SQL Server Business Intelligence Development Studio you can create an
Integration Services Project.
From the Project menu select 'Add Existing Package'
Specify where the package can be found. Click Ok. Now the icon of the
package appears in the Solution Explorer.
Right click on the icon of the package and select 'View designer.'
In the designer you can find all the SQL 7.0 options (and much more!) to
edit packages.
a <a@.b.c> wrote in message news:OkVNdF9MHHA.4992@.TK2MSFTNGP04.phx.gbl...
> I saved the package as a DTS file and then tried to open it with right
> clicking on the Data Transformations Services Folder.
> It opened succesfully and an icon was created in DTS folder.
> When I right clicked on the icon I had the options to Open, Export,
Migrate
> or Delete the package.
> The Open option showed me a messagebox telling me that the SQL Server 2000
> DTS Designer Component need to be installed to edit the package. I
> downloaded this designer and installed it. Now I am able to view and edit
> the package the way I did in SQL 7.0. But I have not found a way to start
> this package on scheduled times with the SQL Jobs. The package can be
> started manually from the menu or with the button on the toolbar, so I
think
> there is a possibillity to start it with an Operating System command.
> In SQL 7.0 you can right click a package to schedule it and then an
> Operating System command is created to start the package.
> The command (for example) then looks like this: (between the dashes)
> --
> DTSRun /~S 0xDEC29263B482BF0654C6653D6E68736D /~N
>
0x1787C8739479E8EEE3EDD35BDD12403EAF86F1
19B7E6BB7D71C055D523BCAF9B80C874A881[vbc
ol=seagreen]
> ADB6E23669940A342D8A5C13A8FCD425B3C8FA1F
469E8326E6839D2B482D22143EC03E /E
> --
> I don't know exactly what this command does and I also do not know how to
> create a similar command in SQL 2005.
> Unfortunately in SQL 2005 the option to schedule a package is not[/vbcol]
available.
> I also tried to migrate the package with the Migrate option. It did the
> migration job without any errors. So now I can see in the Integration
> Services an icon representing the migrated package. But...
> When I right click on the icon I do not have an option to view or modify
the
> package. I can Run the package, but because I cannot check if the
migration
> is succesfull in my opinion and I can not modify the package, I don't
think
> I am going to use the migrated package.
> So 3 questions are still open:
> 1 How can schedule the execution of the (original) package? (Probably with
> Job running a complex operating System Command, but I don't know how to
> create that command)
> 2 Is there a way to view, modify and schedule the migrated package?
> 3 How do I create a new package (or something else) that can do similar
> tasks like the one I described below. (because there are more packages I
> need to migrate to SQL 2005)
> Thanks,
> John.
>
> Hilary Cotter <hilary.cotter@.gmail.com> wrote in message
> news:#ULCMy1MHHA.4912@.TK2MSFTNGP02.phx.gbl...
up[vbcol=seagreen]
> in
news:uf19BVxMHHA.5016@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
process[vbcol=seagreen]
it[vbcol=seagreen]
subpackages[vbcol=seagreen]
> 2000
"working[vbcol=seagreen]
> (tblImport)
> in
> about
tasks.[vbcol=seagreen]
have[vbcol=seagreen]
than[vbcol=seagreen]
> can
or[vbcol=seagreen]
> too
and[vbcol=seagreen]
changes,[vbcol=seagreen]
> goes
what[vbcol=seagreen]
>