Showing posts with label packages. Show all posts
Showing posts with label packages. Show all posts

Sunday, March 25, 2012

backing up DTS packages stored on a SQL Server

How do I do that?
Then how do I restored them?
Thanks!
DanutThis is a multi-part message in MIME format.
--=_NextPart_000_0332_01C35132.9804EC60
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
They are stored in the msdb database. Backing it up backs up the =packages. Restore it and you restore the packages.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Danut" <danutzp1@.hotmail.com> wrote in message =news:OKvB1LVUDHA.2020@.TK2MSFTNGP11.phx.gbl...
How do I do that?
Then how do I restored them?
Thanks!
Danut
--=_NextPart_000_0332_01C35132.9804EC60
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

They are stored in the msdb =database. Backing it up backs up the packages. Restore it and you restore =the packages.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Danut" wrote =in message news:OKvB1LVUDHA.2020=@.TK2MSFTNGP11.phx.gbl...How do I do that?Then how do I restored them?Thanks!Danut

--=_NextPart_000_0332_01C35132.9804EC60--|||Danut,
See
Backing Up Packages
http://www.sqldts.com/default.aspx?6,105,202,0,1
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Danut" <danutzp1@.hotmail.com> wrote in message
news:OKvB1LVUDHA.2020@.TK2MSFTNGP11.phx.gbl...
> How do I do that?
> Then how do I restored them?
> Thanks!
> Danut
>|||Thanks Tom!
Thanks Dinesh!
Danut
"Danut" <danutzp1@.hotmail.com> wrote in message
news:OKvB1LVUDHA.2020@.TK2MSFTNGP11.phx.gbl...
> How do I do that?
> Then how do I restored them?
> Thanks!
> Danut
>
Posted by Adler at 8:03 PM 0 comments
Labels: , , , , , , , , , ,

Backing Up DTS packages

I am looking for a way to back up local DTS packages. Are they stored in
msdb? Has anyone dealt with the backup issue for DTS packages?
Larry Menzin
American Techsystems Corp.
I save them out to disk as .dts file and store them in Visual Source Safe.
Greg Jackson
PDX, Oregon
|||Hi,
All the DTS packages will be stored in MSDB. So backing up the MSDB database
will be enough.
See the below link for more info:-
http://www.databasejournal.com/featu...le.php/1462601
Thanks
Hari
SQL Server MVP
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:75673569-040D-458F-B62D-1AB1EA76D285@.microsoft.com...
>I am looking for a way to back up local DTS packages. Are they stored in
> msdb? Has anyone dealt with the backup issue for DTS packages?
> --
> Larry Menzin
> American Techsystems Corp.
|||Yes, packages are in msdb. So just make sure you have backup of your msdb database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:75673569-040D-458F-B62D-1AB1EA76D285@.microsoft.com...
>I am looking for a way to back up local DTS packages. Are they stored in
> msdb? Has anyone dealt with the backup issue for DTS packages?
> --
> Larry Menzin
> American Techsystems Corp.
|||Also have a look at DTSBackup 2000
http://www.sqldts.com/default.aspx?242
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:75673569-040D-458F-B62D-1AB1EA76D285@.microsoft.com...
>I am looking for a way to back up local DTS packages. Are they stored in
> msdb? Has anyone dealt with the backup issue for DTS packages?
> --
> Larry Menzin
> American Techsystems Corp.
Posted by Adler at 8:02 PM 0 comments
Labels: , , , , , , , , , , , , , , ,

Backing Up DTS packages

I am looking for a way to back up local DTS packages. Are they stored in
msdb? Has anyone dealt with the backup issue for DTS packages?
Larry Menzin
American Techsystems Corp.I save them out to disk as .dts file and store them in Visual Source Safe.
Greg Jackson
PDX, Oregon|||Hi,
All the DTS packages will be stored in MSDB. So backing up the MSDB database
will be enough.
See the below link for more info:-
http://www.databasejournal.com/feat...cle.php/1462601
Thanks
Hari
SQL Server MVP
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:75673569-040D-458F-B62D-1AB1EA76D285@.microsoft.com...
>I am looking for a way to back up local DTS packages. Are they stored in
> msdb? Has anyone dealt with the backup issue for DTS packages?
> --
> Larry Menzin
> American Techsystems Corp.|||Yes, packages are in msdb. So just make sure you have backup of your msdb da
tabase.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:75673569-040D-458F-B62D-1AB1EA76D285@.microsoft.com...
>I am looking for a way to back up local DTS packages. Are they stored in
> msdb? Has anyone dealt with the backup issue for DTS packages?
> --
> Larry Menzin
> American Techsystems Corp.|||Also have a look at DTSBackup 2000
http://www.sqldts.com/default.aspx?242
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:75673569-040D-458F-B62D-1AB1EA76D285@.microsoft.com...
>I am looking for a way to back up local DTS packages. Are they stored in
> msdb? Has anyone dealt with the backup issue for DTS packages?
> --
> Larry Menzin
> American Techsystems Corp.
Posted by Adler at 8:02 PM 0 comments
Labels: , , , , , , , , , , , , , , ,

Backing Up DTS packages

I am looking for a way to back up local DTS packages. Are they stored in
msdb? Has anyone dealt with the backup issue for DTS packages?
--
Larry Menzin
American Techsystems Corp.I save them out to disk as .dts file and store them in Visual Source Safe.
Greg Jackson
PDX, Oregon|||Hi,
All the DTS packages will be stored in MSDB. So backing up the MSDB database
will be enough.
See the below link for more info:-
http://www.databasejournal.com/features/mssql/article.php/1462601
Thanks
Hari
SQL Server MVP
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:75673569-040D-458F-B62D-1AB1EA76D285@.microsoft.com...
>I am looking for a way to back up local DTS packages. Are they stored in
> msdb? Has anyone dealt with the backup issue for DTS packages?
> --
> Larry Menzin
> American Techsystems Corp.|||Yes, packages are in msdb. So just make sure you have backup of your msdb database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:75673569-040D-458F-B62D-1AB1EA76D285@.microsoft.com...
>I am looking for a way to back up local DTS packages. Are they stored in
> msdb? Has anyone dealt with the backup issue for DTS packages?
> --
> Larry Menzin
> American Techsystems Corp.|||Also have a look at DTSBackup 2000
http://www.sqldts.com/default.aspx?242
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:75673569-040D-458F-B62D-1AB1EA76D285@.microsoft.com...
>I am looking for a way to back up local DTS packages. Are they stored in
> msdb? Has anyone dealt with the backup issue for DTS packages?
> --
> Larry Menzin
> American Techsystems Corp.
Posted by Adler at 8:01 PM 0 comments
Labels: , , , , , , , , , , , , , , ,

Backing up DTS and SQL jobs....

I know If I store the DTS packages in SQL Server it will be stored in the
msdb database.
Also all the job scheduling information is stored in the msdb database.
Even though I am taking backup of system databases, I would like to know if
is there any way I can export all the DTS packages and jobs in any different
way. This is only an additional extra precaution incase If I am not able to
restore the system database in the event of disaster.
I am able to open the DTS packages individually and store as .dts file. It
is a time consuming process.
I am using SQL 2K.
Thanks,
J Smith
For DTS backup, I use a neat little program called, "DTS Backup 2000." It's
freeware.
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:uMDTg4gZFHA.3048@.TK2MSFTNGP14.phx.gbl...
>I know If I store the DTS packages in SQL Server it will be stored in the
> msdb database.
> Also all the job scheduling information is stored in the msdb database.
> Even though I am taking backup of system databases, I would like to know
> if
> is there any way I can export all the DTS packages and jobs in any
> different
> way. This is only an additional extra precaution incase If I am not able
> to
> restore the system database in the event of disaster.
> I am able to open the DTS packages individually and store as .dts file. It
> is a time consuming process.
> I am using SQL 2K.
> Thanks,
> J Smith
>
|||Free Backup Software from Darren Green is available at www.sqldts.com
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:uMDTg4gZFHA.3048@.TK2MSFTNGP14.phx.gbl...
>I know If I store the DTS packages in SQL Server it will be stored in the
> msdb database.
> Also all the job scheduling information is stored in the msdb database.
> Even though I am taking backup of system databases, I would like to know
> if
> is there any way I can export all the DTS packages and jobs in any
> different
> way. This is only an additional extra precaution incase If I am not able
> to
> restore the system database in the event of disaster.
> I am able to open the DTS packages individually and store as .dts file. It
> is a time consuming process.
> I am using SQL 2K.
> Thanks,
> J Smith
>
sql
Posted by Adler at 8:01 PM 0 comments
Labels: , , , , , , , , , , , , , , ,

Backing up DTS and SQL jobs....

I know If I store the DTS packages in SQL Server it will be stored in the
msdb database.
Also all the job scheduling information is stored in the msdb database.
Even though I am taking backup of system databases, I would like to know if
is there any way I can export all the DTS packages and jobs in any different
way. This is only an additional extra precaution incase If I am not able to
restore the system database in the event of disaster.
I am able to open the DTS packages individually and store as .dts file. It
is a time consuming process.
I am using SQL 2K.
Thanks,
J SmithFor DTS backup, I use a neat little program called, "DTS Backup 2000." It's
freeware.
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:uMDTg4gZFHA.3048@.TK2MSFTNGP14.phx.gbl...
>I know If I store the DTS packages in SQL Server it will be stored in the
> msdb database.
> Also all the job scheduling information is stored in the msdb database.
> Even though I am taking backup of system databases, I would like to know
> if
> is there any way I can export all the DTS packages and jobs in any
> different
> way. This is only an additional extra precaution incase If I am not able
> to
> restore the system database in the event of disaster.
> I am able to open the DTS packages individually and store as .dts file. It
> is a time consuming process.
> I am using SQL 2K.
> Thanks,
> J Smith
>|||Free Backup Software from Darren Green is available at www.sqldts.com
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:uMDTg4gZFHA.3048@.TK2MSFTNGP14.phx.gbl...
>I know If I store the DTS packages in SQL Server it will be stored in the
> msdb database.
> Also all the job scheduling information is stored in the msdb database.
> Even though I am taking backup of system databases, I would like to know
> if
> is there any way I can export all the DTS packages and jobs in any
> different
> way. This is only an additional extra precaution incase If I am not able
> to
> restore the system database in the event of disaster.
> I am able to open the DTS packages individually and store as .dts file. It
> is a time consuming process.
> I am using SQL 2K.
> Thanks,
> J Smith
>
Posted by Adler at 8:00 PM 0 comments
Labels: , , , , , , , , , , , , , ,

Backing up DTS and SQL jobs....

I know If I store the DTS packages in SQL Server it will be stored in the
msdb database.
Also all the job scheduling information is stored in the msdb database.
Even though I am taking backup of system databases, I would like to know if
is there any way I can export all the DTS packages and jobs in any different
way. This is only an additional extra precaution incase If I am not able to
restore the system database in the event of disaster.
I am able to open the DTS packages individually and store as .dts file. It
is a time consuming process.
I am using SQL 2K.
Thanks,
J SmithFor DTS backup, I use a neat little program called, "DTS Backup 2000." It's
freeware.
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:uMDTg4gZFHA.3048@.TK2MSFTNGP14.phx.gbl...
>I know If I store the DTS packages in SQL Server it will be stored in the
> msdb database.
> Also all the job scheduling information is stored in the msdb database.
> Even though I am taking backup of system databases, I would like to know
> if
> is there any way I can export all the DTS packages and jobs in any
> different
> way. This is only an additional extra precaution incase If I am not able
> to
> restore the system database in the event of disaster.
> I am able to open the DTS packages individually and store as .dts file. It
> is a time consuming process.
> I am using SQL 2K.
> Thanks,
> J Smith
>|||Free Backup Software from Darren Green is available at www.sqldts.com
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:uMDTg4gZFHA.3048@.TK2MSFTNGP14.phx.gbl...
>I know If I store the DTS packages in SQL Server it will be stored in the
> msdb database.
> Also all the job scheduling information is stored in the msdb database.
> Even though I am taking backup of system databases, I would like to know
> if
> is there any way I can export all the DTS packages and jobs in any
> different
> way. This is only an additional extra precaution incase If I am not able
> to
> restore the system database in the event of disaster.
> I am able to open the DTS packages individually and store as .dts file. It
> is a time consuming process.
> I am using SQL 2K.
> Thanks,
> J Smith
>
Posted by Adler at 7:59 PM 0 comments
Labels: , , , , , , , , , , , , , , ,

Friday, February 24, 2012

Avoiding clear text passwords and editing of packages

Hi!

I have an SQL Server where only a group of sysadmins have access to install DTSX packages. Those DTSX packages are developed by another team that does not have access to the production SQL Server. They use their own SQL Server.

In order to make it as simple as possible to install these packages by the sysadmins, I suggested the use of configuration files. The files are associated with the job that executes the package and all that has to be done to install the package is copy it to the file system or import it into the SQL Server. Developers use their configuration file, sysadmins user theirs. Nothing new here.

The problem is that some of the packages have to access some old systems and we cannot use integrated authentication. We have to use SQL authentication and therefore specify a user account and password in the connection string. If this is stored in the configuration file, it is available in clear text! If I store the configuration in the package itself using ProtectSensitiveWithPassword protection level, the sysadmins will have to edit every DTSX package to reset the connections to the production environment (the developers always send them with their development configurations) and I don't want that. If I store it in a SQL Server database, it seems the sysadmins also have to edit the package to point the package configuration to the correct database and set the configuration filter.

Another solution is to store the credentials in clear text in the configuration file but set the file system permissions on that file so only the account that executes the package can read them (this is what I'm implementing if nothing better comes up...)

Is there any other way to do this? Am I doing something wrong?

Thanks in advance.


Rui Covelo wrote:

Hi!
Another solution is to store the credentials in clear text in the configuration file but set the file system permissions on that file so only the account that executes the package can read them (this is what I'm implementing if nothing better comes up...)

Is there any other way to do this? Am I doing something wrong?

Thanks in advance.

I don't see any other way. Unless that you want to save you the time of oppening each package to change the credentials in every package; I think you are better dealing with the passwords in plain text and securing the place where they live. BTW, you can use Env. variables or a SQL table to place those connection strings if that makes you feel more secure.

This is just my opinion and not an entry for the 'best practice' book

Rafael Salas

|||

I don't see using configuration files is not that big issue that people seem to think of it. So what if they use clear text, all that means is that you need secure them correctly. You probably have a password store already. Does it have password complexity? Does it have group membership? Do passwords expire? Can you manage access centrally? If not it would seem obvious that AD is going to do a better job, so use it :)

The issue I think is more about how people have viewed file permissions in the past, or rather the lack of them. Use them correctly and will be more secure than a single password embedded in a package, and don't forget those jobs that also have the password too. AD is much harder to circumvent any controls around it. People just don't use file permissions, because it is "hard", well get it right and it is more secure, so what sounds better?

|||

I would re-iterate what Darren has said.

At some point someone somewhere needs to know what some password is to open up access to a securable. If that password is the password of a Windows AD account then so much the better. Security is one of the main things that AD is there for.

-Jamie

|||Thanks for your answers!

Posted by Adler at 12:52 AM 0 comments
Labels: , , , , , , , , , , , , , , , , , ,

Avoid multiple packages to run concurrently

Is there any way to avoid multiple packages to run concurrently on the same SSIS server ?

There is nothing built in to allow this (unless you can somehow restrict the number of instances of dtexec.exe that are allowed). You would have to build functionality into all your packages that (e.g.) places a zero-byte file onto the hard drive in a particular place when it starts and removes it when its finished. Each package should check to see if this file exists before it starts.

You could also check jaegd's suggestion here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1811745&SiteID=1

-Jamie

|||Thanks for your answer Jamie.
Posted by Adler at 12:49 AM 0 comments
Labels: , , , , , , , , , , ,
Subscribe to: Posts (Atom)