Transform Data Task Won't Run

[fuzzyocelot]

Something strange is going on with one of my packages that I hope someone can help me with. I tried to search for something similar in this forum but couldn't find anything.

I'm running a DTS package from my machine which is running Windows XP Pro SP2. I'm connected to a test server (Windows Server 2003 with SP1) which has SQL Server 2000.

When I run/execute the package from DTS, the last step won't run. It's a transform data task. The package completes successfully without error but it says the last step didn't run. More specifically, the Package Execution Results message box appears and says "Successfully completed execution of package." I click okay and I see the execution progress window with the step in question and the status is "Not Run". I can't figure out why and it's bugging the heck out of me.

If I run the transform data task by itself, it runs fine without error. Meaning, it runs and populates the destination table. I then removed all the other tasks so that all that's left is the source (text file), the transform data task, and the destination connection (server). I also deleted any global variables set at the package level (nothing is set to change the run status or whatever it's called). Within the transform data task, the source is set to the same file and path as in the text file (source) connection. The destination is a table that exists in the correct database that the destiantion connection is pointing to. The transformations are just copying columns from the file into the table. I have the correct login information set in the destination server connection. It still won't run when I click the execute button for the package. However, it runs fine when I select the transformation task, right-click, and select execute. It says it was successful and the destination table is populated (checked the table via query analyzer).

Does anyone have any suggestions or ideas? I really hope I won't have to recreate the package.

Thanks!
Rebecca

 

[AlexCuse]

Sounds like a workflow issue. What is the previous step?

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[AngelB]

If you have other steps before this one, have you checked the workflow criteria between the last-but-one step and the last one? It may be that it's set to execute on failure of the previous step and that is why you get a 'not run' message rather than a package failure error.

Hope this helps


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[fuzzyocelot]

First, thank you both for replying.

I thought about the workflow as well. If the previous step is successful, the workflow indicates it should go to the next step which is the data transform task. However, I removed ALL workflows and ALL other tasks except for the source connection, data transform task, and destination connection. I end up with the exact same problem.

 

[AlexCuse]

Which is set by global variable, your data source or your destination? (if either)

Is your data source using any parameters?

This is truly strange.

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[fuzzyocelot]

I fixed the problem, although I'm not sure how it got to be this way in the first place. In the original package, I checked if a certain directory is missing using an ActiveX Script Task. If it is missing, disable the "Continue" step and enable the "Error" step. The code I used is as follows (I got the idea from somewhere on the net but I don't remember where exactly):

Option Explicit

Function Main()
Dim pkgMain, stepContinue, stepError

SET pkgMain = DTSGlobalVariables.Parent
SET stepContinue = pkg.Steps("DTSStep_DTSActiveScriptTask_3")
SET stepError = pkg.Steps("DTSStep_DTSActiveScriptTask_2")

SET fso = CREATEOBJECT("Scripting.FileSystemObject")

If  fso.FolderExists(DTSGlobalVariables("FileLocation").Value) <>  "True" Then
	DTSGlobalVariables("FileError").Value = "File Error: " & CSTR(DTSGlobalVariables("FileError").Value) 
End If

If len(DTSGlobalVariables("FileError").Value) > 2 Then
	stepContinue.DisableStep = True
	stepError.DisableStep = False
Else
	stepContinue.DisableStep = False
	stepError.DisableStep = True
End If

Main = DTSTaskExecResult_Success
End Function

The transform data task that wouldn't run comes after the "Continue" step. I don't know why it became disabled. So I added code to enable it (DisableStep = False) if the directory exists and now it works fine.

Thanks!

 

[AlexCuse]

I had thought it was something along those lines. I would think that the 'disabled' property would default to false when all other steps are removed.

Thanks for the update, it's good to know!

Alex

A wise man once said
"The only thing normal about database guys is their tables".