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
No comments:
Post a Comment