migrating DTS TO SSIS

Hi ,

Getting error even remove Text (Source) task in DTS.

Pl. help me .

Thanks,

Raja

Error:

LogID=23
#Time=11:32 AM
#Level=DTSMW_LOGLEVEL_ERR
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Failed to save package file "C:\PONE_SSIS_PACKAGES\PONE_SSIS_SELLTHRU\PONE_DTS_SELLTHRU_TEST.dtsx" with error 0x80004005 "Unspecified error".
> System.Runtime.InteropServices.COMException (0xC001100E): Failed to save package file "C:\PONE_SSIS_PACKAGES\PONE_SSIS_SELLTHRU\PONE_DTS_SELLTHRU_TEST.dtsx" with error 0x80004005 "Unspecified error".

at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.SaveToXML(String FileName, IDTSPersist90 pPersistObj, IDTSEvents90 pEvents)
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToXml(String fileName, Package package, IDTSEvents events)
End of inner exception stack trace
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToXml(String fileName, Package package, IDTSEvents events)
at Microsoft.SqlServer.Dts.MigrationWizard.DTS9HelperUtility.DTS9Helper.SaveToXML(Package pkg, String sFileLocation)
at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.StartMigration(PackageInfo pInfo)

Hello Raja,

I cannot help you with your specific issue but I recomend that you do not use the DTS SSIS migration wizard. I have tried it several times but it ended with creating new SSIS packages from scratch...

/Erik
|||

Here are some other resources taht talk about DTS-SSIS migration:

http://www.google.com/search?hl=en&q=dts+to+ssis+migration

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=DTS+migration&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

|||

Hi Raja,

Did you get it fixed ? I have the similar issue scratching my head arround for several hrs.

Regards,

Rahim.

|||

Hi Rahim,

I started new SSIS and copy and paste to create second SSIS. It is easy way to start than DTS to SSIS migration. I don't find any DTS to SSIS conversion with out error.

Regards,

Raja

|||

we found that embedding legacy DTS packages in SSIS worked fine. Just create a new package and add an "Execute DTS 2000 Package Task". You can then load the old package in and embed it permenantly. We've had no problems running our old packages in this way Going forward we will rewrite as and when required.

Good luck.

|||

Hi Jez,

Thanks a lot for your response. I did as you suggested and it worked.

Regards,

Rahim

|||

Hi Raja,

Greetings!
Thank you for your response. Since we have many packages and some of them are big we couldn't copy and paste it.

Instead we created new SSIS package and then added the problem packages as DTS 2000 tasks.

Regards,

Rahim.

migrating DTS TO SSIS

Hi ,

Getting error even remove Text (Source) task in DTS.

Pl. help me .

Thanks,

Raja

Error:

LogID=23
#Time=11:32 AM
#Level=DTSMW_LOGLEVEL_ERR
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Failed to save package file "C:\PONE_SSIS_PACKAGES\PONE_SSIS_SELLTHRU\PONE_DTS_SELLTHRU_TEST.dtsx" with error 0x80004005 "Unspecified error".
> System.Runtime.InteropServices.COMException (0xC001100E): Failed to save package file "C:\PONE_SSIS_PACKAGES\PONE_SSIS_SELLTHRU\PONE_DTS_SELLTHRU_TEST.dtsx" with error 0x80004005 "Unspecified error".

at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.SaveToXML(String FileName, IDTSPersist90 pPersistObj, IDTSEvents90 pEvents)
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToXml(String fileName, Package package, IDTSEvents events)
End of inner exception stack trace
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToXml(String fileName, Package package, IDTSEvents events)
at Microsoft.SqlServer.Dts.MigrationWizard.DTS9HelperUtility.DTS9Helper.SaveToXML(Package pkg, String sFileLocation)
at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.StartMigration(PackageInfo pInfo)

Hello Raja,

I cannot help you with your specific issue but I recomend that you do not use the DTS SSIS migration wizard. I have tried it several times but it ended with creating new SSIS packages from scratch...

/Erik
|||

Here are some other resources taht talk about DTS-SSIS migration:

http://www.google.com/search?hl=en&q=dts+to+ssis+migration

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=DTS+migration&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

|||

Hi Raja,

Did you get it fixed ? I have the similar issue scratching my head arround for several hrs.

Regards,

Rahim.

|||

Hi Rahim,

I started new SSIS and copy and paste to create second SSIS. It is easy way to start than DTS to SSIS migration. I don't find any DTS to SSIS conversion with out error.

Regards,

Raja

|||

we found that embedding legacy DTS packages in SSIS worked fine. Just create a new package and add an "Execute DTS 2000 Package Task". You can then load the old package in and embed it permenantly. We've had no problems running our old packages in this way Going forward we will rewrite as and when required.

Good luck.

|||

Hi Jez,

Thanks a lot for your response. I did as you suggested and it worked.

Regards,

Rahim

|||

Hi Raja,

Greetings!
Thank you for your response. Since we have many packages and some of them are big we couldn't copy and paste it.

Instead we created new SSIS package and then added the problem packages as DTS 2000 tasks.

Regards,

Rahim.

migrating DTS TO SSIS

Hi ,

Getting error even remove Text (Source) task in DTS.

Pl. help me .

Thanks,

Raja

Error:

LogID=23
#Time=11:32 AM
#Level=DTSMW_LOGLEVEL_ERR
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Failed to save package file "C:\PONE_SSIS_PACKAGES\PONE_SSIS_SELLTHRU\PONE_DTS_SELLTHRU_TEST.dtsx" with error 0x80004005 "Unspecified error".
> System.Runtime.InteropServices.COMException (0xC001100E): Failed to save package file "C:\PONE_SSIS_PACKAGES\PONE_SSIS_SELLTHRU\PONE_DTS_SELLTHRU_TEST.dtsx" with error 0x80004005 "Unspecified error".

at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.SaveToXML(String FileName, IDTSPersist90 pPersistObj, IDTSEvents90 pEvents)
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToXml(String fileName, Package package, IDTSEvents events)
End of inner exception stack trace
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToXml(String fileName, Package package, IDTSEvents events)
at Microsoft.SqlServer.Dts.MigrationWizard.DTS9HelperUtility.DTS9Helper.SaveToXML(Package pkg, String sFileLocation)
at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.StartMigration(PackageInfo pInfo)

Hello Raja,

I cannot help you with your specific issue but I recomend that you do not use the DTS SSIS migration wizard. I have tried it several times but it ended with creating new SSIS packages from scratch...

/Erik
|||

Here are some other resources taht talk about DTS-SSIS migration:

http://www.google.com/search?hl=en&q=dts+to+ssis+migration

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=DTS+migration&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

|||

Hi Raja,

Did you get it fixed ? I have the similar issue scratching my head arround for several hrs.

Regards,

Rahim.

|||

Hi Rahim,

I started new SSIS and copy and paste to create second SSIS. It is easy way to start than DTS to SSIS migration. I don't find any DTS to SSIS conversion with out error.

Regards,

Raja

|||

we found that embedding legacy DTS packages in SSIS worked fine. Just create a new package and add an "Execute DTS 2000 Package Task". You can then load the old package in and embed it permenantly. We've had no problems running our old packages in this way Going forward we will rewrite as and when required.

Good luck.

|||

Hi Jez,

Thanks a lot for your response. I did as you suggested and it worked.

Regards,

Rahim

|||

Hi Raja,

Greetings!
Thank you for your response. Since we have many packages and some of them are big we couldn't copy and paste it.

Instead we created new SSIS package and then added the problem packages as DTS 2000 tasks.

Regards,

Rahim.

Migrating DTS packages Question: Dynamic Properties

Hi guys,

After DTS Migration is "successful", I opened up dynamic properties step but I saw codes were all commented out inside the public class ScriptMain. Since I am not familiar with Microsoft.SqlServer.Dts.Runtime, I'm not sure what methods I should use as there aren't much examples available online. How do I do the following in SISS way?

' Add your code here

' Source Type = 2

' Global variable = glvTrade

' Destination = 'Connections';'Trade';'OLEDBProperties';'Data Source';'Properties';'Value'

Regards

Hi,
I have same problem too.
' Source(Type = 2)
' Global variable = SPLastDate
' Destination = 'Tasks';'DTSTask_DTSExecuteSQLTask_7';'Properties';'SQLStatement'
How can i convert this code for ssis ?
Does anyone have any solution ?

Thans
Yasemin

Migrating DTS packages Question: Dynamic Properties

Hi guys,

After DTS Migration is "successful", I opened up dynamic properties step but I saw codes were all commented out inside the public class ScriptMain. Since I am not familiar with Microsoft.SqlServer.Dts.Runtime, I'm not sure what methods I should use as there aren't much examples available online. How do I do the following in SISS way?

' Add your code here

' Source Type = 2

' Global variable = glvTrade

' Destination = 'Connections';'Trade';'OLEDBProperties';'Data Source';'Properties';'Value'

Regards

Hi,
I have same problem too.
' Source(Type = 2)
' Global variable = SPLastDate
' Destination = 'Tasks';'DTSTask_DTSExecuteSQLTask_7';'Properties';'SQLStatement'
How can i convert this code for ssis ?
Does anyone have any solution ?

Thans
Yasemin

sql

Migrating DTS packages on instances

I have problem and don't know how to resolve it.

I'm running multiple instances(developpement,acceptation, production) on one server with the same db's on each one of them.

Now I'm trying to find out a way to transfer DTS packages from one instance to another (I know it is straight-forward if you don't have to change anything) but in my case I would want to change the connection properties within the dts package e.g.

on my server\developpement instance I've created a DTS package with connection properties server name server\developpement

Now I want to transfer it to my server\acceptation instance and there I want the connection properties server name to be server\acceptation .

I've tried doing it with a dynamic properties task but I can't seem to find the instance name where the package is saved. I can only find the server name(environment variable).

This of course is no problem if you have but a few packages, but if you have over 50 to transfer

Any help would be more than welcome.

Rosko

PS: I know it is possible between two different servers using (local) as the servername but that's not an option for my boss.I have a very similar situation. I currently have global variables. When I migrate, I am changing this from Package Properties.

If I do not find a solution, I plan to have a flat file and read the information from that file using ActiveScript.

Please let me know if you have a better solution.|||See

"LoadFromSQLServer' & 'SaveToSQLServer'
in SQLServer Books online or on the web.
this can be called in VB and will repopulate on the other server.

For more advanced changes the attached structe can be populated and manipulated in VB to create new DTS Packages.|||Correct me if I am wrong. I think this still does not address the issue.

The issue is to set the datasource property to the connections within the package(s) while the package is migrated from one instance to another.

LoadFromSQLServer and SaveToSQLServer seem to help in migrating from one instance to another.|||Unfortunately jayaramanc,

The connections held in the connections object must also be updated with the new server name, as does any ADODB connections in ActiveXScript in the Steps object.

MIgrating DTS packages of SQL 2000 to SQL 2005

Hello All,

I am working on a migration project of my database(lets name it DB1), from SQL 2000 to SQL 2005. I have some DTS packages in my SQL 2000 database which also needs to be migrated to SSIS. These DTS packages currently interacts with other database(lets name it DB2) which is also in SQL 2000 and which does the data transmission. The real issue is that the other database(DB2) also has some DTS packages which also communicates with DB1 (the db to be upgraded) and so when i migrate my DB1 to SQL 2005, i will have to change my DB2 packages also (although the change will be minor). Now considering that in future I might also migrate my DB2 to SQL 2005 I wanted to know what will be the right approach to follow. One is to modify the DTS packages of DB2 to accmodate the change in connection and the other is to migrate the DTS packages of DB2 also to SSIS.

Can anyone help me with this one.

Thanks in Advance

Mitesh

Hi Mitesh,

Something to consider: You can migrate a SQL Server 2000 database (DB1) to SQL Server 2005 and continue to run it at a SQL Server 2000 Compatibility Level. The implication is you may be able to continue connecting to DB1 from the DB2 DTS packages as you do now - it will just be on a different instance of SQL Server. I'm not sure what your DB2 DTS packages do exactly, so it's difficult to make that statement with more confidence.

There's also an Execute DTS 2000 Package Task available in SSIS. You can execute your existing DTS packages from SSIS. I've used this (and the aforementioned Compatibility Level) before as part of a migration plan from SQL Server 2000 to SQL Server 2005. It's nice because I don't have to get everything converted before starting to use the new database engine.

Regarding migrating between DTS and SSIS: that's a tough one. I teach ETL with SSIS for Solid Quality Learning. One of the things I share with students is the fact that SSIS is not the new version of DTS - it's a replacement. Microsoft did not start with DTS and modify it - they rewrote the entire application. This has some bearing on how easy it is to migrate from DTS. I've seen very simple DTS packages migrate easily, but that's about it.

There's so much new under the hood, you will most likely want to rebuild - and perhaps redesign - your packages in SSIS to take advantage of some of the stellar performance gains.

Hope this helps,

Andy

|||

Hey Andy,

Thanks for the reply will surely consider your suggestion. Just one more thing, when i use the Execute DTS 2000 Package task of SSIS i will have to change my connection as my database is migrated to SQL 2005 on another server. Correct me if i am wrong

|||

Hi Mitesh,

Yes sir - you will have to change your connection to target another database, server, or both.

Hope this helps,

Andy

Migrating DTS packages from SQL2000 to SQL2005

Hi,
My initial tests show that this feature will only see DTS Local Packages.
How can we migrate the DTS Meta Data Services Packages?
Thanks
Chris
The requirements for migrating DTS packages saved to the
repository are explained in Books Online help topic:
Using the Package Migration Wizard
http://msdn2.microsoft.com/en-us/library/ms143496.aspx
-Sue
On Tue, 19 Sep 2006 08:04:56 -0600, "Chris Wood"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>My initial tests show that this feature will only see DTS Local Packages.
>How can we migrate the DTS Meta Data Services Packages?
>Thanks
>Chris
>
|||Sue,
This mentions Repository packages only if SQL2000, SQL2000 tools or the
Repository files are present on the local computer, the server that the
Migration Wizard is being run on I assume? As we have msdb as our Repository
we would have to migrate the SQL2000 msdb to the SQL2005 to find the Meta
Data Services packages?
Thanks
Chris
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:7a20h2pq7uggrgp7iuvggghvdpu343mms8@.4ax.com...
> The requirements for migrating DTS packages saved to the
> repository are explained in Books Online help topic:
> Using the Package Migration Wizard
> http://msdn2.microsoft.com/en-us/library/ms143496.aspx
> -Sue
> On Tue, 19 Sep 2006 08:04:56 -0600, "Chris Wood"
> <anonymous@.discussions.microsoft.com> wrote:
>
|||Yes...on the server where the migration wizard is being run.
The wizard needs the repository files to read the packages.
You just need the repository redistributable files on that
server.
-Sue
On Tue, 19 Sep 2006 10:07:43 -0600, "Chris Wood"
<anonymous@.discussions.microsoft.com> wrote:

>Sue,
>This mentions Repository packages only if SQL2000, SQL2000 tools or the
>Repository files are present on the local computer, the server that the
>Migration Wizard is being run on I assume? As we have msdb as our Repository
>we would have to migrate the SQL2000 msdb to the SQL2005 to find the Meta
>Data Services packages?
>Thanks
>Chris
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:7a20h2pq7uggrgp7iuvggghvdpu343mms8@.4ax.com.. .
>
|||Thanks Sue.
The analysts changed the packages to Local packages so we could use the
Migration Wizard.
Now we have migrated them we had to install the SQL2000 DTS Designer
Components, from the Feature Pack, but we still cannot see the migrated
packages. I remember an article by Brian Moran in SQL Mag on how to fix this
problem but I don't have the article on hand.
Can someone tell me the fix please? I think it had to do with the registry.
Thanks
Chris
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:2t81h2trkbmjlfsve86v8egsgs9lc01cci@.4ax.com...
> Yes...on the server where the migration wizard is being run.
> The wizard needs the repository files to read the packages.
> You just need the repository redistributable files on that
> server.
> -Sue
> On Tue, 19 Sep 2006 10:07:43 -0600, "Chris Wood"
> <anonymous@.discussions.microsoft.com> wrote:
>

Migrating DTS packages from SQL2000 to SQL2005

Hi,
My initial tests show that this feature will only see DTS Local Packages.
How can we migrate the DTS Meta Data Services Packages?
Thanks
ChrisThe requirements for migrating DTS packages saved to the
repository are explained in Books Online help topic:
Using the Package Migration Wizard
http://msdn2.microsoft.com/en-us/library/ms143496.aspx
-Sue
On Tue, 19 Sep 2006 08:04:56 -0600, "Chris Wood"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>My initial tests show that this feature will only see DTS Local Packages.
>How can we migrate the DTS Meta Data Services Packages?
>Thanks
>Chris
>|||Sue,
This mentions Repository packages only if SQL2000, SQL2000 tools or the
Repository files are present on the local computer, the server that the
Migration Wizard is being run on I assume? As we have msdb as our Repository
we would have to migrate the SQL2000 msdb to the SQL2005 to find the Meta
Data Services packages?
Thanks
Chris
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:7a20h2pq7uggrgp7iuvggghvdpu343mms8@.4ax.com...
> The requirements for migrating DTS packages saved to the
> repository are explained in Books Online help topic:
> Using the Package Migration Wizard
> http://msdn2.microsoft.com/en-us/library/ms143496.aspx
> -Sue
> On Tue, 19 Sep 2006 08:04:56 -0600, "Chris Wood"
> <anonymous@.discussions.microsoft.com> wrote:
>>Hi,
>>My initial tests show that this feature will only see DTS Local Packages.
>>How can we migrate the DTS Meta Data Services Packages?
>>Thanks
>>Chris
>|||Yes...on the server where the migration wizard is being run.
The wizard needs the repository files to read the packages.
You just need the repository redistributable files on that
server.
-Sue
On Tue, 19 Sep 2006 10:07:43 -0600, "Chris Wood"
<anonymous@.discussions.microsoft.com> wrote:
>Sue,
>This mentions Repository packages only if SQL2000, SQL2000 tools or the
>Repository files are present on the local computer, the server that the
>Migration Wizard is being run on I assume? As we have msdb as our Repository
>we would have to migrate the SQL2000 msdb to the SQL2005 to find the Meta
>Data Services packages?
>Thanks
>Chris
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:7a20h2pq7uggrgp7iuvggghvdpu343mms8@.4ax.com...
>> The requirements for migrating DTS packages saved to the
>> repository are explained in Books Online help topic:
>> Using the Package Migration Wizard
>> http://msdn2.microsoft.com/en-us/library/ms143496.aspx
>> -Sue
>> On Tue, 19 Sep 2006 08:04:56 -0600, "Chris Wood"
>> <anonymous@.discussions.microsoft.com> wrote:
>>Hi,
>>My initial tests show that this feature will only see DTS Local Packages.
>>How can we migrate the DTS Meta Data Services Packages?
>>Thanks
>>Chris
>>
>|||Thanks Sue.
The analysts changed the packages to Local packages so we could use the
Migration Wizard.
Now we have migrated them we had to install the SQL2000 DTS Designer
Components, from the Feature Pack, but we still cannot see the migrated
packages. I remember an article by Brian Moran in SQL Mag on how to fix this
problem but I don't have the article on hand.
Can someone tell me the fix please? I think it had to do with the registry.
Thanks
Chris
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:2t81h2trkbmjlfsve86v8egsgs9lc01cci@.4ax.com...
> Yes...on the server where the migration wizard is being run.
> The wizard needs the repository files to read the packages.
> You just need the repository redistributable files on that
> server.
> -Sue
> On Tue, 19 Sep 2006 10:07:43 -0600, "Chris Wood"
> <anonymous@.discussions.microsoft.com> wrote:
>>Sue,
>>This mentions Repository packages only if SQL2000, SQL2000 tools or the
>>Repository files are present on the local computer, the server that the
>>Migration Wizard is being run on I assume? As we have msdb as our
>>Repository
>>we would have to migrate the SQL2000 msdb to the SQL2005 to find the Meta
>>Data Services packages?
>>Thanks
>>Chris
>>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>>news:7a20h2pq7uggrgp7iuvggghvdpu343mms8@.4ax.com...
>> The requirements for migrating DTS packages saved to the
>> repository are explained in Books Online help topic:
>> Using the Package Migration Wizard
>> http://msdn2.microsoft.com/en-us/library/ms143496.aspx
>> -Sue
>> On Tue, 19 Sep 2006 08:04:56 -0600, "Chris Wood"
>> <anonymous@.discussions.microsoft.com> wrote:
>>Hi,
>>My initial tests show that this feature will only see DTS Local
>>Packages.
>>How can we migrate the DTS Meta Data Services Packages?
>>Thanks
>>Chris
>>
>

Migrating DTS packages from SQL2000 to SQL2005

Hi,
My initial tests show that this feature will only see DTS Local Packages.
How can we migrate the DTS Meta Data Services Packages?
Thanks
ChrisThe requirements for migrating DTS packages saved to the
repository are explained in Books Online help topic:
Using the Package Migration Wizard
http://msdn2.microsoft.com/en-us/library/ms143496.aspx
-Sue
On Tue, 19 Sep 2006 08:04:56 -0600, "Chris Wood"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>My initial tests show that this feature will only see DTS Local Packages.
>How can we migrate the DTS Meta Data Services Packages?
>Thanks
>Chris
>|||Sue,
This mentions Repository packages only if SQL2000, SQL2000 tools or the
Repository files are present on the local computer, the server that the
Migration Wizard is being run on I assume? As we have msdb as our Repository
we would have to migrate the SQL2000 msdb to the SQL2005 to find the Meta
Data Services packages?
Thanks
Chris
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:7a20h2pq7uggrgp7iuvggghvdpu343mms8@.
4ax.com...
> The requirements for migrating DTS packages saved to the
> repository are explained in Books Online help topic:
> Using the Package Migration Wizard
> http://msdn2.microsoft.com/en-us/library/ms143496.aspx
> -Sue
> On Tue, 19 Sep 2006 08:04:56 -0600, "Chris Wood"
> <anonymous@.discussions.microsoft.com> wrote:
>
>|||Yes...on the server where the migration wizard is being run.
The wizard needs the repository files to read the packages.
You just need the repository redistributable files on that
server.
-Sue
On Tue, 19 Sep 2006 10:07:43 -0600, "Chris Wood"
<anonymous@.discussions.microsoft.com> wrote:

>Sue,
>This mentions Repository packages only if SQL2000, SQL2000 tools or the
>Repository files are present on the local computer, the server that the
>Migration Wizard is being run on I assume? As we have msdb as our Repositor
y
>we would have to migrate the SQL2000 msdb to the SQL2005 to find the Meta
>Data Services packages?
>Thanks
>Chris
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:7a20h2pq7uggrgp7iuvggghvdpu343mms8@.
4ax.com...
>|||Thanks Sue.
The analysts changed the packages to Local packages so we could use the
Migration Wizard.
Now we have migrated them we had to install the SQL2000 DTS Designer
Components, from the Feature Pack, but we still cannot see the migrated
packages. I remember an article by Brian Moran in SQL Mag on how to fix this
problem but I don't have the article on hand.
Can someone tell me the fix please? I think it had to do with the registry.
Thanks
Chris
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:2t81h2trkbmjlfsve86v8egsgs9lc01cci@.
4ax.com...
> Yes...on the server where the migration wizard is being run.
> The wizard needs the repository files to read the packages.
> You just need the repository redistributable files on that
> server.
> -Sue
> On Tue, 19 Sep 2006 10:07:43 -0600, "Chris Wood"
> <anonymous@.discussions.microsoft.com> wrote:
>
>sql

MIgrating DTS packages from one machine to another

I am building a new machine (SQL server 2000 SP3a on Windows 2003 R2) for an
exisitng production michine (SQL server 2000 SP3a on Windows 2000) with 27
user databases. I can move databases, logins, scheduled jobs. I have lot of
DTS packages and Maintenance Plans. Is there an easy way to recreate these
DTS packages and Maintenance plans on the new server with out creating each
one of them manually?
--
RKYou can open the package on one server, using DTS Designer, and then using
[Package], [Save As], save it to a different server.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:F5FA74AD-DFFC-4C10-AC6D-CED5F8DE6203@.microsoft.com...
>I am building a new machine (SQL server 2000 SP3a on Windows 2003 R2) for
>an
> exisitng production michine (SQL server 2000 SP3a on Windows 2000) with 27
> user databases. I can move databases, logins, scheduled jobs. I have lot
> of
> DTS packages and Maintenance Plans. Is there an easy way to recreate these
> DTS packages and Maintenance plans on the new server with out creating
> each
> one of them manually?
> --
> RK|||Arnie Rowland wrote:[vbcol=seagreen]
> You can open the package on one server, using DTS Designer, and then using
> [Package], [Save As], save it to a different server.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:F5FA74AD-DFFC-4C10-AC6D-CED5F8DE6203@.microsoft.com...
http://www.sqldts.com/?204
Regards
Amish Shah

MIgrating DTS packages from one machine to another

I am building a new machine (SQL server 2000 SP3a on Windows 2003 R2) for an
exisitng production michine (SQL server 2000 SP3a on Windows 2000) with 27
user databases. I can move databases, logins, scheduled jobs. I have lot of
DTS packages and Maintenance Plans. Is there an easy way to recreate these
DTS packages and Maintenance plans on the new server with out creating each
one of them manually?
--
RKYou can open the package on one server, using DTS Designer, and then using
[Package], [Save As], save it to a different server.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:F5FA74AD-DFFC-4C10-AC6D-CED5F8DE6203@.microsoft.com...
>I am building a new machine (SQL server 2000 SP3a on Windows 2003 R2) for
>an
> exisitng production michine (SQL server 2000 SP3a on Windows 2000) with 27
> user databases. I can move databases, logins, scheduled jobs. I have lot
> of
> DTS packages and Maintenance Plans. Is there an easy way to recreate these
> DTS packages and Maintenance plans on the new server with out creating
> each
> one of them manually?
> --
> RK|||Arnie Rowland wrote:
> You can open the package on one server, using DTS Designer, and then using
> [Package], [Save As], save it to a different server.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:F5FA74AD-DFFC-4C10-AC6D-CED5F8DE6203@.microsoft.com...
> >I am building a new machine (SQL server 2000 SP3a on Windows 2003 R2) for
> >an
> > exisitng production michine (SQL server 2000 SP3a on Windows 2000) with 27
> > user databases. I can move databases, logins, scheduled jobs. I have lot
> > of
> > DTS packages and Maintenance Plans. Is there an easy way to recreate these
> > DTS packages and Maintenance plans on the new server with out creating
> > each
> > one of them manually?
> > --
> > RK
http://www.sqldts.com/?204
Regards
Amish Shah

Migrating DTS packages and SQL Jobs

Hello All,
We are in the process of migrating our SQLServer2000 production server to a
better hardware and Windows 2003. We have lot of DTS packages and SQL Jobs
that needs to be migrated as well. I can script the Jobs and run the script
on the new server and similarly I can save the DTS package on the new server.
Instead, Can I backup the msdb database and restore it on the new server. I
was wondering which method would be the best.
Thanks,
rgnRestoring MSDB is certainly an option but if you have anything that
references the original server name directly you may have to fix that.
These links may help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
Roles and Permissions
--
Andrew J. Kelly SQL MVP
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:798481FC-6AD6-461C-A554-AF99EBD5B3E7@.microsoft.com...
> Hello All,
> We are in the process of migrating our SQLServer2000 production server to
> a
> better hardware and Windows 2003. We have lot of DTS packages and SQL Jobs
> that needs to be migrated as well. I can script the Jobs and run the
> script
> on the new server and similarly I can save the DTS package on the new
> server.
> Instead, Can I backup the msdb database and restore it on the new server.
> I
> was wondering which method would be the best.
> Thanks,
> rgn

Migrating DTS packages and SQL Jobs

Hello All,
We are in the process of migrating our SQLServer2000 production server to a
better hardware and Windows 2003. We have lot of DTS packages and SQL Jobs
that needs to be migrated as well. I can script the Jobs and run the script
on the new server and similarly I can save the DTS package on the new server
.
Instead, Can I backup the msdb database and restore it on the new server. I
was wondering which method would be the best.
Thanks,
rgnRestoring MSDB is certainly an option but if you have anything that
references the original server name directly you may have to fix that.
These links may help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/col...se
s.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scr...utions/1598.asp Script
Roles and Permissions
Andrew J. Kelly SQL MVP
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:798481FC-6AD6-461C-A554-AF99EBD5B3E7@.microsoft.com...
> Hello All,
> We are in the process of migrating our SQLServer2000 production server to
> a
> better hardware and Windows 2003. We have lot of DTS packages and SQL Jobs
> that needs to be migrated as well. I can script the Jobs and run the
> script
> on the new server and similarly I can save the DTS package on the new
> server.
> Instead, Can I backup the msdb database and restore it on the new server.
> I
> was wondering which method would be the best.
> Thanks,
> rgn

Migrating DTS packages and SQL Jobs

Hello All,
We are in the process of migrating our SQLServer2000 production server to a
better hardware and Windows 2003. We have lot of DTS packages and SQL Jobs
that needs to be migrated as well. I can script the Jobs and run the script
on the new server and similarly I can save the DTS package on the new server.
Instead, Can I backup the msdb database and restore it on the new server. I
was wondering which method would be the best.
Thanks,
rgn
Restoring MSDB is certainly an option but if you have anything that
references the original server name directly you may have to fix that.
These links may help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
Roles and Permissions
Andrew J. Kelly SQL MVP
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:798481FC-6AD6-461C-A554-AF99EBD5B3E7@.microsoft.com...
> Hello All,
> We are in the process of migrating our SQLServer2000 production server to
> a
> better hardware and Windows 2003. We have lot of DTS packages and SQL Jobs
> that needs to be migrated as well. I can script the Jobs and run the
> script
> on the new server and similarly I can save the DTS package on the new
> server.
> Instead, Can I backup the msdb database and restore it on the new server.
> I
> was wondering which method would be the best.
> Thanks,
> rgn
sql

Migrating DTS packages

Is there anyway to copy all the DTS packages and jobs in one step from one server to another. I usvally copy one DTS package at a time. As our packages count grown bigger, it became a painful process.
If anyone knows a way to copy these packages and jobs in one then it would be very helpful.

ThanksI don't think you can for DTS: Steps I have used in the past:

Save DTS package to hard drive:
Save DTS package as file on the disk, then copy this file on the target SQL Server and load this DTS package on the target machine. Dont forget to save in SQL Server.

Or...

Run DTS Export Wizard, and transfer this select statement:

SELECT * FROM sysdtspackages WHERE name = 'DTSPackageName'

from the msdb database on the source server to the msdb database on the destination server to transfer the DTSPackageName package.

You may be able to add multi packageName, I have never play around with the code. (Maybe someone else will have an idea)

And...

As for jobs you can create a DTS package and under Task select 15 - Transfer job task and follow the steps. You can do all of your jobs at once.

Hope it works out for you.

Thanks

Lystra|||As for the DTS packages go to http://www.sqldts.com/default.aspx?242 This is a free tool to migrate the DTS packages in one step.

migrating DTS packages

I need to migrate dts packages on one server to structured storage files and then restore them all to another server in a different network

i got the restore method down fine, but all of the backup methods I used yield an incorrect DTS package. the only way i can get the package to look right is if I manually open and save it

i would like an automated way if possible

i tried tools from sqldts.com, wrote my own, etc... none of them work right

it is sql server 2000

thanksI need to migrate dts packages on one server to structured storage files and then restore them all to another server in a different network

i got the restore method down fine, but all of the backup methods I used yield an incorrect DTS package. the only way i can get the package to look right is if I manually open and save it

i would like an automated way if possible

i tried tools from sqldts.com, wrote my own, etc... none of them work right

it is sql server 2000

thanks

What's the code you are using for your backup method? If you do a quick search on here, you should find a post that I recently wrote on a script that backsup all DTS packages for a selected instance. I have restored from one of these with no problem (other than perhaps needing to reset a few connection properties).

Regards,

hmscott|||I cannot find your recent post regarding backing up DTS packages, could you provide me with the title of the post, or re-post the code

Here is one of the methods I used:

DECLARE @.COMMAND varchar(1000)
DECLARE @.TARGETDIR varchar(500)
SET @.TARGETDIR = 'D:\DTS\'

DECLARE c1 CURSOR FOR

SELECT distinct
'DTSRUN.EXE /S '
+ CONVERT(varchar(200), SERVERPROPERTY('servername'))
+ ' /E '
+ ' /N '
+ '"' + name + '"'
+ ' /F '
+ '"' + @.TARGETDIR + replace(name,' ','_') + '.dts"'
+ ' /!X'
FROM msdb.dbo.sysdtspackages P

OPEN c1

FETCH NEXT FROM c1
INTO @.COMMAND

WHILE @.@.FETCH_STATUS = 0
BEGIN

exec xp_cmdshell @.COMMAND, no_output

FETCH NEXT FROM c1
INTO @.COMMAND
END

CLOSE c1
DEALLOCATE c1|||Search appears to be FUBAR. I found it by scrolling through. Here it is...

http://www.dbforums.com/showthread.php?t=1607904

Regards,

hmscott|||nevermind.....|||didn't work

i made the update to this thread over here:

http://www.dbforums.com/showthread.php?p=6233426#post6233426|||I can not find my script but I swear I have done this by just moving records between msdb.dbo.sysdtspackages tables|||didn't work

i made the update to this thread over here:

http://www.dbforums.com/showthread.php?p=6233426#post6233426

It's bad form, I realize, but I posted an update to your update on the other thread. Let's agree to move the discussion there for now. Perhaps we can convince a mod to merge the threads?

Regards,

hmscott

Migrating DTS Active X/ADO script to SSIS

Hi All,

In SQL Server 2000 DTS, I had an Active X script that, using ADO, performed the following basic operation:

Retrieve a list of account numbers through Recordset1
While Not Recordset1.eof
Build a sql statement based on fields from current row of Recordset1
Open Recordset2 based on the sql statement built
Run an update query based on results from recordset 2
Loop

I want to bring this over to SSIS, but i'd like to take advantage of the most current "ways of doing things" instead of just using SSIS's activex script.

Does anyone have any recommendations on the best way to handle this type of thing in SSIS? A script task? A script component? I've never really worked w/ ADO.net so if it involves that I'd have to pick up some pointers... any suggestions?

Thanks in advance
Jeff

A script task would be one way to do this. You could use an ADO.Net connection in the Script. You can then use that to help manager the connection information, and it will return a SqlConnection for use in the script. For example-

Public Sub Main()

Dim connection As SqlClient.SqlConnection = CType(Dts.Connections("MyAdoSqlClient").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)

Dim command As New SqlClient.SqlCommand("EXEC ProcName", connection)

command.ExecuteNonQuery()

Dts.Connections("MyAdoSqlClient").ReleaseConnection(connection)

Dts.TaskResult = Dts.Results.Success

End Sub

A script component is only used inside the data flow, and this doesn't seem like it is moving data anywhere really. You could use a data flow, OLE-DB Source to Recordset Destination, but then what. I think this fits into the over engineered category as does the option below.

Alternative methods could be to use the Execute SQL task to get the recordset and the For Each Loop to process the recordset. I'd be tempted by this option. I'd use a script task maybe to just build teh SQL statement inside the loop, then store it in a variable. I don't quite get the Recordet2 and Update steps, but maybe an Execute SQL Task would do this. If you don't end up with any real worker tasks inside the For Each Loop, then I would just do it all in a Script Task again, but using VB.Net and the nice things it gives over ActiveX Script.

Migrating data from SQL 7 to 2000 ? Possible ? DTS ?

Hello,
I have a weird task. After going to SQL 2000 from SQL 7 on a
weekend...after a few days...incase of problems... I might be asked to
revert back to SQL 7 with those upgrade SQL 2000 databases.
Now, I know this cannot be done, but is there a possibility of using DTS to
do this ?
I have tried DTS from SQL 2000 to SQL 7... out of the 6 databases it worked
for 4. Although it does seem kind of "unclean" way to do it.
With the 5th database, I can move everything but procedures. Some of the
procedures refer to columns in user tables which don't exist.
With the 6th database, it seems max precision is set to 38 on SQL 2000 but
only 28 on SQL 7. Which is why this one isn't working at all.
Any suggestions ? Over should I just mark this "unclean" way of going from
SQL 2000 to SQL 7 as NOT an option ?
Thanks in advance.
-Kunal.
The best way as per me is to take a backup of your SQL 7 and restore in your
SQL 2000.
By doing this you will not loose and objects. DTS will not copy all the
objects and the references that are available to the TABLE.
Hope this will solve your problem
thanks and regards
Chandra
"kunalap" wrote:

> Hello,
> I have a weird task. After going to SQL 2000 from SQL 7 on a
> weekend...after a few days...incase of problems... I might be asked to
> revert back to SQL 7 with those upgrade SQL 2000 databases.
> Now, I know this cannot be done, but is there a possibility of using DTS to
> do this ?
> I have tried DTS from SQL 2000 to SQL 7... out of the 6 databases it worked
> for 4. Although it does seem kind of "unclean" way to do it.
> With the 5th database, I can move everything but procedures. Some of the
> procedures refer to columns in user tables which don't exist.
> With the 6th database, it seems max precision is set to 38 on SQL 2000 but
> only 28 on SQL 7. Which is why this one isn't working at all.
> Any suggestions ? Over should I just mark this "unclean" way of going from
> SQL 2000 to SQL 7 as NOT an option ?
> Thanks in advance.
> -Kunal.
|||Im sorry I made a mistake in the title of the topic.
Its SQL 2000 to SQL 7.
The matter of the topic explains the problem correctly.
Its SQL 2000 to SQL 7 not the other way around. Any thoughts ?
Thanks.
-Kunal.
"Chandra" wrote:
[vbcol=seagreen]
> The best way as per me is to take a backup of your SQL 7 and restore in your
> SQL 2000.
> By doing this you will not loose and objects. DTS will not copy all the
> objects and the references that are available to the TABLE.
> Hope this will solve your problem
> thanks and regards
> Chandra
>
> "kunalap" wrote:
|||DTS the objects from the 2000 to the 7.0 server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"kunalap" <kunalap@.discussions.microsoft.com> schrieb im Newsbeitrag
news:7A7185B2-8DDC-4FB7-929A-F32AB5D5703E@.microsoft.com...[vbcol=seagreen]
> Im sorry I made a mistake in the title of the topic.
> Its SQL 2000 to SQL 7.
> The matter of the topic explains the problem correctly.
> Its SQL 2000 to SQL 7 not the other way around. Any thoughts ?
> Thanks.
> -Kunal.
> "Chandra" wrote:
|||Yes thats what I am trying. But doesn't it seem kind of "unclean" way to
maintain production data. Also the 2 problems Im having with 2 of the
databases in DTS'ing.
Any advise ?
"Jens Sü?meyer" wrote:

> DTS the objects from the 2000 to the 7.0 server.
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "kunalap" <kunalap@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:7A7185B2-8DDC-4FB7-929A-F32AB5D5703E@.microsoft.com...
>
>
|||did you use the bakup technique (the one that i posted earlier) here?
thanks and regards
Chandra
"kunalap" wrote:
[vbcol=seagreen]
> Yes thats what I am trying. But doesn't it seem kind of "unclean" way to
> maintain production data. Also the 2 problems Im having with 2 of the
> databases in DTS'ing.
> Any advise ?
> "Jens Sü?meyer" wrote:
|||Transfer whatewer possible with DTS, and then:
1. Try to script objects (procedures etc.) on 7.0 and run the script on
2000.
2. Check options when transferring - allow demotion (shrink) of the numeric
data types.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"kunalap" <kunalap@.discussions.microsoft.com> wrote in message
news:F788F92D-7DE0-44BF-9578-6A4F51C7E9DC@.microsoft.com...[vbcol=seagreen]
> Yes thats what I am trying. But doesn't it seem kind of "unclean" way to
> maintain production data. Also the 2 problems Im having with 2 of the
> databases in DTS'ing.
> Any advise ?
> "Jens Smeyer" wrote:
in[vbcol=seagreen]
the[vbcol=seagreen]
asked[vbcol=seagreen]
using[vbcol=seagreen]
it[vbcol=seagreen]
of[vbcol=seagreen]
2000[vbcol=seagreen]
going[vbcol=seagreen]
|||Chandra,
SQL 2000 backup cannot be applied on SQL 7.
Hence the whole DTS approach.
"Chandra" wrote:
[vbcol=seagreen]
> did you use the bakup technique (the one that i posted earlier) here?
> thanks and regards
> Chandra
>
> "kunalap" wrote:
|||You mean script out on 2000 and then apply on 7, right ? coz thats what im
trying to do. move from sql 2000 to sql 7.
And this demotion option. where is this ? I dont recall ever seeing such an
option in DTS or in script out objects.
Please let me know.
Thanks.
-Kunal.
"Dejan Sarka" wrote:

> Transfer whatewer possible with DTS, and then:
> 1. Try to script objects (procedures etc.) on 7.0 and run the script on
> 2000.
> 2. Check options when transferring - allow demotion (shrink) of the numeric
> data types.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "kunalap" <kunalap@.discussions.microsoft.com> wrote in message
> news:F788F92D-7DE0-44BF-9578-6A4F51C7E9DC@.microsoft.com...
> in
> the
> asked
> using
> it
> of
> 2000
> going
>
>
sql

Migrating data from SQL 7 to 2000 ? Possible ? DTS ?

Hello,
I have a weird task. After going to SQL 2000 from SQL 7 on a
weekend...after a few days...incase of problems... I might be asked to
revert back to SQL 7 with those upgrade SQL 2000 databases.
Now, I know this cannot be done, but is there a possibility of using DTS to
do this ?
I have tried DTS from SQL 2000 to SQL 7... out of the 6 databases it worked
for 4. Although it does seem kind of "unclean" way to do it.
With the 5th database, I can move everything but procedures. Some of the
procedures refer to columns in user tables which don't exist.
With the 6th database, it seems max precision is set to 38 on SQL 2000 but
only 28 on SQL 7. Which is why this one isn't working at all.
Any suggestions ? Over should I just mark this "unclean" way of going from
SQL 2000 to SQL 7 as NOT an option ?
Thanks in advance.
-Kunal.The best way as per me is to take a backup of your SQL 7 and restore in your
SQL 2000.
By doing this you will not loose and objects. DTS will not copy all the
objects and the references that are available to the TABLE.
Hope this will solve your problem
thanks and regards
Chandra
"kunalap" wrote:
> Hello,
> I have a weird task. After going to SQL 2000 from SQL 7 on a
> weekend...after a few days...incase of problems... I might be asked to
> revert back to SQL 7 with those upgrade SQL 2000 databases.
> Now, I know this cannot be done, but is there a possibility of using DTS to
> do this ?
> I have tried DTS from SQL 2000 to SQL 7... out of the 6 databases it worked
> for 4. Although it does seem kind of "unclean" way to do it.
> With the 5th database, I can move everything but procedures. Some of the
> procedures refer to columns in user tables which don't exist.
> With the 6th database, it seems max precision is set to 38 on SQL 2000 but
> only 28 on SQL 7. Which is why this one isn't working at all.
> Any suggestions ? Over should I just mark this "unclean" way of going from
> SQL 2000 to SQL 7 as NOT an option ?
> Thanks in advance.
> -Kunal.|||Im sorry I made a mistake in the title of the topic.
Its SQL 2000 to SQL 7.
The matter of the topic explains the problem correctly.
Its SQL 2000 to SQL 7 not the other way around. Any thoughts ?
Thanks.
-Kunal.
"Chandra" wrote:
> The best way as per me is to take a backup of your SQL 7 and restore in your
> SQL 2000.
> By doing this you will not loose and objects. DTS will not copy all the
> objects and the references that are available to the TABLE.
> Hope this will solve your problem
> thanks and regards
> Chandra
>
> "kunalap" wrote:
> > Hello,
> >
> > I have a weird task. After going to SQL 2000 from SQL 7 on a
> > weekend...after a few days...incase of problems... I might be asked to
> > revert back to SQL 7 with those upgrade SQL 2000 databases.
> >
> > Now, I know this cannot be done, but is there a possibility of using DTS to
> > do this ?
> > I have tried DTS from SQL 2000 to SQL 7... out of the 6 databases it worked
> > for 4. Although it does seem kind of "unclean" way to do it.
> >
> > With the 5th database, I can move everything but procedures. Some of the
> > procedures refer to columns in user tables which don't exist.
> >
> > With the 6th database, it seems max precision is set to 38 on SQL 2000 but
> > only 28 on SQL 7. Which is why this one isn't working at all.
> >
> > Any suggestions ? Over should I just mark this "unclean" way of going from
> > SQL 2000 to SQL 7 as NOT an option ?
> >
> > Thanks in advance.
> >
> > -Kunal.|||DTS the objects from the 2000 to the 7.0 server.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"kunalap" <kunalap@.discussions.microsoft.com> schrieb im Newsbeitrag
news:7A7185B2-8DDC-4FB7-929A-F32AB5D5703E@.microsoft.com...
> Im sorry I made a mistake in the title of the topic.
> Its SQL 2000 to SQL 7.
> The matter of the topic explains the problem correctly.
> Its SQL 2000 to SQL 7 not the other way around. Any thoughts ?
> Thanks.
> -Kunal.
> "Chandra" wrote:
>> The best way as per me is to take a backup of your SQL 7 and restore in
>> your
>> SQL 2000.
>> By doing this you will not loose and objects. DTS will not copy all the
>> objects and the references that are available to the TABLE.
>> Hope this will solve your problem
>> thanks and regards
>> Chandra
>>
>> "kunalap" wrote:
>> > Hello,
>> >
>> > I have a weird task. After going to SQL 2000 from SQL 7 on a
>> > weekend...after a few days...incase of problems... I might be asked
>> > to
>> > revert back to SQL 7 with those upgrade SQL 2000 databases.
>> >
>> > Now, I know this cannot be done, but is there a possibility of using
>> > DTS to
>> > do this ?
>> > I have tried DTS from SQL 2000 to SQL 7... out of the 6 databases it
>> > worked
>> > for 4. Although it does seem kind of "unclean" way to do it.
>> >
>> > With the 5th database, I can move everything but procedures. Some of
>> > the
>> > procedures refer to columns in user tables which don't exist.
>> >
>> > With the 6th database, it seems max precision is set to 38 on SQL 2000
>> > but
>> > only 28 on SQL 7. Which is why this one isn't working at all.
>> >
>> > Any suggestions ? Over should I just mark this "unclean" way of going
>> > from
>> > SQL 2000 to SQL 7 as NOT an option ?
>> >
>> > Thanks in advance.
>> >
>> > -Kunal.|||Yes thats what I am trying. But doesn't it seem kind of "unclean" way to
maintain production data. Also the 2 problems Im having with 2 of the
databases in DTS'ing.
Any advise ?
"Jens Sü�meyer" wrote:
> DTS the objects from the 2000 to the 7.0 server.
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "kunalap" <kunalap@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:7A7185B2-8DDC-4FB7-929A-F32AB5D5703E@.microsoft.com...
> > Im sorry I made a mistake in the title of the topic.
> > Its SQL 2000 to SQL 7.
> >
> > The matter of the topic explains the problem correctly.
> >
> > Its SQL 2000 to SQL 7 not the other way around. Any thoughts ?
> >
> > Thanks.
> >
> > -Kunal.
> >
> > "Chandra" wrote:
> >
> >> The best way as per me is to take a backup of your SQL 7 and restore in
> >> your
> >> SQL 2000.
> >>
> >> By doing this you will not loose and objects. DTS will not copy all the
> >> objects and the references that are available to the TABLE.
> >>
> >> Hope this will solve your problem
> >>
> >> thanks and regards
> >> Chandra
> >>
> >>
> >> "kunalap" wrote:
> >>
> >> > Hello,
> >> >
> >> > I have a weird task. After going to SQL 2000 from SQL 7 on a
> >> > weekend...after a few days...incase of problems... I might be asked
> >> > to
> >> > revert back to SQL 7 with those upgrade SQL 2000 databases.
> >> >
> >> > Now, I know this cannot be done, but is there a possibility of using
> >> > DTS to
> >> > do this ?
> >> > I have tried DTS from SQL 2000 to SQL 7... out of the 6 databases it
> >> > worked
> >> > for 4. Although it does seem kind of "unclean" way to do it.
> >> >
> >> > With the 5th database, I can move everything but procedures. Some of
> >> > the
> >> > procedures refer to columns in user tables which don't exist.
> >> >
> >> > With the 6th database, it seems max precision is set to 38 on SQL 2000
> >> > but
> >> > only 28 on SQL 7. Which is why this one isn't working at all.
> >> >
> >> > Any suggestions ? Over should I just mark this "unclean" way of going
> >> > from
> >> > SQL 2000 to SQL 7 as NOT an option ?
> >> >
> >> > Thanks in advance.
> >> >
> >> > -Kunal.
>
>|||did you use the bakup technique (the one that i posted earlier) here?
thanks and regards
Chandra
"kunalap" wrote:
> Yes thats what I am trying. But doesn't it seem kind of "unclean" way to
> maintain production data. Also the 2 problems Im having with 2 of the
> databases in DTS'ing.
> Any advise ?
> "Jens Sü�meyer" wrote:
> > DTS the objects from the 2000 to the 7.0 server.
> >
> >
> > HTH, Jens Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --
> >
> >
> >
> > "kunalap" <kunalap@.discussions.microsoft.com> schrieb im Newsbeitrag
> > news:7A7185B2-8DDC-4FB7-929A-F32AB5D5703E@.microsoft.com...
> > > Im sorry I made a mistake in the title of the topic.
> > > Its SQL 2000 to SQL 7.
> > >
> > > The matter of the topic explains the problem correctly.
> > >
> > > Its SQL 2000 to SQL 7 not the other way around. Any thoughts ?
> > >
> > > Thanks.
> > >
> > > -Kunal.
> > >
> > > "Chandra" wrote:
> > >
> > >> The best way as per me is to take a backup of your SQL 7 and restore in
> > >> your
> > >> SQL 2000.
> > >>
> > >> By doing this you will not loose and objects. DTS will not copy all the
> > >> objects and the references that are available to the TABLE.
> > >>
> > >> Hope this will solve your problem
> > >>
> > >> thanks and regards
> > >> Chandra
> > >>
> > >>
> > >> "kunalap" wrote:
> > >>
> > >> > Hello,
> > >> >
> > >> > I have a weird task. After going to SQL 2000 from SQL 7 on a
> > >> > weekend...after a few days...incase of problems... I might be asked
> > >> > to
> > >> > revert back to SQL 7 with those upgrade SQL 2000 databases.
> > >> >
> > >> > Now, I know this cannot be done, but is there a possibility of using
> > >> > DTS to
> > >> > do this ?
> > >> > I have tried DTS from SQL 2000 to SQL 7... out of the 6 databases it
> > >> > worked
> > >> > for 4. Although it does seem kind of "unclean" way to do it.
> > >> >
> > >> > With the 5th database, I can move everything but procedures. Some of
> > >> > the
> > >> > procedures refer to columns in user tables which don't exist.
> > >> >
> > >> > With the 6th database, it seems max precision is set to 38 on SQL 2000
> > >> > but
> > >> > only 28 on SQL 7. Which is why this one isn't working at all.
> > >> >
> > >> > Any suggestions ? Over should I just mark this "unclean" way of going
> > >> > from
> > >> > SQL 2000 to SQL 7 as NOT an option ?
> > >> >
> > >> > Thanks in advance.
> > >> >
> > >> > -Kunal.
> >
> >
> >|||Transfer whatewer possible with DTS, and then:
1. Try to script objects (procedures etc.) on 7.0 and run the script on
2000.
2. Check options when transferring - allow demotion (shrink) of the numeric
data types.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"kunalap" <kunalap@.discussions.microsoft.com> wrote in message
news:F788F92D-7DE0-44BF-9578-6A4F51C7E9DC@.microsoft.com...
> Yes thats what I am trying. But doesn't it seem kind of "unclean" way to
> maintain production data. Also the 2 problems Im having with 2 of the
> databases in DTS'ing.
> Any advise ?
> "Jens Süßmeyer" wrote:
> > DTS the objects from the 2000 to the 7.0 server.
> >
> >
> > HTH, Jens Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --
> >
> >
> >
> > "kunalap" <kunalap@.discussions.microsoft.com> schrieb im Newsbeitrag
> > news:7A7185B2-8DDC-4FB7-929A-F32AB5D5703E@.microsoft.com...
> > > Im sorry I made a mistake in the title of the topic.
> > > Its SQL 2000 to SQL 7.
> > >
> > > The matter of the topic explains the problem correctly.
> > >
> > > Its SQL 2000 to SQL 7 not the other way around. Any thoughts ?
> > >
> > > Thanks.
> > >
> > > -Kunal.
> > >
> > > "Chandra" wrote:
> > >
> > >> The best way as per me is to take a backup of your SQL 7 and restore
in
> > >> your
> > >> SQL 2000.
> > >>
> > >> By doing this you will not loose and objects. DTS will not copy all
the
> > >> objects and the references that are available to the TABLE.
> > >>
> > >> Hope this will solve your problem
> > >>
> > >> thanks and regards
> > >> Chandra
> > >>
> > >>
> > >> "kunalap" wrote:
> > >>
> > >> > Hello,
> > >> >
> > >> > I have a weird task. After going to SQL 2000 from SQL 7 on a
> > >> > weekend...after a few days...incase of problems... I might be
asked
> > >> > to
> > >> > revert back to SQL 7 with those upgrade SQL 2000 databases.
> > >> >
> > >> > Now, I know this cannot be done, but is there a possibility of
using
> > >> > DTS to
> > >> > do this ?
> > >> > I have tried DTS from SQL 2000 to SQL 7... out of the 6 databases
it
> > >> > worked
> > >> > for 4. Although it does seem kind of "unclean" way to do it.
> > >> >
> > >> > With the 5th database, I can move everything but procedures. Some
of
> > >> > the
> > >> > procedures refer to columns in user tables which don't exist.
> > >> >
> > >> > With the 6th database, it seems max precision is set to 38 on SQL
2000
> > >> > but
> > >> > only 28 on SQL 7. Which is why this one isn't working at all.
> > >> >
> > >> > Any suggestions ? Over should I just mark this "unclean" way of
going
> > >> > from
> > >> > SQL 2000 to SQL 7 as NOT an option ?
> > >> >
> > >> > Thanks in advance.
> > >> >
> > >> > -Kunal.
> >
> >
> >|||Chandra,
SQL 2000 backup cannot be applied on SQL 7.
Hence the whole DTS approach.
"Chandra" wrote:
> did you use the bakup technique (the one that i posted earlier) here?
> thanks and regards
> Chandra
>
> "kunalap" wrote:
> > Yes thats what I am trying. But doesn't it seem kind of "unclean" way to
> > maintain production data. Also the 2 problems Im having with 2 of the
> > databases in DTS'ing.
> >
> > Any advise ?
> >
> > "Jens Sü�meyer" wrote:
> >
> > > DTS the objects from the 2000 to the 7.0 server.
> > >
> > >
> > > HTH, Jens Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --
> > >
> > >
> > >
> > > "kunalap" <kunalap@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > news:7A7185B2-8DDC-4FB7-929A-F32AB5D5703E@.microsoft.com...
> > > > Im sorry I made a mistake in the title of the topic.
> > > > Its SQL 2000 to SQL 7.
> > > >
> > > > The matter of the topic explains the problem correctly.
> > > >
> > > > Its SQL 2000 to SQL 7 not the other way around. Any thoughts ?
> > > >
> > > > Thanks.
> > > >
> > > > -Kunal.
> > > >
> > > > "Chandra" wrote:
> > > >
> > > >> The best way as per me is to take a backup of your SQL 7 and restore in
> > > >> your
> > > >> SQL 2000.
> > > >>
> > > >> By doing this you will not loose and objects. DTS will not copy all the
> > > >> objects and the references that are available to the TABLE.
> > > >>
> > > >> Hope this will solve your problem
> > > >>
> > > >> thanks and regards
> > > >> Chandra
> > > >>
> > > >>
> > > >> "kunalap" wrote:
> > > >>
> > > >> > Hello,
> > > >> >
> > > >> > I have a weird task. After going to SQL 2000 from SQL 7 on a
> > > >> > weekend...after a few days...incase of problems... I might be asked
> > > >> > to
> > > >> > revert back to SQL 7 with those upgrade SQL 2000 databases.
> > > >> >
> > > >> > Now, I know this cannot be done, but is there a possibility of using
> > > >> > DTS to
> > > >> > do this ?
> > > >> > I have tried DTS from SQL 2000 to SQL 7... out of the 6 databases it
> > > >> > worked
> > > >> > for 4. Although it does seem kind of "unclean" way to do it.
> > > >> >
> > > >> > With the 5th database, I can move everything but procedures. Some of
> > > >> > the
> > > >> > procedures refer to columns in user tables which don't exist.
> > > >> >
> > > >> > With the 6th database, it seems max precision is set to 38 on SQL 2000
> > > >> > but
> > > >> > only 28 on SQL 7. Which is why this one isn't working at all.
> > > >> >
> > > >> > Any suggestions ? Over should I just mark this "unclean" way of going
> > > >> > from
> > > >> > SQL 2000 to SQL 7 as NOT an option ?
> > > >> >
> > > >> > Thanks in advance.
> > > >> >
> > > >> > -Kunal.
> > >
> > >
> > >|||You mean script out on 2000 and then apply on 7, right ? coz thats what im
trying to do. move from sql 2000 to sql 7.
And this demotion option. where is this ? I dont recall ever seeing such an
option in DTS or in script out objects.
Please let me know.
Thanks.
-Kunal.
"Dejan Sarka" wrote:
> Transfer whatewer possible with DTS, and then:
> 1. Try to script objects (procedures etc.) on 7.0 and run the script on
> 2000.
> 2. Check options when transferring - allow demotion (shrink) of the numeric
> data types.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "kunalap" <kunalap@.discussions.microsoft.com> wrote in message
> news:F788F92D-7DE0-44BF-9578-6A4F51C7E9DC@.microsoft.com...
> > Yes thats what I am trying. But doesn't it seem kind of "unclean" way to
> > maintain production data. Also the 2 problems Im having with 2 of the
> > databases in DTS'ing.
> >
> > Any advise ?
> >
> > "Jens Sü�meyer" wrote:
> >
> > > DTS the objects from the 2000 to the 7.0 server.
> > >
> > >
> > > HTH, Jens Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --
> > >
> > >
> > >
> > > "kunalap" <kunalap@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > news:7A7185B2-8DDC-4FB7-929A-F32AB5D5703E@.microsoft.com...
> > > > Im sorry I made a mistake in the title of the topic.
> > > > Its SQL 2000 to SQL 7.
> > > >
> > > > The matter of the topic explains the problem correctly.
> > > >
> > > > Its SQL 2000 to SQL 7 not the other way around. Any thoughts ?
> > > >
> > > > Thanks.
> > > >
> > > > -Kunal.
> > > >
> > > > "Chandra" wrote:
> > > >
> > > >> The best way as per me is to take a backup of your SQL 7 and restore
> in
> > > >> your
> > > >> SQL 2000.
> > > >>
> > > >> By doing this you will not loose and objects. DTS will not copy all
> the
> > > >> objects and the references that are available to the TABLE.
> > > >>
> > > >> Hope this will solve your problem
> > > >>
> > > >> thanks and regards
> > > >> Chandra
> > > >>
> > > >>
> > > >> "kunalap" wrote:
> > > >>
> > > >> > Hello,
> > > >> >
> > > >> > I have a weird task. After going to SQL 2000 from SQL 7 on a
> > > >> > weekend...after a few days...incase of problems... I might be
> asked
> > > >> > to
> > > >> > revert back to SQL 7 with those upgrade SQL 2000 databases.
> > > >> >
> > > >> > Now, I know this cannot be done, but is there a possibility of
> using
> > > >> > DTS to
> > > >> > do this ?
> > > >> > I have tried DTS from SQL 2000 to SQL 7... out of the 6 databases
> it
> > > >> > worked
> > > >> > for 4. Although it does seem kind of "unclean" way to do it.
> > > >> >
> > > >> > With the 5th database, I can move everything but procedures. Some
> of
> > > >> > the
> > > >> > procedures refer to columns in user tables which don't exist.
> > > >> >
> > > >> > With the 6th database, it seems max precision is set to 38 on SQL
> 2000
> > > >> > but
> > > >> > only 28 on SQL 7. Which is why this one isn't working at all.
> > > >> >
> > > >> > Any suggestions ? Over should I just mark this "unclean" way of
> going
> > > >> > from
> > > >> > SQL 2000 to SQL 7 as NOT an option ?
> > > >> >
> > > >> > Thanks in advance.
> > > >> >
> > > >> > -Kunal.
> > >
> > >
> > >
>
>