how to change password to dts package programmatically?

Hi,

I was wondering if it is possible to change the password on a SQL Server 2000 DTS package programatically instead of just
manually using the the interface?

I have over 150 DTS packages used for building BI data cubes and our password policy requires a password change every 60 days.

I would like to write a windows vb.net application that loops through all the DTS packages and changes the password.

Please advise.

Thanks

Chris

This is just an analysis...not an answer.

If you develop a program that loops through each dts package and changes the pwd then you must have to put the pwd into a file, which kind of defeats security.

My suggestion is to remove the policy for changing pwds on DTS packages and control security at a higher level in SQL Server.

|||

I was going to have the password be entered via a password text box by the user.

Is this even possible?

Thanks

Chris

how to change dts owner name

hi,
does anyone know how to change a dts packeage's owner name
on sql 2000?
we have individuals that creates dts packages on
production servers, and we would like to change the dts
owner name from the individual accounts into server's own
account name.
many thanks!
JJ
sp_changeobjectowner
I've never had to change a dts owner, but this is where I would look first.
John
"JJ Wang" wrote:

> hi,
> does anyone know how to change a dts packeage's owner name
> on sql 2000?
> we have individuals that creates dts packages on
> production servers, and we would like to change the dts
> owner name from the individual accounts into server's own
> account name.
> many thanks!
> JJ
>
|||Check out http://www.sqldts.com/default.aspx?212.
Hope this helps.
Dan Guzman
SQL Server MVP
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:03d001c4a74b$4ce490d0$a501280a@.phx.gbl...
> hi,
> does anyone know how to change a dts packeage's owner name
> on sql 2000?
> we have individuals that creates dts packages on
> production servers, and we would like to change the dts
> owner name from the individual accounts into server's own
> account name.
> many thanks!
> JJ
|||sp_changeobjectowner is intended for SQL Server database objects and can't
be used for DTS packages.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Cappelletti" <JohnCappelletti@.discussions.microsoft.com> wrote in
message news:7D4B53C1-5AAD-42F5-A8FE-C1DB9DB37508@.microsoft.com...[vbcol=seagreen]
> sp_changeobjectowner
> I've never had to change a dts owner, but this is where I would look
> first.
> John
>
> "JJ Wang" wrote:
|||I use:
use msdb
go
sp_reassign_dtspackageowner [@.name =] 'name',
[@.id =] 'id',
[@.newloginname =] 'newloginname'
--to find dts package id :
use msdb
go
select * from sysdtspackages
go
"JJ Wang" wrote:

> hi,
> does anyone know how to change a dts packeage's owner name
> on sql 2000?
> we have individuals that creates dts packages on
> production servers, and we would like to change the dts
> owner name from the individual accounts into server's own
> account name.
> many thanks!
> JJ
>

how to change dts owner name

hi,
does anyone know how to change a dts packeage's owner name
on sql 2000?
we have individuals that creates dts packages on
production servers, and we would like to change the dts
owner name from the individual accounts into server's own
account name.
many thanks!
JJsp_changeobjectowner
I've never had to change a dts owner, but this is where I would look first.
John
"JJ Wang" wrote:
> hi,
> does anyone know how to change a dts packeage's owner name
> on sql 2000?
> we have individuals that creates dts packages on
> production servers, and we would like to change the dts
> owner name from the individual accounts into server's own
> account name.
> many thanks!
> JJ
>|||Check out http://www.sqldts.com/default.aspx?212.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:03d001c4a74b$4ce490d0$a501280a@.phx.gbl...
> hi,
> does anyone know how to change a dts packeage's owner name
> on sql 2000?
> we have individuals that creates dts packages on
> production servers, and we would like to change the dts
> owner name from the individual accounts into server's own
> account name.
> many thanks!
> JJ|||sp_changeobjectowner is intended for SQL Server database objects and can't
be used for DTS packages.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John Cappelletti" <JohnCappelletti@.discussions.microsoft.com> wrote in
message news:7D4B53C1-5AAD-42F5-A8FE-C1DB9DB37508@.microsoft.com...
> sp_changeobjectowner
> I've never had to change a dts owner, but this is where I would look
> first.
> John
>
> "JJ Wang" wrote:
>> hi,
>> does anyone know how to change a dts packeage's owner name
>> on sql 2000?
>> we have individuals that creates dts packages on
>> production servers, and we would like to change the dts
>> owner name from the individual accounts into server's own
>> account name.
>> many thanks!
>> JJ|||I use:
use msdb
go
sp_reassign_dtspackageowner [@.name =] 'name',
[@.id =] 'id',
[@.newloginname =] 'newloginname'
--to find dts package id :
use msdb
go
select * from sysdtspackages
go
"JJ Wang" wrote:
> hi,
> does anyone know how to change a dts packeage's owner name
> on sql 2000?
> we have individuals that creates dts packages on
> production servers, and we would like to change the dts
> owner name from the individual accounts into server's own
> account name.
> many thanks!
> JJ
>|||wow, thanks to you all for the quick response, tons of
good tips here. you solved my problem!!
thank you so much!!!!
JJ
>--Original Message--
>hi,
>does anyone know how to change a dts packeage's owner
name
>on sql 2000?
>we have individuals that creates dts packages on
>production servers, and we would like to change the dts
>owner name from the individual accounts into server's own
>account name.
>many thanks!
>JJ
>.
>

How to change dts owner

It is possible to change dts owner ?
If it is possible, How to change the owner.
thanks.>--Original Message--
>It is possible to change dts owner ?
>If it is possible, How to change the owner.
>thanks.
>.
>
Try this:
http://www.sqldts.com/default.aspx?6,105,212,0,0
Regards,
Thomas
http://wwwl.sqlscripter.com|||Try this:
sp_reassign_dtspackageowner [@.name =] 'name',
[@.id =] 'id',
[@.newloginname =] 'newloginname'
Both the @.name and @.id parameters are required as a package name is not
guaranteed to be unique.
Zvi
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:25e5f01c38e11$886a4540$a601280a@.phx.gbl...
> It is possible to change dts owner ?
> If it is possible, How to change the owner.
> thanks.|||Hello
> It is possible to change dts owner ?
> If it is possible, How to change the owner.
Update msdb..sysdtspackages set owner = 'newowner' where versionid ='...'
Serge Shakhovsql

how to change dts import/export windows size

Howdy!
Does any of you good folk know how to change window size of the DTS
Import/Export Wizard window?
Darn thing is very small and inconvinient to work with, no apparent way to
change it's size,
may be there is a registry tweak or something.
Thanks in advance,
I.B.Yes, It's inconvenient. No, there is no way to change the size of it, as
far as I know. Maybe in MS SQL SERVER 2004?
Best regards,
Chuck Conover
www.TechnicalVideos.net

"Ilya Bari" <IBari@.SnappyDsl.net> wrote in message
news:4015c5fd@.news.snappydsl.net...
> Howdy!
> Does any of you good folk know how to change window size of the DTS
> Import/Export Wizard window?
> Darn thing is very small and inconvinient to work with, no apparent way to
> change it's size,
> may be there is a registry tweak or something.
> Thanks in advance,
> I.B.

How to change a local DTS package's owner or how to open a password protected package.

Hi,
In our database server (SQL Server 7.0), there are some local DTS packages,
all were password protected, and we don't know the password, the one who
create them has left the company.
And now, we want to modify them, how to do?
Thanks
FrankLocal packages are held in the msdb database table sysdtspackages. By doing a
simple "SELECT * FROM sysdtspackages" you can see the owner for each package.
Whilst you could probably use a simple UPDATE statement to change the owner,
there is an undocumented stored procedure, that appears to be purpose written
for this task, sp_reassign_dtspackageowner :
sp_reassign_dtspackageowner [@.name =] 'name',
[@.id =] 'id',
[@.newloginname =] 'newloginname'
[@.name =] 'name'
The package name.
[@.id =] 'id'
This is the uniqueidentifier for the package. A name may not necessarily be
unique.
[@.newloginname =] 'newloginname'
The new Owner name. SQL Server login example 'sa', NT Integrated example
'Domain\Username'
If you look at sp_reassign_dtspackageowner you'll notice that it not only
updates the owner text field, but it also updates the owner_sid field.
"Frank" wrote:
> Hi,
> In our database server (SQL Server 7.0), there are some local DTS packages,
> all were password protected, and we don't know the password, the one who
> create them has left the company.
> And now, we want to modify them, how to do?
> Thanks
> Frank
>
>

How to catch error and retry AFTER dts script step has ran

hello,

i am trying to figure out how to check for failure or success AFTER the script task has ran.

its a piece of cake to write script logic that runs before the task but how do i check things and decide to retry AFTER a script task has ran?

i want to check for an error after a large table replication and if it detects that there was an error i want to RETRY.

dts does not seem to have this one specific piece of functionality. am i overlooking something?alright... i am closer to figuring this out although the solution seems a bit complex.

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=6196&pg=2

the WROX book that i bought on DTS "DOES NOT EVEN COVER THIS TOPIC" . it covers reactive error handling but doesn't say a word about proactive error handling.

i have a java programming background. can anyone see this from my point of view and give me a hint?

this reminds me of using the "onStart" and "onLoad" methods of ASP programming but it doesn't seem to be quite as simple to use...

How to call a DTS package (SQL Server 2000) in VisualBasic

Dear friends,
I am implementing a COM by VisualBasic. In this COM, I want to call a DTS package on SQL server to transfer data.
How can I do that? Please help me!I found this in MSDN :)
Hope it helps!

--------------------
The following code example shows a DTS package using an
ExecutePackageTask,
through the Execute and UnInitialize methods:
--------------------

Private WithEvents mobjPkgEvents As DTS.Package
. . .
Private Sub RunPackage()
'Run the package stored in file C:\DTS_UE\TestPkg\VarPubsFields.dts.
Dim objPackage As DTS.Package2
Dim objStep As DTS.Step
Dim objTask As DTS.Task
Dim objExecPkg As DTS.ExecutePackageTask

On Error GoTo PackageError
Set objPackage = New DTS.Package
Set mobjPkgEvents = objPackage
objPackage.FailOnError = True

'Create the step and task. Specify the package to be run, and link the step to the task.
Set objStep = objPackage.Steps.New
Set objTask = objPackage.Tasks.New("DTSExecutePackageTask")
Set objExecPkg = objTask.CustomTask
With objExecPkg
.PackagePassword = "user"
.FileName = "C:\DTS_UE\TestPkg\VarPubsFields.dts"
.Name = "ExecPkgTask"
End With
With objStep
.TaskName = objExecPkg.Name
.Name = "ExecPkgStep"
.ExecuteInMainThread = True
End With
objPackage.Steps.Add objStep
objPackage.Tasks.Add objTask

'Run the package and release references.
objPackage.Execute

Set objExecPkg = Nothing
Set objTask = Nothing
Set objStep = Nothing
Set mobjPkgEvents = Nothing

objPackage.UnInitialize
End Sub|||Very thanks, sanchi!
By the way, can we call DTS packages from Stored Procedure?|||Yup, you can create a job running the DTS package and the call the job from your VB-program. Right-click your DTS-package in Enterprise Manager and Scedule it to run at some interval. Then you go to the job-management window in EM, edit the newly created job, and delete the schedule but leave the job as it is with the rest. The you can execute the following statement from your VB-app:

EXEC sp_start_job @.job_name = 'myDTSjobname'

How to build a script to extract 5% of the production Database dat

Hi, all
How to build a script to extract 5% of the production Database data to
development DB? Normally, I use DTS import/export to do that table by table
( use select top 500 * from theTable). Is there a better way? Our Production
DB is so big, I want to give some simple data to development DB for test.
Thanks for any help!
Brian
Try SELECT TOP 5 PERCENT instead.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:CFE57E50-21F6-41D9-B227-61411F066EC1@.microsoft.com...
Hi, all
How to build a script to extract 5% of the production Database data to
development DB? Normally, I use DTS import/export to do that table by table
( use select top 500 * from theTable). Is there a better way? Our
Production
DB is so big, I want to give some simple data to development DB for test.
Thanks for any help!
Brian
|||This really is not a feasible task to accomplish in any generic manner. You
will need to factor in the relationships (defined or assumed) between the
tables before you can begin to address what some random percentage of the
database really means. As a simple example, do you want want to pull a
random 5% of the rows in an order table without also pulling ALL of the
associated order_detail rows (and we'll ignore all of the other
related/required rows - customers, products, addresses, etc.).
Usually, there is some basic set of information that is required for any
system to work correctly - you'll need 100% of this information (e.g.,
you'll need all of the GL accounts before you can pull ANY activity for
these accounts).
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:CFE57E50-21F6-41D9-B227-61411F066EC1@.microsoft.com...
> Hi, all
> How to build a script to extract 5% of the production Database data to
> development DB? Normally, I use DTS import/export to do that table by
table
> ( use select top 500 * from theTable). Is there a better way? Our
Production
> DB is so big, I want to give some simple data to development DB for test.
> Thanks for any help!
> Brian

How to build a script to extract 5% of the production Database dat

Hi, all
How to build a script to extract 5% of the production Database data to
development DB? Normally, I use DTS import/export to do that table by table
( use select top 500 * from theTable). Is there a better way? Our Productio
n
DB is so big, I want to give some simple data to development DB for test.
Thanks for any help!
BrianTry SELECT TOP 5 PERCENT instead.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:CFE57E50-21F6-41D9-B227-61411F066EC1@.microsoft.com...
Hi, all
How to build a script to extract 5% of the production Database data to
development DB? Normally, I use DTS import/export to do that table by table
( use select top 500 * from theTable). Is there a better way? Our
Production
DB is so big, I want to give some simple data to development DB for test.
Thanks for any help!
Brian|||This really is not a feasible task to accomplish in any generic manner. You
will need to factor in the relationships (defined or assumed) between the
tables before you can begin to address what some random percentage of the
database really means. As a simple example, do you want want to pull a
random 5% of the rows in an order table without also pulling ALL of the
associated order_detail rows (and we'll ignore all of the other
related/required rows - customers, products, addresses, etc.).
Usually, there is some basic set of information that is required for any
system to work correctly - you'll need 100% of this information (e.g.,
you'll need all of the GL accounts before you can pull ANY activity for
these accounts).
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:CFE57E50-21F6-41D9-B227-61411F066EC1@.microsoft.com...
> Hi, all
> How to build a script to extract 5% of the production Database data to
> development DB? Normally, I use DTS import/export to do that table by
table
> ( use select top 500 * from theTable). Is there a better way? Our
Production
> DB is so big, I want to give some simple data to development DB for test.
> Thanks for any help!
> Brian

How to build a script to extract 5% of the production Database dat

Hi, all
How to build a script to extract 5% of the production Database data to
development DB? Normally, I use DTS import/export to do that table by table
( use select top 500 * from theTable). Is there a better way? Our Production
DB is so big, I want to give some simple data to development DB for test.
Thanks for any help!
BrianTry SELECT TOP 5 PERCENT instead.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:CFE57E50-21F6-41D9-B227-61411F066EC1@.microsoft.com...
Hi, all
How to build a script to extract 5% of the production Database data to
development DB? Normally, I use DTS import/export to do that table by table
( use select top 500 * from theTable). Is there a better way? Our
Production
DB is so big, I want to give some simple data to development DB for test.
Thanks for any help!
Brian|||This really is not a feasible task to accomplish in any generic manner. You
will need to factor in the relationships (defined or assumed) between the
tables before you can begin to address what some random percentage of the
database really means. As a simple example, do you want want to pull a
random 5% of the rows in an order table without also pulling ALL of the
associated order_detail rows (and we'll ignore all of the other
related/required rows - customers, products, addresses, etc.).
Usually, there is some basic set of information that is required for any
system to work correctly - you'll need 100% of this information (e.g.,
you'll need all of the GL accounts before you can pull ANY activity for
these accounts).
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:CFE57E50-21F6-41D9-B227-61411F066EC1@.microsoft.com...
> Hi, all
> How to build a script to extract 5% of the production Database data to
> development DB? Normally, I use DTS import/export to do that table by
table
> ( use select top 500 * from theTable). Is there a better way? Our
Production
> DB is so big, I want to give some simple data to development DB for test.
> Thanks for any help!
> Brian