embedding a DTS package which contains a 'Copy SQL Server Objects Task'

Hi,

I am getting an error when doing the above.

I create a new SSIS package, drag in an Execute DTS 2000 Package Task, select and embed a DTS package which consists only one one task (as above), and then change the source & destination details (svr + user/pwd). Then when I go to the Copy tab, I get the following error when I hit Select Objects, to view the objects which the embedded DTS package should copy:

SQL-DMO error 21776: general error.

On further inspection, none of the objects selected for copy within the atomic/original DTS package, remain selected for copy within the embedded DTS package.

I have googled to search for an answer to this one, but to no avail. Any ideas would be greatly welcomed.

Thanks,

Tamim.

Off the top of my head I am not sure...but I wonder if there is more to this or perhaps what the real goal is?

I ask because if your only dealing with one transfer task from a dts package, then why not just create a new SSIS package using the new ssis transfer tasks? Prehaps you have 100s of said DTS packages, and you need to keep them going rather than rebuild all. However, we should always keep in mind that while the Execute DTS 2000 Package Task is nice for 'keeping things running' from the old version, over time you will want to rebuild any DTS pacakge as a 'native' SSIS 2005 pacakge allows you to do a lot more AND DTS is considered a deprecated technology, which does mean its fading form the picure. If you want you can read more about that here.

http://msdn2.microsoft.com/en-us/library/ms403408.aspx

Hope that helps.

|||

'...if your only dealing with one transfer task from a dts package, then why not just create a new SSIS package using the new ssis transfer tasks?...'

Sure. And that's exactly what I did, thus reducing the problem to being merely academic - but it's still there....

Thanks Craig,

embedding a DTS package and logging/error reporting

Hi All,

When the embedded DTS package fails at runtime, and logging has been enabled for the package (and all log events selected for reporting on for the package and the task), the DTS error (i.e. any meaningful errors) are not thrown up to/caught by the SSIS/outer level. All you get is something like:

COMException - error returned from a call to a COM component.

Does anyone have any comments &/or know to get errors thrown from within an embedded DTS package, thrown up to the wrapping SSIS package?

Cheers,

Tamim.

Hi Tamim,

Try configuring your DTS package (using the old SQL 2000 designer) to log to an msdb database (it can be a SQL 2005 msdb). Then you will find the DTS messages logged to the tables eg sysdtspackagelog and sysdtssteplog.

Good luck.

Mike

embedding a DTS package and logging/error reporting

Hi All,

When the embedded DTS package fails at runtime, and logging has been enabled for the package (and all log events selected for reporting on for the package and the task), the DTS error (i.e. any meaningful errors) are not thrown up to/caught by the SSIS/outer level. All you get is something like:

COMException - error returned from a call to a COM component.

Does anyone have any comments &/or know to get errors thrown from within an embedded DTS package, thrown up to the wrapping SSIS package?

Cheers,

Tamim.

Hi Tamim,

Try configuring your DTS package (using the old SQL 2000 designer) to log to an msdb database (it can be a SQL 2005 msdb). Then you will find the DTS messages logged to the tables eg sysdtspackagelog and sysdtssteplog.

Good luck.

Mike

sql

Embedded 2000 Package Question/Issue

Hi All,

I am facing with a very intersting question here.

I have an embedded DTS 2000 package inside my SSIS 2005 Package. This DTS 2000 package just execute a transfer from a DB2 server to the 2005 server. Its a very simple transfer, without any transformation ( just column to column ).

Well, when I edit the DTS 2000 and save it, the package size increases about 100/200kb. It sounds like the package version ( the same as we have in the 2000 environment ). But i dont know how deactivate this "auto-increase".

Anyone can help me with this auto-increase? or just explain me what is happening?

Today I have packages with 9mb and just 3 or 4 embedded DTS 2000 packages inside.

Thanks in advance.

Thiago

Hello All,

Any answer?

After 3 months migrating DTS packages to SSIS packages we have some packages with 16mb of size. The SSIS packages which has incorporated 2000 packages, increase size every time we change anything on the 2000 package.

We are considering redesign those packages but we want to make sure that we are not missing any configuration.

Thanks for your attention.

Regards,

Thiago

|||DTS 2000 keeps every version you've saved, so it keeps growing each time. Unfortunately, unlike Enterprise Manager in SQL 2000, SSIS provides no easy way to manage the versions and cleanup the old versions. I've opened a bug for this issue in product defect database.

As a (long) workaround, you may save the DTS package as a file (right click the DTS designer surface, select Save As - this will save the latest version only), then switching the task to use this file instead of embedded package, finally use "load DTS 2000 package internally" in task editor. This way you'll get rid of all the old versions of DTS 2000 package.

Embedded 2000 Package Question/Issue

Hi All,

I am facing with a very intersting question here.

I have an embedded DTS 2000 package inside my SSIS 2005 Package. This DTS 2000 package just execute a transfer from a DB2 server to the 2005 server. Its a very simple transfer, without any transformation ( just column to column ).

Well, when I edit the DTS 2000 and save it, the package size increases about 100/200kb. It sounds like the package version ( the same as we have in the 2000 environment ). But i dont know how deactivate this "auto-increase".

Anyone can help me with this auto-increase? or just explain me what is happening?

Today I have packages with 9mb and just 3 or 4 embedded DTS 2000 packages inside.

Thanks in advance.

Thiago

Hello All,

Any answer?

After 3 months migrating DTS packages to SSIS packages we have some packages with 16mb of size. The SSIS packages which has incorporated 2000 packages, increase size every time we change anything on the 2000 package.

We are considering redesign those packages but we want to make sure that we are not missing any configuration.

Thanks for your attention.

Regards,

Thiago

|||DTS 2000 keeps every version you've saved, so it keeps growing each time. Unfortunately, unlike Enterprise Manager in SQL 2000, SSIS provides no easy way to manage the versions and cleanup the old versions. I've opened a bug for this issue in product defect database.

As a (long) workaround, you may save the DTS package as a file (right click the DTS designer surface, select Save As - this will save the latest version only), then switching the task to use this file instead of embedded package, finally use "load DTS 2000 package internally" in task editor. This way you'll get rid of all the old versions of DTS 2000 package.sql

emailing an attachment from DTS

I have a DTS package that runs 4x a day and generates an excel spreadsheet, renames that spreadsheet with a datetime stamp and then places it into a folder on our network. I have been asked to email that spreadsheet to someone everytime the package runs.

My question is what would be the best way to handle this and how do I ensure that whatever process I define grabs the correct file? The folder that the Excel file is being placed into has multiple files in it. Is there a way to tell SQL Server which file to grab and email?

Any thoughts or suggestions would be greatly appreciated! Thanks!

Frank

You can use Gert's xp:

http://sqldev.net/xp/xpsmtp.htmsql

E-Mail User When StoredProc Fails

Hi,

I want to e-mail a user when a Stored Proc fails, what is the best way to do this? I was going to create a DTS package or is this too complicated?

Also, the Stored Proc inserts data from one table to another, I would like to use Transactions so that if this fails it rolls back to where it was, I'm not sure of the best way to go about this. Could anyone possibly point me in the right direction? Here's a copy of some of the stored procedure to give an idea of what I am doing:

-- insert data into proper tables with extract date added
INSERT INTO tbl_Surgery
SELECT
SurgeryKey,
GETDATE(),
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey,
@.practiceCode --SUBSTRING(SurgeryKey,PATINDEX('%.%',SurgeryKey)+1, 5)
FROM tbl_SurgeryIn

INSERT INTO tbl_SurgerySlot
SELECT
SurgerySlotKey,
GETDATE(),
SurgeryKey,
Length,
Deleted,
StartTime,
RestrictionDays,
Label,
IsRestricted,
@.practiceCode
FROM tbl_SurgerySlotIn

