Sql 2000 DTS packages on Sql 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Sql 2000 DTS packages on Sql 2005

Author

Topic

svicky9
Posting Yak Master

232 Posts

Posted - 2007-05-24 : 06:10:03

Hi Friends

When i open the DTS packages in the Management Studio for editing the DTS packages

I get an error prompt asking me for Installing the Sql Server 2000 Designer Components. I already have the Sql Server 2000 Client tools installed in my machine. Should i still install the designer components...Wont there be a crash in the Client tools side???

cheers

Vic

http://vicdba.blogspot.com

svicky9
Posting Yak Master

232 Posts

Posted - 2007-05-24 : 06:45:10

If it helps this is the error message i am getting

TITLE: Object Explorer
------------------------------

This 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 Components" to use this feature.

If the "SQL Server 2000 DTS Designer Components" download is already installed, there might be a problem with the compatibility of icons persisted in this DTS package. See the following KB article for more details: http://support.microsoft.com/kb/917406 (Microsoft.SqlServer.DtsObjectExplorerUI)

------------------------------
BUTTONS:

OK
------------------------------

Vic

http://vicdba.blogspot.com

patshaw
Posting Yak Master

177 Posts

Posted - 2007-06-04 : 05:46:00

You need to install the SQL Server 2000 Designer Components to edit any SQL Server 2000 DTS Packages in 2005 Management Studio. They cannot be edited without this add-on.

aravindt77
Posting Yak Master

120 Posts

Posted - 2007-06-05 : 07:11:56

/*
Author : Aravind
Date of Creation : 04-06-2007
-----------------------------------------------------------------------------
Modified :

-----------------------------------------------------------------------------
Input : (i) PortalID - int
(2) UserID - int
(3) ShorFieldName in DyReg_Question Table - Varchar(255) HardCoded
-----------------------------------------------------------------------------
OutPut : RecordSet - 2
(1) Returns the QuestionOptions (DynReg_QuestionOptions Table)
Based on ShortFieldName('subscriptionlist') in DyReg_Question table
(2) Returns the RecordSet with col as Response - from DynReg_QuestionResponse Table
only if Corresponding Response exist against QuestionOption-- Return Response Field
-----------------------------------------------------------------------------
To Run :-
-- Exec JobsonUser_GetuserListValues 0,1026,'subscriptionlist'

-----------------------------------------------------------------------------
*/

CREATE PROCEDURE JobsonUser_GetuserListValues (
@p_PortalID int ,
@p_UserID int,
@p_SubscriptionText varchar(255))
AS
BEGIN

SET NOCOUNT ON

--- Variable Declarations

Declare @userID int
Declare @portalID int
Declare @DynamicQuestionID varchar(255)
Declare @Question varchar(255)
Declare @i int
Declare @count int
Declare @loop int

Declare @SubscriptionList Table (PortalID int ,
Question Varchar(255) ,
DynamicQuestionID varchar(255),
QuestionOption Varchar(255),
SortOrder int )

---- Variable Declarations Ends

-- Initializations

Set @i = 0
Set @count = 1
Set @loop = 0

-- Getting User Level Info And Portal ID

-- Step 1

SELECT
@userID = a.UserID ,
@portalID = b.PortalID
FROM
Users a
LEFT OUTER JOIN
UserPortals b
ON
a.UserId = b.UserID
WHERE
a.UserID = @p_UserID
And b.PortalID = @p_PortalID

--------------------------

---- Step 2 Listing The QuestionOption ( from DynReg_QuestionOption Table)
---- Which Relates to ShortFieldName = 'subscription' in DynReg_Question Table

INSERT INTO @SubscriptionList (PortalID,Question , DynamicQuestionID , QuestionOption , SortOrder)
SELECT
q.PortalID,q.Question,qo.DynamicQuestionID,qo.QuestionOption ,qo.SortOrder
From
DynamicRegistration_Question q
RIGHT OUTER JOIN
DynamicRegistration_QuestionOption qo
ON
q.DynamicQuestionId = qo.DynamicQuestionId
WHERE
q.ShortFieldName = @p_SubscriptionText
And qo.Inactive > -1
ORDER BY qo.SortOrder

SELECT QuestionOption FROM @SubscriptionList
---------------------
---------------Step 3

SELECT a.Response
FROM
(
SELECT
q.DynamicQuestionID , q.Question , q.ShortFieldName , qr.Response,qr.UserID
FROM
DynamicRegistration_Question q
RIGHT OUTER JOIN
DynamicRegistration_QuestionResponse qr
ON
q.DynamicQuestionID = qr.DynamicQuestionID
WHERE
qr.UserID = @p_UserID
And q.PortalID = @p_PortalID
And q.ShortFieldName = @p_SubscriptionText --'subscriptionlist'
)a
LEFT OUTER JOIN
(
SELECT
QuestionOption,SortOrder
FROM
@SubscriptionList
)b
ON
a.Response = b.QuestionOption
ORDER BY b.SortOrder

RETURN

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources