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 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 capture Package Validation Error?

Hi,

I have a package which has 5 connection managers. One of the Connection Manager has incorrect server name, which results in Package Validation error. Which event handler should be used to run on such errors for OnError Event handler doesnt work @. all.

Thanks

Prasad

I may be wrong; but I think if the validation step fails; the package logging nor event handler would work as they need a valid package. What you can do is to capture the output of the package execution command line. Eg if you are using sql server agent; and run the package as CmdExec step; you will see the validation error in the job log.|||

i dont see any errors in the job log which point out to invalid packages.

Is there any metadata table like sysdtspackages90 which can be queried to find out if the generated packages are valid or not?

|||You need to run the package through a CmdExec step, not from a SSIS step, as Rafael mentioned. This is the same as running it from the command line.|||using dtexec /validate we can validate the packages|||

2lazydba wrote:

using dtexec /validate we can validate the packages

Well now THERE is something I never knew. Thanks 2lazydba.

-Jamie

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 call .NET framework library within SPROC

Hi, with SQL server 2005.

Could I write a SPROC with C# or VB.NET that calls methods within the .net library or

Could I package a .net methods into a SPROC to let other SPROC invoke it?

Thanks in advance.

Ricky.

You can write C#, VB.NET methods that can be used from inside SQL Server to call .NET libraries.

Niels
|||Yes, you can write a stored proc in clr (C# or VB.NET) that can call methods in the .NET Framework.
Yes.

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

If the SPORC written with C# needs to call another C# method that is within a thrid-party component, is it feasible implement in the SQL server 2005?

Thanks,

Ricky.

|||Yes, but you have to deploy that third party component to the database as weel (i.e together with your original .NET assembly(ies)).

Niels
|||

Hi, nielsb.

I create a database project trying to add reference.

However, I cannot add .dll except for built-in .net library.

How do I deploy a third-party .dll onto sql server 2005? Please give me example if possible.

Thanks a million.

Ricky.

|||Deploy your third party dll manually to SQL Server, by using CREATE ASSEMBLY syntax. Read more about CREATE ASSEMBLY in SQL Server Books OnLine. By deploying it manually, you can then reference it from your database project.

Niels
|||

Thanks too much.

nielsb.

By the way, someone told me that previously (before .net era), he could wrote extended sproc with C and umlinitedly access the library outside SQL server. Why cannot we do as him currently? Security consideration?

Ricky.

|||

Ricky Wang wrote:

By the way, someone told me that previously (before .net era), he could wrote extended sproc with C and umlinitedly access the library outside SQL server. Why cannot we do as him currently? Security consideration?

Extebded stored proc's are still supported in SQL 2005, but are being deprecated. Main reason for this is security and reliability issues. The extended proc's are deprecated in favor of .NET methods. You can almost do anything that you are doing in and XP in a .NET method.

Niels
|||

Thanks for explanation.

Ricky.