Hey all,
I have an operator who needs to be able to run a dts package, but I do not want to give them Enetrprise Manager. Is it possible to just install the objects needed to run DTS?Give them osql.exe, create a bat that executes the package...
make sure you can connect and execute with something other than sa, because you'll have to hard code the is and pwd...|||You can write a little vb app to execute the package or just schedule it.|||Look at dtsrun.exe or dtsrunui.exe.|||Originally posted by rnealejr
Look at dtsrun.exe or dtsrunui.exe.
No kidding...
Good one for the memory banks...
I avoid DTS for the most part...
Showing posts with label package. Show all posts
Showing posts with label package. Show all posts
Friday, March 23, 2012
minimum price on earliest date
Ok my first posted question :
(This is related to a travel website)
I have the following table layout :
CREATE TABLE "public"."package" (
"id" BIGINT NOT NULL,
"accom_code" VARCHAR(4) NOT NULL,
"start_date" DATE NOT NULL,
"end_date" DATE NOT NULL,
"pricing_type" VARCHAR(2),
"indic_price" NUMERIC(7,2),
"unit_price" NUMERIC(7,2),
"adult_age_max_cnt" INTEGER,
CONSTRAINT "package_pkey" PRIMARY KEY("id")
) WITH OIDS;
The package table contains a list (a very large one) for holiday accomodation packages.
What i'm trying to get is the following :
The MINIMUM price using the following for "price" :
CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.unit_price
END AS NUMERIC(7,2))
AND
The minimum date (i.e. nearest start date) greater than today
WHERE the start_date equals the minimum start_date and the price equals the minimum price.
Any thoughts?
With any luck I will be able to give you an test data insert for this.
This should probably be posted in the POSTGRESQL section however I feel that it is a more general SQL question than anything else.What I have thus far :
SELECT p.accom_code
,MIN(CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.indic_price
END AS NUMERIC(7,2))) as min_price
,MIN(p.start_date) as min_start
FROM package p
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code
At the present time i'm getting 10 records (as expected). However I believe these records are WRONG as the minimum price doesn't necessarily match up with the minimum date for a particular (correct me i'm wrong here). How do I go about correcting this?|||Ooh think I nearly got it, can someone verify this :
SELECT p.accom_code
,x.min_start
,MIN(CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.indic_price
END AS NUMERIC(7,2))) as min_price
FROM package p
JOIN (
SELECT p.accom_code,MIN(p.start_date) as min_start
FROM package p
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code
) x
ON p.accom_code = x.accom_code AND x.min_start = p.start_date
WHERE p.accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code,x.min_start
In theory it should give the cheapest price on the earliest start date for each of the 10 accomodation types.
Edit : Just to finish it off I needed the start_date out of it as well ;) (added x.min_start to select and group clause)|||looks okay to me
i would not use the same alias "p" in more than one place in the query
and i would probably remove one of the following:AND p.duration = 7
AND p.duration = 7;)|||LOL oh yeah. I've cleaned it up in the above post now.
However I do have one question to finish it off (and this annoys me about of lot of SQL i've done in the past) :
Is there any way to remove the duplicate WHERE clauses in a query like this, and somehow apply it only once but for both sections? I hope that question makes sense.|||Is there any way to remove the duplicate WHERE clauses in a query like thisdefine a view|||Btw, your queries won't work at all in SQL because you used delimited schema/table/column names in the CREATE TABLE statement (i.e. double-quotes) but not in your query.|||That DDL was written in PMS for Postgres so it's a pure copy. I did mention that I was using Postgresql in my first post.
What I DID want to be standard was the SQL query I was doing. :D|||Well, then you have to use delimited schema/table/column names in your query - as I said.|||I have come across a problem with the query above. If two records calculate the same minimum price for one accom_code I can get two results when joined to itself.
i.e. min price and min date is £189 and 25/05/07
when self-joined total price = £567 / 3 adults = £189
and total price = £378 / 2 adults = £189
They both match the minimum price and date and thus both records are output. DOH!
As a quick hack (and I don't like this method) I did a distinct and ordered by total price minimum first.|||Is there any way to remove the duplicate WHERE clauses in a query like this, and somehow apply it only once but for both sections?
For this purpose, SQL99 defines "common table expressions", i.e., a "WITH" subclause of a select statement. Not yet available in the current version of PostgreSQL but coming soon (8.4 probably) ;)
(DB2, Oracle and SQLServer already have them in place.)
Your query, with CTEs, would become:WITH p AS
( SELECT accom_code, start_date,
MIN(CAST (CASE pricing_type
WHEN 'UN' THEN unit_price
WHEN 'PA' THEN indic_price*adult_age_max_cnt
ELSE indic_price
END AS NUMERIC(7,2))) as min_price
FROM package
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC',
'BDB9','HGMD','CMEF','BGDE','YRB5','BJAM')
AND duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
),
x AS
( SELECT p.accom_code,MIN(p.start_date) as min_start
FROM p
GROUP BY p.accom_code
)
SELECT p.accom_code,x.min_start,p.min_price
FROM p INNER JOIN x
ON p.accom_code = x.accom_code AND x.min_start = p.start_date
GROUP BY p.accom_code,x.min_start
These are effectively "local view definitions", cf suggestion by r937.
(Note that in the above query, the SELECT .. p.min_price is not compatible with the GROUP BY -- maybe add p.min_price to the GROUP BY? Or replace it by "MIN(p.min_price)"?)sql
(This is related to a travel website)
I have the following table layout :
CREATE TABLE "public"."package" (
"id" BIGINT NOT NULL,
"accom_code" VARCHAR(4) NOT NULL,
"start_date" DATE NOT NULL,
"end_date" DATE NOT NULL,
"pricing_type" VARCHAR(2),
"indic_price" NUMERIC(7,2),
"unit_price" NUMERIC(7,2),
"adult_age_max_cnt" INTEGER,
CONSTRAINT "package_pkey" PRIMARY KEY("id")
) WITH OIDS;
The package table contains a list (a very large one) for holiday accomodation packages.
What i'm trying to get is the following :
The MINIMUM price using the following for "price" :
CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.unit_price
END AS NUMERIC(7,2))
AND
The minimum date (i.e. nearest start date) greater than today
WHERE the start_date equals the minimum start_date and the price equals the minimum price.
Any thoughts?
With any luck I will be able to give you an test data insert for this.
This should probably be posted in the POSTGRESQL section however I feel that it is a more general SQL question than anything else.What I have thus far :
SELECT p.accom_code
,MIN(CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.indic_price
END AS NUMERIC(7,2))) as min_price
,MIN(p.start_date) as min_start
FROM package p
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code
At the present time i'm getting 10 records (as expected). However I believe these records are WRONG as the minimum price doesn't necessarily match up with the minimum date for a particular (correct me i'm wrong here). How do I go about correcting this?|||Ooh think I nearly got it, can someone verify this :
SELECT p.accom_code
,x.min_start
,MIN(CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.indic_price
END AS NUMERIC(7,2))) as min_price
FROM package p
JOIN (
SELECT p.accom_code,MIN(p.start_date) as min_start
FROM package p
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code
) x
ON p.accom_code = x.accom_code AND x.min_start = p.start_date
WHERE p.accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code,x.min_start
In theory it should give the cheapest price on the earliest start date for each of the 10 accomodation types.
Edit : Just to finish it off I needed the start_date out of it as well ;) (added x.min_start to select and group clause)|||looks okay to me
i would not use the same alias "p" in more than one place in the query
and i would probably remove one of the following:AND p.duration = 7
AND p.duration = 7;)|||LOL oh yeah. I've cleaned it up in the above post now.
However I do have one question to finish it off (and this annoys me about of lot of SQL i've done in the past) :
Is there any way to remove the duplicate WHERE clauses in a query like this, and somehow apply it only once but for both sections? I hope that question makes sense.|||Is there any way to remove the duplicate WHERE clauses in a query like thisdefine a view|||Btw, your queries won't work at all in SQL because you used delimited schema/table/column names in the CREATE TABLE statement (i.e. double-quotes) but not in your query.|||That DDL was written in PMS for Postgres so it's a pure copy. I did mention that I was using Postgresql in my first post.
What I DID want to be standard was the SQL query I was doing. :D|||Well, then you have to use delimited schema/table/column names in your query - as I said.|||I have come across a problem with the query above. If two records calculate the same minimum price for one accom_code I can get two results when joined to itself.
i.e. min price and min date is £189 and 25/05/07
when self-joined total price = £567 / 3 adults = £189
and total price = £378 / 2 adults = £189
They both match the minimum price and date and thus both records are output. DOH!
As a quick hack (and I don't like this method) I did a distinct and ordered by total price minimum first.|||Is there any way to remove the duplicate WHERE clauses in a query like this, and somehow apply it only once but for both sections?
For this purpose, SQL99 defines "common table expressions", i.e., a "WITH" subclause of a select statement. Not yet available in the current version of PostgreSQL but coming soon (8.4 probably) ;)
(DB2, Oracle and SQLServer already have them in place.)
Your query, with CTEs, would become:WITH p AS
( SELECT accom_code, start_date,
MIN(CAST (CASE pricing_type
WHEN 'UN' THEN unit_price
WHEN 'PA' THEN indic_price*adult_age_max_cnt
ELSE indic_price
END AS NUMERIC(7,2))) as min_price
FROM package
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC',
'BDB9','HGMD','CMEF','BGDE','YRB5','BJAM')
AND duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
),
x AS
( SELECT p.accom_code,MIN(p.start_date) as min_start
FROM p
GROUP BY p.accom_code
)
SELECT p.accom_code,x.min_start,p.min_price
FROM p INNER JOIN x
ON p.accom_code = x.accom_code AND x.min_start = p.start_date
GROUP BY p.accom_code,x.min_start
These are effectively "local view definitions", cf suggestion by r937.
(Note that in the above query, the SELECT .. p.min_price is not compatible with the GROUP BY -- maybe add p.min_price to the GROUP BY? Or replace it by "MIN(p.min_price)"?)sql
Wednesday, March 21, 2012
Minimum Hardware Requirement Warning
Hi all, we have released a shrink-wrapped software package that uses SQL Server Express as it's backend. I am currently tracking down an issue to do with SQL Server Express not installing correctly, and at the end of a lot of investigation I have come to the conclusion that it is a CPU problem.
The CPU is not "PII Compatible" enough for SQL Express. It appears as though the Cache prefetching instructions are the most likely culprit, but I was just wondering why the SQL Server Installer allows the user to continue with only a warning about the minimum hardware requirement not being met?
It seems to me that a lot of time and pain would be saved if the installer just said "Sorry, but you have no hope in hell of running SQL Server... go out and buy yourself a real machine" instead of allowing the user to continue all the way through the install and finally crash out at the final step.
Hey Scott,
Yeah, it turns out that the cache prefetching is required and we're not checking for it up front. I've filed a request to add that check for the next version of the product.
-Jeffrey.
|||Thanks, that would be a great help.|||Sorry, signed in with the wrong passport.
This will be a great help, thanks.
Friday, March 9, 2012
Migration SSIS in msdb to other server
I depolyed several package in a server A msdb in some logical folders
E.g. msdb\Task1\package1
msdb\Task2\package2
msdb\Task3\package3
I would like to migrate the package to other server B and keep the logical folders tree.
I know I can reploy the packages in B and move them to the logical folders tree one by one, it is too slow.
Is there any fast method to migrate the package and keeping the tree?
Thanks.
Sure, move the tables directly. Should work.
You can use a package to move rows in the sysdtspackagefolders90 and sysdtspackages90 tables. Move the packagefolders rows first to retain RI.
Be careful that there aren't identically named folders or packages, it could get confusing. Also, do this too much and you'll get duplicate IDs etc. So, proceed with caution!!!
Kirk Haselden
Author "SQL Server Integration Services"
Migration of SQL 7.0 package to SQL 2005
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!
>
>
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
>
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!
>
>
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
>
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...
> > 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!
> >
> >
> >
> >
>|||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...
>> 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!
>> >
>> >
>> >
>> >
>>
>|||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
0x1787C8739479E8EEE3EDD35BDD12403EAF86F119B7E6BB7D71C055D523BCAF9B80C874A881
ADB6E23669940A342D8A5C13A8FCD425B3C8FA1F469E8326E6839D2B482D22143EC03E /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...
> > 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...
> >> > 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!
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>|||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
>
0x1787C8739479E8EEE3EDD35BDD12403EAF86F119B7E6BB7D71C055D523BCAF9B80C874A881
> ADB6E23669940A342D8A5C13A8FCD425B3C8FA1F469E8326E6839D2B482D22143EC03E /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...
> > > 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...
> > >> > 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!
> > >> >
> > >> >
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>
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...
> > 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!
> >
> >
> >
> >
>|||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...
>> 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!
>> >
>> >
>> >
>> >
>>
>|||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
0x1787C8739479E8EEE3EDD35BDD12403EAF86F119B7E6BB7D71C055D523BCAF9B80C874A881
ADB6E23669940A342D8A5C13A8FCD425B3C8FA1F469E8326E6839D2B482D22143EC03E /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...
> > 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...
> >> > 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!
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>|||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
>
0x1787C8739479E8EEE3EDD35BDD12403EAF86F119B7E6BB7D71C055D523BCAF9B80C874A881
> ADB6E23669940A342D8A5C13A8FCD425B3C8FA1F469E8326E6839D2B482D22143EC03E /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...
> > > 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...
> > >> > 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!
> > >> >
> > >> >
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>
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]
>
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]
>
Migration of DTS to SSIS
Well..
Have migrated my SQL 2000 DTS packages to SSIS packages through the package migration Wizard.Also, the hurdle to schedule it as a job has been resolved.But, the concern is the package contains entries\lins for old dataserver.
Is it possible to edit the SSIS pckage using management studio or integration services?
Please move this question to the " SQL Server Integration Services forum" .
Thanks
Migration of DTS to SSIS
Well..
Have migrated my SQL 2000 DTS packages to SSIS packages through the package migration Wizard.Also, the hurdle to schedule it as a job has been resolved.But, the concern is the package contains entries\lins for old dataserver.
Is it possible to edit the SSIS pckage using management studio or integration services?
Please move this question to the " SQL Server Integration Services forum" .
Thanks
Wednesday, March 7, 2012
Migration Maint Plns SQL Server 2005 ==> SQL Server 2005
Hello all,
Has anyone been able to migrate a maint plan from one server to another and have it work? I can do the migrition by doing a package export but when I go into the package on the new server and change the connection information I get a guid should contain 32 digits with 4 dashes error message. Does anyone know a work around?
Hi
I'm having the same problem did you ever find a solution for it?
|||I'm having the same problem - and I did not migrate any plan, just created a new one on the new server. I get this error when I try to save/edit the plan.Toby|||
I have moved this thread to the SQL Server Tools General forum whihc is where Maintenance Plans are best discussed.
Donald Farmer
|||We have a bug tracking for this this issue and there is no workaround for this issue. I will update more on the status of this bug after following up with my team.
Thanks,
Gops Dwarak
|||I am experiencing the same problems, have their been any updates on this?
Edward
Migration Maint Plns SQL Server 2005 ==> SQL Server 2005
Hello all,
Has anyone been able to migrate a maint plan from one server to another and have it work? I can do the migrition by doing a package export but when I go into the package on the new server and change the connection information I get a guid should contain 32 digits with 4 dashes error message. Does anyone know a work around?
Hi
I'm having the same problem did you ever find a solution for it?
|||I'm having the same problem - and I did not migrate any plan, just created a new one on the new server. I get this error when I try to save/edit the plan.Toby|||
I have moved this thread to the SQL Server Tools General forum whihc is where Maintenance Plans are best discussed.
Donald Farmer
|||We have a bug tracking for this this issue and there is no workaround for this issue. I will update more on the status of this bug after following up with my team.
Thanks,
Gops Dwarak
|||I am experiencing the same problems, have their been any updates on this?
Edward
Migration Maint Plns SQL Server 2005 ==> SQL Server 2005
Hello all,
Has anyone been able to migrate a maint plan from one server to another and have it work? I can do the migrition by doing a package export but when I go into the package on the new server and change the connection information I get a guid should contain 32 digits with 4 dashes error message. Does anyone know a work around?
Hi
I'm having the same problem did you ever find a solution for it?
|||I'm having the same problem - and I did not migrate any plan, just created a new one on the new server. I get this error when I try to save/edit the plan.Toby|||
I have moved this thread to the SQL Server Tools General forum whihc is where Maintenance Plans are best discussed.
Donald Farmer
|||
We have a bug tracking for this this issue and there is no workaround for this issue. I will update more on the status of this bug after following up with my team.
Thanks,
Gops Dwarak
|||I am experiencing the same problems, have their been any updates on this?
Edward
Subscribe to:
Posts (Atom)