INSERT INTO tbl_Appointment
SELECT
AppointmentKey,
GETDATE(),
SurgerySlotKey,
PatientKey,
Cancelled,
Continuation,
Deleted,
Reason,
DateMade
FROM tbl_AppointmentIn

-- empty input tables
DELETE FROM tbl_SurgeryIn
DELETE FROM tbl_SurgerySlotIn
DELETE FROM tbl_AppointmentIn

Any help would me very much appreciated,

ThanksSomething like this should work:

CREATE PROCEDURE ProcName

AS

BEGIN TRANSACTION transaction_1

DECLARE @.error_handle VARCHAR(255)

-- insert data into proper tables with extract date added
INSERT INTO tbl_Surgery
SELECT
SurgeryKey,
GETDATE(),
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey,
@.practiceCode --SUBSTRING(SurgeryKey,PATINDEX('%.%',SurgeryKey)+1, 5)
FROM tbl_SurgeryIn

IF @.@.ERROR <> 0
BEGIN
SELECT @.error_handle = 'ProcName::Failure on tbl_Surgery insert.'
GOTO error_handle
END

INSERT INTO tbl_SurgerySlot
SELECT
SurgerySlotKey,
GETDATE(),
SurgeryKey,
Length,
Deleted,
StartTime,
RestrictionDays,
Label,
IsRestricted,
@.practiceCode
FROM tbl_SurgerySlotIn

IF @.@.ERROR <> 0
BEGIN
SELECT @.error_handle = 'ProcName::Failure on tbl_SurgerySlot insert.'
GOTO error_handle
END

INSERT INTO tbl_Appointment
SELECT
AppointmentKey,
GETDATE(),
SurgerySlotKey,
PatientKey,
Cancelled,
Continuation,
Deleted,
Reason,
DateMade
FROM tbl_AppointmentIn

IF @.@.ERROR <> 0
BEGIN
SELECT @.error_handle = 'ProcName::Failure on tbl_Appointment insert.'
GOTO error_handle
END

-- empty input tables
DELETE FROM tbl_SurgeryIn

IF @.@.ERROR <> 0
BEGIN
SELECT @.error_handle = 'ProcName::Failure on tbl_Surgery delete.'
GOTO error_handle
END

DELETE FROM tbl_SurgerySlotIn

IF @.@.ERROR <> 0
BEGIN
SELECT @.error_handle = 'ProcName::Failure on tbl_SurgerySlotIn delete.'
GOTO error_handle
END

DELETE FROM tbl_AppointmentIn

IF @.@.ERROR <> 0
BEGIN
SELECT @.error_handle = 'ProcName::Failure on tbl_AppointmentIn delete.'
GOTO error_handle
END

end_procedure:
COMMIT TRANSACTION transaction_1 --Commits transactions if no errors occurred.
RETURN 0 --Indicates succcess.

error_handle
ROLLBACK TRANSACTION transaction_1
RAISERROR(@.error_handle,16,1)
EXEC xp_sendmail 'user@.mail.com',@.error_handle
RETURN 1

Alternately, if you are running this from a job, you can strip out the xp_sendmail and just have it send email on failure. The RETURN 1 with the RAISERROR will indicate failure.|||I would strongly suggest creating a job to run the stored procedure, and having the job email you on failure. It is easy to do, and relatively foolproof!

-PatP|||Thanks, that's great, I'll give it a go. So the RAISERROR and RETURN 1 are just a way of letting SQL know that the procedure has failed??

The only thing I'm confused/worried about is the best place to put the BEGIN TRANSACTION and the end_procedure code. The stored procedure I've inherited has rather a lot of BEGINS/ENDS so I'm worried about confusing it, here's a copy of the original, any indication you could give me would really help:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE sproc_48hrAccess_Upload

AS

SET DATEFORMAT dmy

DECLARE @.practiceCode char(5)
DECLARE @.server varchar(255)
DECLARE @.inPath varchar(255)
DECLARE @.archiveBase varchar(255)
DECLARE @.archivePath varchar(255)
DECLARE @.zipPath varchar(255)
DECLARE @.cmdshell varchar(255)
DECLARE @.result int
DECLARE @.date varchar(10)

SET @.server = 'MURDOCH'
SET @.inPath = 'E:\48hrAccess\48hrDataIn\'
SET @.archiveBase = 'E:\48hrAccess\48hrDataArchive\'
SET @.zipPath = 'C:\Progra~1\WinZip\' --C:\Program Files\Winzip\
SET @.date = CONVERT(varchar(2),DATEPART(dd,GETDATE()))+CONVERT (varchar(2),DATEPART(mm,GETDATE()))+CONVERT(char(4 ),DATEPART(yy,GETDATE()))

-- upload for each practice in tbl_Practice
DECLARE allPractices CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT practiceCode FROM tbl_Practice
OPEN allPractices
FETCH NEXT FROM allPractices INTO @.practiceCode

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.archivePath = @.archiveBase+@.practiceCode+'\'
-- copy files into archive folder
SET @.cmdshell = 'MOVE '+@.inPath+'48hr_'+@.practiceCode+'_'+@.date+'.zip '+@.archivePath
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

-- unzip file
SET @.cmdshell = @.zipPath+'WZUNZIP -ybc -o -sPASSWORD '+@.archivePath+'48hr_'+@.practiceCode+'_'+@.date+'.z ip '+@.archivePath
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

-- copy data files into upload tables
SET @.cmdshell = 'ECHO ** BEGIN Upload '+CONVERT(varchar,GETDATE())+' ******************************* >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

SET @.cmdshell = 'ECHO xv_Surgery.dat >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT
SET @.cmdshell = 'bcp GMS_48hrAccess..tbl_SurgeryIn in '+@.archivePath+'xv_Surgery.dat -n -V65 -t"||" -r"|||\n" -S'+@.server+' -T >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

SET @.cmdshell = 'ECHO xv_SurgerySlot.dat >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT
SET @.cmdshell = 'bcp GMS_48hrAccess..tbl_SurgerySlotIn in '+@.archivePath+'xv_SurgerySlot.dat -n -V65 -t"||" -r"|||\n" -S'+@.server+' -T >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

SET @.cmdshell = 'ECHO xv_Appointment.dat >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT
SET @.cmdshell = 'bcp GMS_48hrAccess..tbl_AppointmentIn in '+@.archivePath+'xv_Appointment.dat -n -V65 -t"||" -r"|||\n" -S'+@.server+' -T >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

-- clean up
SET @.cmdshell = 'DEL /F '+@.archivePath+'*.dat '+@.archivePath+'48hrAccess.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

-- update tbl_SurgerySlotDescription (will fire trigger if new labels appear)
INSERT INTO tbl_SurgerySlotDescription
SELECT DISTINCT @.practiceCode, ssi.Label, ssi.AutoFillMessage, ssi.IsBookable, null
FROM tbl_SurgerySlotIn ssi
WHERE SUBSTRING(ssi.SurgerySlotKey,PATINDEX('%.%',ssi.Su rgerySlotKey)+1,5) = @.practiceCode
AND ssi.Label NOT IN (
SELECT Label
FROM tbl_SurgerySlotDescription
WHERE PracticeCode = @.practiceCode
)

-- insert data into proper tables with extract date added
INSERT INTO tbl_Surgery
SELECT
SurgeryKey,
GETDATE(),
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey,
@.practiceCode --SUBSTRING(SurgeryKey,PATINDEX('%.%',SurgeryKey)+1, 5)
FROM tbl_SurgeryIn

INSERT INTO tbl_SurgerySlot
SELECT
SurgerySlotKey,
GETDATE(),
SurgeryKey,
Length,
Deleted,
StartTime,
RestrictionDays,
Label,
IsRestricted,
@.practiceCode
FROM tbl_SurgerySlotIn

INSERT INTO tbl_Appointment
SELECT
AppointmentKey,
GETDATE(),
SurgerySlotKey,
PatientKey,
Cancelled,
Continuation,
Deleted,
Reason,
DateMade
FROM tbl_AppointmentIn

-- empty input tables
DELETE FROM tbl_SurgeryIn
DELETE FROM tbl_SurgerySlotIn
DELETE FROM tbl_AppointmentIn

FETCH NEXT FROM allPractices INTO @.practiceCode
END

CLOSE allPractices
DEALLOCATE allPractices

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Email Task

In my DTS package I tried to make an email task and it works if I execute it manualy, but if I schedule it I get the following error:

DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_16 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_16 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnProgress: DTSStep_DTSDataPumpTask_2; 8 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 8 DTSRun OnFinish: DTSStep_DTSDataPumpTask_2 DTSRun OnStart: DTSStep_DTSSendMailTask_1 DTSRun OnError: DTSStep_DTSSendMailTask_1, Error = -2147220352 (80040480) Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273: MAPI Logon failed. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147220352 (80040480); Provider Error: 0 (0) Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273: MAPI Logon failed. Erro... Process Exit Code 1. The step failed.

What could it be?SQLAgent service account should be used to set up the mail profile.|||Got that to work, Thanks,

But now I am getting into another error:
Executed as user: UPSAPP\SQLService. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_16 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_16 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnError: DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005) Error string: Failure creating file. Error source: Microsoft JET Database Engine Help file: Help context: 5003436 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -329978796 (EC54EC54) Error string: Failure creating file. Error source: Microsoft JET Database Engine Help file: Help context: 5003436 DTSRun OnFinish: DTSStep_DTSDataPumpTask_2 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

This is happends when I am exporting a table into .xls file on the network drive.
Any ideas? Why is it using Microsoft JET Database Engine from SQL Server? Why can't it create a file, and why is it creating a file, i already have the file there.

Thanks in advance.|||So, if you're trying to append to the file, you just say so in your task desgn. The reason you're getting the error is because upon an attempt to append to the file it looks for it, and it can't find it because this account (SQLAgent service account) doesn't have permission to even read that share. So, it tries to create one (by design), and failes due to the same permission issue.|||So how would I tell the network admin to add that account for permission to that drive?|||First, confirm that permission is the issue by logging in with SQLAgent service account to demonstrate to yourself and then to your network admin that this is the case.|||And remember, when you execute a DTS package, it runs using your account. When you schedule it as a job, it runs as SQLAgent. This can confuse things when you are trying to debug permissions.

EM DTS Designer ODBC source as a file DSN doesnt work!

Hi All there!

I am quite new in MS SQL administration so let me explain how it work
on Your instances of SQL Servers.
We have several DTS packages on our server, all of them managed on
some station which have seriously hardvare problem. So we wolud like
to catch two problems at one time and decided to develop systematic
way of DTS manipulation.
One of several aspects of this operation would be migration from
system ODBC data sources definitions into file ODBC sources ( .dsn
files) in order to make them easier to manage ( backup for example,
and even reusability on other workstations). All .dsn files should be
located on some network resource (\\server\\directory\...) which would
be set as default ODBC directory in ODBC administrator on management
station.
When I begin to do so, then it apears that EM DTS Designer does not
remember the path to the DSN files ( for example on design panel I
chose file dsn and by browse button point at the certain .dsn file,
and then after DTS save the path disapears).

Do You use this facility ( file .dsn) in DTS EM Designer, or maybe MS
has it treated as usless, and nobody wants to use this?

