Performance issues - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Performance issues

Author

Topic

johnoxy
Starting Member

10 Posts

Posted - 2007-05-02 : 01:13:44

Hi
I am running dts packages from an Access app. The Dts package i am running is taking about 5 mins to run when it only takes 30 seconds when run from EM. Does anyone know why this is.

Cheers

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-02 : 09:50:00

How do you call the package in access?

johnoxy
Starting Member

10 Posts

Posted - 2007-05-02 : 19:46:42

Hi rmiao

Public Function ExecuteDTSPackage(sServer As String, sUsername As String, sPassword As String, sPackageName, Optional LogFileName As String) As String
Dim oPKG As DTS.Package, oStep As DTS.Step
Dim Failed As Boolean, sMessage As String
Dim lErr As Long, sSource As String, sDesc As String

On Error GoTo ExecuteDTSPackageError

Set oPKG = New DTS.Package

' Load Package

oPKG.LoadFromSQLServer sServer, sUsername, sPassword, DTSSQLStgFlag_Default, , , , sPackageName

' Set Exec on Main Thread

For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next

' Execute

oPKG.Execute

Failed = False

' Get Status and Error Message

For Each oStep In oPKG.Steps

If oStep.ExecutionResult = DTSStepExecResult_Failure Then

oStep.GetExecutionErrorInfo lErr, sSource, sDesc

Call Add_Error(lErr & "-" & sSource & "-" & sDesc, "DTS Error")
Failed = True

End If

Next

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-02 : 22:26:51

Try create a job to run the package (without schedule), then run the package with msdb..sp_start_job.

johnoxy
Starting Member

10 Posts

Posted - 2007-05-02 : 22:42:04

Thanks rmiao
that speeds it up

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources