DTS packages migration from MSSQL 2K to MSSQL server 2005

Hello
I need to migrate all my dts packages from sql server 2000 to sql
server 2005 so I can access these packages under Legacy->DTS in
Microsft sql server 2005 Management studio.
How can I do this?
Regards,
Lena
Lena, privet
Under Legacy folder, there is Data Transformation Services folder, right
click on it then Import Package File ( I assumed you saved the dts package
as a file)
"Lena" <lenaglozman@.yahoo.com> wrote in message
news:e01c20ab-e0a9-4104-ad48-90f70fd91a5d@.47g2000hsb.googlegroups.com...
> Hello
> I need to migrate all my dts packages from sql server 2000 to sql
> server 2005 so I can access these packages under Legacy->DTS in
> Microsft sql server 2005 Management studio.
> How can I do this?
> Regards,
> Lena
|||I already did that.
Now when I try to open the package, I'm getting error:
"The package cannot be edited.
SQL Server 2000 DTS Designer Components are required to edit DTS
packages. Install the special Web download, "SQL server 2000 DTS
designer Com[ponents" to use this feature".
I can not find this download on Microsoft's site.
On Mar 6, 1:21Xam, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Lena, privet
> Under Legacy folder, there is XData Transformation Services folder, right
> click on it then Import Package File ( I assumed you saved the dts package
> as a file)
> "Lena" <lenagloz...@.yahoo.com> wrote in message
> news:e01c20ab-e0a9-4104-ad48-90f70fd91a5d@.47g2000hsb.googlegroups.com...
>
>
>
> - Show quoted text -

DTS packages migration from MSSQL 2K to MSSQL server 2005

Hello
I need to migrate all my dts packages from sql server 2000 to sql
server 2005 so I can access these packages under Legacy->DTS in
Microsft sql server 2005 Management studio.
How can I do this?
Regards,
LenaLena, privet
Under Legacy folder, there is Data Transformation Services folder, right
click on it then Import Package File ( I assumed you saved the dts package
as a file)
"Lena" <lenaglozman@.yahoo.com> wrote in message
news:e01c20ab-e0a9-4104-ad48-90f70fd91a5d@.47g2000hsb.googlegroups.com...
> Hello
> I need to migrate all my dts packages from sql server 2000 to sql
> server 2005 so I can access these packages under Legacy->DTS in
> Microsft sql server 2005 Management studio.
> How can I do this?
> Regards,
> Lena|||I already did that.
Now when I try to open the package, I'm getting error:
"The package cannot be edited.
SQL Server 2000 DTS Designer Components are required to edit DTS
packages. Install the special Web download, "SQL server 2000 DTS
designer Com[ponents" to use this feature".
I can not find this download on Microsoft's site.
On Mar 6, 1:21=A0am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Lena, privet
> Under Legacy folder, there is =A0Data Transformation Services folder, righ=t
> click on it then Import Package File ( I assumed you saved the dts package=
> as a file)
> "Lena" <lenagloz...@.yahoo.com> wrote in message
> news:e01c20ab-e0a9-4104-ad48-90f70fd91a5d@.47g2000hsb.googlegroups.com...
>
> > Hello
> > I need to migrate all my dts packages from sql server 2000 to sql
> > server 2005 so I can access these packages under Legacy->DTS in
> > Microsft sql server 2005 =A0Management studio.
> > How can I do this?
> > Regards,
> > Lena- Hide quoted text -
> - Show quoted text -

DTS Package Migration Error

I've been migrating SQL 2000 DTS packages into SQL 2005 using the Migrate DTS 2000 Package wizard in Visual Studio 2005. All packages migrate fine except one which throws out the following error:

exception of type 'Microsoft.SQLServer.DTS.MigrationWizard.HelperUtility.DTSMWException was thrown

No other supporting information is given to help diagnose the problem. Has anyone else come across this error before? Looking at past threads, I've checked the DTS package for leading or trailing spaces which might cause an issue and there are none.

Thanks.

me too in the same position...|||

Hey i found the problem. if you are package is Incompleted take an example "doing the bulk insert from SQL Server Conn to Excel sheet but with out Defining which Table and column" package will thro error. Just check you package.

DTS Package Migration Error

I've been migrating SQL 2000 DTS packages into SQL 2005 using the Migrate DTS 2000 Package wizard in Visual Studio 2005. All packages migrate fine except one which throws out the following error:

exception of type 'Microsoft.SQLServer.DTS.MigrationWizard.HelperUtility.DTSMWException was thrown

No other supporting information is given to help diagnose the problem. Has anyone else come across this error before? Looking at past threads, I've checked the DTS package for leading or trailing spaces which might cause an issue and there are none.

Thanks.

me too in the same position...|||

Hey i found the problem. if you are package is Incompleted take an example "doing the bulk insert from SQL Server Conn to Excel sheet but with out Defining which Table and column" package will thro error. Just check you package.

DTS Package Execution Problem

Friends,
I have very strange problem.
I have a DTS Package, which migrate data from one database to another
database after some validation.
Configuration :
-Sql Server 2000
-Working on Test Database Server
-2 GB RAM
The issue is, lastly the whole migration takes only 6 - 7 hours, but
now its not compelete after 24+ hours.
--Database is same.
--DTS package is same.
If any body has any idea, please reply me.
--Points to find the exact problem.Hi
What does the packaege do? Have you checked whether your db is growing
during the execution or not? What is recovery model of the database?
"Rahul" <verma.career@.gmail.com> wrote in message
news:1189486332.650922.89980@.o80g2000hse.googlegroups.com...
> Friends,
> I have very strange problem.
> I have a DTS Package, which migrate data from one database to another
> database after some validation.
> Configuration :
> -Sql Server 2000
> -Working on Test Database Server
> -2 GB RAM
> The issue is, lastly the whole migration takes only 6 - 7 hours, but
> now its not compelete after 24+ hours.
> --Database is same.
> --DTS package is same.
> If any body has any idea, please reply me.
> --Points to find the exact problem.
>

DTS of data between two tables, same field names but differnt datatypes

I need to migrate data from one sql database to another. The second DB
is a newer version of the "old" database with mostly the same tables
and fieldnames. In order support some reporting queries in the "new"
version I needed to change the datatype of a few fields from varchar to
int(the data stored was integers already as they were lookup tables).

DTS works great except in the cases of about 10 fields which I changed
the datatypes on from varchar to int.

DTS seems to drop the data if the fieldname and datatype are not an
exact match. Is there any way to use DTS and have it copy data from a
field call subsid type varchar to a field call subsid type int?<tdmailbox@.yahoo.com> wrote in message
news:1115388735.044389.231150@.g14g2000cwa.googlegr oups.com...
>I need to migrate data from one sql database to another. The second DB
> is a newer version of the "old" database with mostly the same tables
> and fieldnames. In order support some reporting queries in the "new"
> version I needed to change the datatype of a few fields from varchar to
> int(the data stored was integers already as they were lookup tables).
> DTS works great except in the cases of about 10 fields which I changed
> the datatypes on from varchar to int.
> DTS seems to drop the data if the fieldname and datatype are not an
> exact match. Is there any way to use DTS and have it copy data from a
> field call subsid type varchar to a field call subsid type int?

This should work fine - I tested it quickly on two tables with the same
column name but the source being varchar(10) and the destination int. Using
a Transform Data Task worked correctly, and the mapping was handled
automatically.

I'm not sure what you mean by DTS seems to "drop the data". Perhaps you can
give some more information - your MSSQL version, which type of task you're
using to move the data, the DDL for the tables, any error messages etc.

Simon|||DTS copies all the columns except the ones with datatype changes. My
varchars to ints end up with empty ints in the desitnation table.

SQL 2000, DTS export, no error messages.|||I tested it again with the export wizard (I'd used the package designer
before), and it worked fine. I have no idea why it's not working for
you, unless you have a column transformation of some sort. One other
guess would be that your values are too big for an int, and you've also
set ANSI_WARNINGS OFF, which would ignore the overflow. But since
that's ON for ODBC/OLE DB by default, it's very unlikely.

You could specify a source query instead of a source table and use CAST
to force the conversion:

select col1, col2, cast(col3 as int), col4...
from dbo.SourceTable

Even if that doesn't work, you might get a clue of some sort from an
error. Finally, you could also copy the data into a staging table which
has exactly the same structure as the source table, then use SQL to
check and INSERT the data. Or just INSERT directly, if both databases
are on the same server.

Simon

DTS Migration Wizard error

I'm using SQL Server 2005 Enterprise x64 and when attempting to migrate a DTS from our SQL Server 2000 Standard x86 server I received the following error message:

DTS Migration Wizard Error

Could not load file or assembly 'Microsoft.SqlServer.Exec80PackageTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of it's dependencies. The system cannot find the file specified.

Click Abort to stop the migration of the current package.

Click Retry to retry the operation.

Click Skip to skip the migration of the current task and continue to the next task.

From the error message, it would appear something did not install or register correctly. Any idea on what is missing and how I can fix it?

Hi there,

Did you do a full SSIS install, or did you install the Migration Wizard by itself? I believe you also need choose "Legacy Components" from the setup if you choose to install the Migration Wizard without the rest of the workbench. That might be the cause of the error you're seeing.

Thanks,

~Matt

|||

I chose to install all (full) components, both server and tools, during my installation of SQL Server 2005 Evaluation Edition. I found it strange that the icon said MS Visual Studio Premier Edition -enu instead of SQL Server Business Intelligence Development Studio. I tried a repair on the MS Visual Studio Premier Edition from Add / Remove Programs but it didn't help. I then removed it using Add / Remove Programs. However, when I fire up the SQL Server 2005 Evaluation Edition setup and attempted to reinstall the tools, it said they were already installed and would not let me go any further. Is a reboot of the production server necessary for Windows 2003 Enterprise to realize I uninstalled MS Visual Studio?

Thanks for your response Matt!

|||

The Visual Studio icon will only be labeled "SQL Server Business Intelligence Development Studio" under the SQL Server folder in the start menu. Your old visual studio icons won't change (they all point to the same thing).

You shouldn't have to reboot after installing SQL Server, but if you've done a repair, I'm not sure what state that puts you in. You might want to uninstall it all and start over again.

The Migration Wizard will be looking for the Microsoft.SqlServer.Exec80PackageTask assembly in the GAC - you might want to make sure it's there. This task gets installed when you select "Legacy Components" or the workbench, so doing a full install should give you all the bits you need.

|||That is the strange part. MS Visual Studio 2005 was not previously installed on this machine; it came across when I chose to install every component of the SQL Server 2005 Eval Edition installation. And the icon in the SQL Server folder didn't say BIDS, instead it was labeled MS Visual Studio 2005. Either way, I have now uninstalled MS Visual Studio 2005 and it looks like I will need to reboot my server before it recognizes the change as it will not allow me to re-install them at this point.|||I ended up uninstalling and reinstalling all of SQL Server 2005 and components. Then to address the missing Business Intellligence projects; I finallly found the answer, In MS Visual Studio (BIDS) click on Tools - Import and Export Settings - Import Selected Environment Settings - Yes, Save my current settings and then highlight "Business Intelligence Settings" and click on finish. This will add the Business Intelligence projects to BIDS.

DTS Migration Wizard error

I'm using SQL Server 2005 Enterprise x64 and when attempting to migrate a DTS from our SQL Server 2000 Standard x86 server I received the following error message:

DTS Migration Wizard Error

Could not load file or assembly 'Microsoft.SqlServer.Exec80PackageTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of it's dependencies. The system cannot find the file specified.

Click Abort to stop the migration of the current package.

Click Retry to retry the operation.

Click Skip to skip the migration of the current task and continue to the next task.

From the error message, it would appear something did not install or register correctly. Any idea on what is missing and how I can fix it?

Hi there,

Did you do a full SSIS install, or did you install the Migration Wizard by itself? I believe you also need choose "Legacy Components" from the setup if you choose to install the Migration Wizard without the rest of the workbench. That might be the cause of the error you're seeing.

Thanks,

~Matt

|||

I chose to install all (full) components, both server and tools, during my installation of SQL Server 2005 Evaluation Edition. I found it strange that the icon said MS Visual Studio Premier Edition -enu instead of SQL Server Business Intelligence Development Studio. I tried a repair on the MS Visual Studio Premier Edition from Add / Remove Programs but it didn't help. I then removed it using Add / Remove Programs. However, when I fire up the SQL Server 2005 Evaluation Edition setup and attempted to reinstall the tools, it said they were already installed and would not let me go any further. Is a reboot of the production server necessary for Windows 2003 Enterprise to realize I uninstalled MS Visual Studio?

Thanks for your response Matt!

|||

The Visual Studio icon will only be labeled "SQL Server Business Intelligence Development Studio" under the SQL Server folder in the start menu. Your old visual studio icons won't change (they all point to the same thing).

You shouldn't have to reboot after installing SQL Server, but if you've done a repair, I'm not sure what state that puts you in. You might want to uninstall it all and start over again.

The Migration Wizard will be looking for the Microsoft.SqlServer.Exec80PackageTask assembly in the GAC - you might want to make sure it's there. This task gets installed when you select "Legacy Components" or the workbench, so doing a full install should give you all the bits you need.

|||That is the strange part. MS Visual Studio 2005 was not previously installed on this machine; it came across when I chose to install every component of the SQL Server 2005 Eval Edition installation. And the icon in the SQL Server folder didn't say BIDS, instead it was labeled MS Visual Studio 2005. Either way, I have now uninstalled MS Visual Studio 2005 and it looks like I will need to reboot my server before it recognizes the change as it will not allow me to re-install them at this point.|||I ended up uninstalling and reinstalling all of SQL Server 2005 and components. Then to address the missing Business Intellligence projects; I finallly found the answer, In MS Visual Studio (BIDS) click on Tools - Import and Export Settings - Import Selected Environment Settings - Yes, Save my current settings and then highlight "Business Intelligence Settings" and click on finish. This will add the Business Intelligence projects to BIDS.

DTS Migration wizard - solved

i am trying to use the DTS migration wizard in sql server 2005 to migrate some of the DTS packages that i have on sql server 2000.

After entering the source and destination server i get the following error:

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index (mscorlib)

Does anyone know the reson behind this?

Thanks for any help.

I've just installed Server 2005 and am getting the same message. The earlier threads refer to special characters and leading or trailing spaces. I've tried the wizard on a few packages that have nothing but letters in the name, and I get the above message. I tried repairing .NET 2.0 as well (didn't work).

What else should I/we try?

Thanks,

K

|||

Found a forum where a user clarified that NONE of the DTS packages can have a leading/trailing space. Well, one out of a hundred or so packages had a space; after I fixed that one, the wizard worked.

Find the spaces (thanks to Joseph Sack's SQL Server blog):

SELECT DISTINCT name
FROM msdb.dbo.sysdtspackages
WHERE name LIKE '% '

and I'd suggest: or name LIKE ' %'

|||

Thanks a lot.

I had one package that had a space. After i deleted the space i was able to get a little further. but when i hit finish, All the packages display "Stopped" and none gets transferred.

Thanks

DTS Migration wizard - same here

i am trying to use the DTS migration wizard in sql server 2005 to migrate some of the DTS packages that i have on sql server 2000.

After entering the source and destination server i get the following error:

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index (mscorlib)

Does anyone know the reson behind this?

Thanks for any help.

I've just installed Server 2005 and am getting the same message. The earlier threads refer to special characters and leading or trailing spaces. I've tried the wizard on a few packages that have nothing but letters in the name, and I get the above message. I tried repairing .NET 2.0 as well (didn't work).

What else should I/we try?

Thanks,

K

|||

Found a forum where a user clarified that NONE of the DTS packages can have a leading/trailing space. Well, one out of a hundred or so packages had a space; after I fixed that one, the wizard worked.

Find the spaces (thanks to Joseph Sack's SQL Server blog):

SELECT DISTINCT name
FROM msdb.dbo.sysdtspackages
WHERE name LIKE '% '

and I'd suggest: or name LIKE ' %'

|||

Thanks a lot.

I had one package that had a space. After i deleted the space i was able to get a little further. but when i hit finish, All the packages display "Stopped" and none gets transferred.

Thanks

DTS migration from ss2000 --> ss2005

hi,
I'm planning to move my existing DTS that I've made in ss2000 to ss2005.
At this moment I'm not planning to migrate existing DTS to SSIS, I
just want to use backward compatibility for now.
What's the "best practice" technique for moving ss2000 DTS --> ss2005?
ThanksPablo
Right Click on Data Transformation Services and choose Migration Wizard
"Pablo Cognito" <pcognito@.gmail.com> wrote in message
news:uXs7OOJDHHA.3660@.TK2MSFTNGP02.phx.gbl...
> hi,
> I'm planning to move my existing DTS that I've made in ss2000 to ss2005.
> At this moment I'm not planning to migrate existing DTS to SSIS, I just
> want to use backward compatibility for now.
> What's the "best practice" technique for moving ss2000 DTS --> ss2005?
> Thanks|||Sorry, firstly save the DTS as stucture storage file
secondly Right Click on Data Transformation Services and choose Import
Package File option
choose Import option
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OMMOegJDHHA.3492@.TK2MSFTNGP06.phx.gbl...
> Pablo
> Right Click on Data Transformation Services and choose Migration Wizard
>
> "Pablo Cognito" <pcognito@.gmail.com> wrote in message
> news:uXs7OOJDHHA.3660@.TK2MSFTNGP02.phx.gbl...
>> hi,
>> I'm planning to move my existing DTS that I've made in ss2000 to ss2005.
>> At this moment I'm not planning to migrate existing DTS to SSIS, I just
>> want to use backward compatibility for now.
>> What's the "best practice" technique for moving ss2000 DTS --> ss2005?
>> Thanks
>

DTS migration from ss2000 --> ss2005

hi,
I'm planning to move my existing DTS that I've made in ss2000 to ss2005.
At this moment I'm not planning to migrate existing DTS to SSIS, I
just want to use backward compatibility for now.
What's the "best practice" technique for moving ss2000 DTS --> ss2005?
ThanksPablo
Right Click on Data Transformation Services and choose Migration Wizard
"Pablo Cognito" <pcognito@.gmail.com> wrote in message
news:uXs7OOJDHHA.3660@.TK2MSFTNGP02.phx.gbl...
> hi,
> I'm planning to move my existing DTS that I've made in ss2000 to ss2005.
> At this moment I'm not planning to migrate existing DTS to SSIS, I just
> want to use backward compatibility for now.
> What's the "best practice" technique for moving ss2000 DTS --> ss2005?
> Thanks|||Sorry, firstly save the DTS as stucture storage file
secondly Right Click on Data Transformation Services and choose Import
Package File option
choose Import option
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OMMOegJDHHA.3492@.TK2MSFTNGP06.phx.gbl...
> Pablo
> Right Click on Data Transformation Services and choose Migration Wizard
>
> "Pablo Cognito" <pcognito@.gmail.com> wrote in message
> news:uXs7OOJDHHA.3660@.TK2MSFTNGP02.phx.gbl...
>

DTS Migration error message

I get the following error message when attempting to migrate DTS packages from SQL Server 2000 to SQL Server 2005:

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index (mscorlib)

Could this be it:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=357132&SiteID=1

-Jamie

DTS Migration

I try to migrate DTS package to SSIS.

Within one DTS package AAA, it calls another DTS package BBB with inner variable.

I successfully migrate the BBB DTS package.

For my AAA package, I replace the DTS_Execute_Task_2000 with Execute Package task. But how do we pass the inner variable parameter ?

There is a Execute DTS2000 Package Task that allows you to do this.

-Jamie

|||Is there a way not to use DTS2000 Package ?|||

You could call dtsrun.exe from the Execute Process Task. Not sure why you'd want to tho.

-Jamie

dts migration

Hi,

How to migrate scheduled jobs from 2000 to 2005. Is this possible by doing bcp out and bcp in system tables which stores sql agent jobs from msdb database ?

Regards

Nit

This question is not related to SSIS. I'd try another forum if I were you. https://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=19&SiteID=1

-Jamie