Regards
K"kakaz" <kakazpl@.yahoo.com> wrote in message
news:efd3e15d.0401140621.2b4eb48b@.posting.google.c om...
> Hi All there!
> I am quite new in MS SQL administration so let me explain how it work
> on Your instances of SQL Servers.
> We have several DTS packages on our server, all of them managed on
> some station which have seriously hardvare problem. So we wolud like
> to catch two problems at one time and decided to develop systematic
> way of DTS manipulation.
> One of several aspects of this operation would be migration from
> system ODBC data sources definitions into file ODBC sources ( .dsn
> files) in order to make them easier to manage ( backup for example,
> and even reusability on other workstations). All .dsn files should be
> located on some network resource (\\server\\directory\...) which would
> be set as default ODBC directory in ODBC administrator on management
> station.
> When I begin to do so, then it apears that EM DTS Designer does not
> remember the path to the DSN files ( for example on design panel I
> chose file dsn and by browse button point at the certain .dsn file,
> and then after DTS save the path disapears).
> Do You use this facility ( file .dsn) in DTS EM Designer, or maybe MS
> has it treated as usless, and nobody wants to use this?
> Regards
> K

You may wish to consider adapting your approach slightly, and modifying the
properties of the Connection objects within the packages directly. I
commonly start a package with a dynamic properties task which assigns global
variable values to various connection properties (server names, database
names, file names etc.) Since you can pass global variables to DTSRUN.EXE,
it's straightforward to build a command line with the variable values you
need; if passwords are required, then DTSRUNUI.EXE will encrypt the command
line for you.

Alternatively, you can use Execute SQL tasks within the package to retrieve
the values from a table and assign them to the global variables first. This
is handy because you can then store all connection properties for all
packages on all servers in one central database, then use @.@.SERVERNAME,
DB_NAME() etc. to find the appropriate values for the current execution.

No doubt there are other possible approaches, but this one works well enough
for me.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<400591b3_3@.news.bluewin.ch>...
> You may wish to consider adapting your approach slightly, and modifying the
> properties of the Connection objects within the packages directly.

Hi!
Thanks for your suggestion but this is no option for us, because of
organisational purposes, mainly because I am only administrator of
this bounch of DTSes and our developer not exactly agree with your
idea. I dont want to change his codes...

But do You have similar problem with DTS Designer? Would like to
chceck this for me on Your SQL Server?

I am not sure if it is problem with EM DTS Designer or with our
environment because we have for example some conflicts with ODBC IBM
Client Access for AS400 libreries so operation system with this
combination ( MS SQL Server + IBM Client Access ) is not stable but
rather hacked to be stable ( we have to make replacemement in some of
system libraries, mainly by Visual Studio installation which resolve
conflict).

est Regards
K|||kakazpl@.yahoo.com (kakaz) wrote in message news:<efd3e15d.0401142307.7499a374@.posting.google.com>...
> "Simon Hayes" <sql@.hayes.ch> wrote in message news:<400591b3_3@.news.bluewin.ch>...
> > You may wish to consider adapting your approach slightly, and modifying the
> > properties of the Connection objects within the packages directly.
> Hi!
> Thanks for your suggestion but this is no option for us, because of
> organisational purposes, mainly because I am only administrator of
> this bounch of DTSes and our developer not exactly agree with your
> idea. I dont want to change his codes...
>
> But do You have similar problem with DTS Designer? Would like to
> chceck this for me on Your SQL Server?
> I am not sure if it is problem with EM DTS Designer or with our
> environment because we have for example some conflicts with ODBC IBM
> Client Access for AS400 libreries so operation system with this
> combination ( MS SQL Server + IBM Client Access ) is not stable but
> rather hacked to be stable ( we have to make replacemement in some of
> system libraries, mainly by Visual Studio installation which resolve
> conflict).
> est Regards
> K

I tested quickly, and it does seem that the designer always looks in
the same folder (the local Data Sources folder on my workstation),
whatever I do. Even setting a different default folder in the
workstation Data Sources (ODBC) Control Panel applet doesn't affect
this, so I would guess it's a 'feature' of the designer.

But hopefully this should just be a minor inconvenience when you're
designing. When you have a working package, it's much easier to change
the connections programmatically from a script (inside or outside the
package) than it is to use the designer. The designer may force you to
update transformations which don't need to be updated, for example.
But I don't have much experience of using the Client Access providers
in DTS, so there may be some extra considerations.

Simon