When you get below error during accessing the DTS packages :
SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)
Then to resolve this issue follow the below processes.
1-Install Sql Server 2000 DTS Designer Components
Download from here. This is the most current version as of the writing of this article.
2-Install Sql Server 2005 backward compatibility components
Download from here. This is the most current version as of the writing of this article.
This is available with the installation media for SQL Server 2008.
3-Verify your path environment variable. The SQL 2000 path should be placed in the string prior to the SQL 2008 variable. As a sample, this is what mine looks like.
%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program Files\Microsoft SQL Server\90\DTS\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\
*You can access the environment variables in Windows 7 by: Right Click My Computer -> Properties -> Click Advanced System Settings -> Click Environment Variables -> Scroll to “Path” -> Click “Path” and then click Edit…
4-SSMS – Manually Copy Files
DLL Files to copy
semsfc.dll, sqlgui.dll, sqlsvc.dll
Source
%Program Files%\Microsoft SQL Server\80\Tools\Binn\
Destination
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\
RLL Files to copy
semsfc.rll, sqlgui.rll, sqlsvc.rll
Source
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\
Destination
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\
5-BIDS Manually Copy Files
DLL Files to copy
semsfc.dll, sqlgui.dll, sqlsvc.dll
Source
%Program Files%\Microsoft SQL Server\80\Tools\Binn\
Destination
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\
RLL Files to copy
semsfc.rll, sqlgui.rll, sqlsvc.rll
Source
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\
Destination
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\
I had to create the Resources Subdirectory
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\%lang_id%\
The last step was not applicable in my case since the file version already matched
stardds.dll File version: 2000.80.2151.0
Showing posts with label DTS. Show all posts
Showing posts with label DTS. Show all posts
Monday, August 2, 2010
Friday, December 25, 2009
Move all DTS Packages between servers.
We can move all DTS packages from one server to another server through given two methods:
Method1:
If you save your dts packages under local packages, they are stored in the msdb.dbo.sysdtspackages table.
1) create a new dts package with connections to the two servers
2) create a datapump between the connection
3) because you cannot select the sysdtspackages table from the pull-down list, you need to use disconnected edit to manually modify the datapump to set the SourceTable and the DestinationTable to [msdb].[dbo].[sysdtspackages]
4) now close the disconnected edit
5) choose properties of the datapump and let dts perform the auto-mapping in the transfermations tab.
6) run it!
This will transform the rows in sysdtspackages from one server to the other, and PRESTO! All the packages have been moved, including all the version history!You could also set up a source query to select specific packages based on the name, or only the current version based on the create date.
Method 2:
Quite easy step!!!!!!1
--restore msdb db with msdbnew name from from source server to the target server then run below script:
insert into sysdtspackages ( name, id, versionid, description, categoryid, createdate, owner, packagedata, owner_sid, packagetype)
select name, id, versionid, description, categoryid, createdate, owner, packagedata, owner_sid, packagetype from msdbnew..sysdtspackages /* this is msdb from old server*/
Method1:
If you save your dts packages under local packages, they are stored in the msdb.dbo.sysdtspackages table.
1) create a new dts package with connections to the two servers
2) create a datapump between the connection
3) because you cannot select the sysdtspackages table from the pull-down list, you need to use disconnected edit to manually modify the datapump to set the SourceTable and the DestinationTable to [msdb].[dbo].[sysdtspackages]
4) now close the disconnected edit
5) choose properties of the datapump and let dts perform the auto-mapping in the transfermations tab.
6) run it!
This will transform the rows in sysdtspackages from one server to the other, and PRESTO! All the packages have been moved, including all the version history!You could also set up a source query to select specific packages based on the name, or only the current version based on the create date.
Method 2:
Quite easy step!!!!!!1
--restore msdb db with msdbnew name from from source server to the target server then run below script:
insert into sysdtspackages ( name, id, versionid, description, categoryid, createdate, owner, packagedata, owner_sid, packagetype)
select name, id, versionid, description, categoryid, createdate, owner, packagedata, owner_sid, packagetype from msdbnew..sysdtspackages /* this is msdb from old server*/
Subscribe to:
Posts (Atom)