December 16, 2002 at 8:52 am
I have a DTS that contains an ODBC connection. After the DTS was created and ran as a job successfully for several months, I have had to change my password for the datasource (IBM DB2). Now when I try to change the password in the connection object in the DTS, it will not save the changed password. I can type in the new password correctly, click on OK and then save the DTS. But the next time I open the DTS and open the properties for the connection object, the original password is still saved in the object. I know this because my original password was 10 characters and my new password is only 8. Every time I go and look at the connection property, it still has the password with the 10 characters. I'm using SQL Server 7.
Any ideas on how to get the new password saved in the connection object so the DTS will run properly???
December 16, 2002 at 9:02 am
I think it just put 10 stars in the password textbox regardless of the password. Does the connection in the package actually connect with the new password?
Regards,
Andy Jones
.
December 16, 2002 at 9:16 am
I think Andy is correct...try running the package with the new password and see if it runs. Please let us know what you find out...
Michael
Michael Weiss
Michael Weiss
December 16, 2002 at 1:16 pm
Oh -- I did not know that tidbit of info about the 10 stars. Well, that certainly helps in the debug process.
I believe it is connecting properly, because I can run it interactively through Ent. Mgr. and it runs fine. Most times it runs properly when scheduled as a job. But it seems like more and more here lately, it is failing when extracting data from the DB2 database. I'm executing several DTS packages from within another DTS and writing the results of each step to a table which I check after the process has completed. But by doing it in this way, I loose the really useful error message that I would get if I ran it interactively and it failed. I simply get Success/Fail as a result code.
Is there a property that I can also write to my log table that would allow me to log the exact error message returned by SQL Server???
Here is the code I'm using for error logging...
'Declare variables
Dim my_LOG_pkg
Dim my_LOG_task
Dim my_Log_Msg
'Initialize LOG package
Set my_LOG_pkg = CreateObject("DTS.Package")
my_LOG_pkg.LoadFromSQLServer "STLMS032",,, DTSSQLStgFlag_UseTrustedConnection,,,, _
"BKE_DTS_Process_Log"
Set my_LOG_task = my_LOG_pkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask
' <<<--- EXECUTE ANOTHER DTS PACKAGE HERE --->>>
'Create and Execute the package
Set objPkg = CreateObject("DTS.Package")
objPkg.LoadFromSQLServer "STLMS032", , , DTSSQLStgFlag_UseTrustedConnection,,,, _
"BKE_DTS_Extract_ODS_Data_for_Monitoring_Tool_ALL_Dynamic_Date"
objPkg.Execute
'Check For Errors
For iCount = 1 To objPkg.Steps.Count
If objPkg.Steps(iCount).ExecutionResult = 0 Then
my_Log_Msg = "Successful"
Else
my_Log_Msg = "Failed"
End If
my_LOG_task.SQLStatement = "exec sp_myProcessTime " & _
"'" & objPkg.Name & "', " & _
"'" & objPkg.Steps(iCount).Description & "', " & _
"'" & objPkg.Steps(iCount).Name & "', " & _
"'" & my_Log_Msg & "', " & _
"'" & objPkg.Steps(iCount).StartTime & "', " & _
"'" & objPkg.Steps(iCount).FinishTime & "'"
' <<<--- EXECUTE STORED PROC WHICH TAKES INFO AS PARAMETERS--->>>
my_LOG_pkg.Execute
Next
Set objPkg = Nothing
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply