Script task no longer working

Question

Post reply

Script task no longer working

Cheryl Reed

Newbie

Points: 6
More actions
March 12, 2009 at 11:22 am

#127027

Hello,
I have an SSIS package that has been running successfully for quite some time. It is a very simple package: prompts user for a filename (using Script task) and exports data to that filename. Recently, the package "hangs" when running the script task to prompt the user for a filename. Here is the script:
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Windows.Forms
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
' _
Public Sub Main()
Dim saveFileDialog1 As New SaveFileDialog
saveFileDialog1.InitialDirectory = "My Documents"
saveFileDialog1.Filter = "Text Files (*.txt)|*.txt"
saveFileDialog1.RestoreDirectory = True
If saveFileDialog1.ShowDialog() = DialogResult.OK Then
Dts.Variables("ExportFilename").Value = saveFileDialog1.FileName
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
End Class
This is now failing/hanging on all Windows Vista machines (was working previously) but still works on an XP machine.
Any ideas would be appreciated - I'm at the point where I don't even know where to look!
Thanks,
Cheri

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Tim Mitchell SSCoach Points: 15695 More actions · Answer 1

Cheryl,

Could you try another initial directory and see if it works there? Try hard coding to a specific directory as a test.

I mention this because Vista doesn't have a user folder named "My Documents". There is a folder for each user called "Documents", and in most cases I believe that using the "My Documents" pseudonym should work, but it may not be recognized when referenced in SSIS.

hth,

Tim

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

Cheryl Reed Newbie Points: 6 More actions · Answer 2

Tim,

Thanks for the suggestion. I changed it to "C:\" and it still is not responding. I even commented the "initial directory" line out with the same results...

Cheri

Tim Mitchell SSCoach Points: 15695 More actions · Answer 3

Are you able to post your package (dtsx) file here? I'll be glad to take a look.

FWIW, I've never seen this approach in an SSIS package, specifically the interaction to prompt for a filename. Is this a requirement, or do you simply need a unique name for the file?

Tim

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

Cheryl Reed Newbie Points: 6 More actions · Answer 4

It is a requirement - we use dtsx packages to import/export data for our various clients. It allows us (and our clients) the flexibility of naming their own files.

I've attached the .dtsx file

Tim Mitchell SSCoach Points: 15695 More actions · Answer 5

Cheri,

I'll take a look. Can you confirm that you are using only SSIS 2005? Do any of your machines run the 2008 version?

Tim

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

Cheryl Reed Newbie Points: 6 More actions · Answer 6

Thanks Tim. And yes, I am using only SSIS 2005.

Tim Mitchell SSCoach Points: 15695 More actions · Answer 7

Hi Cheri,

I ran this through on my test machine (SQL 2005 SP2 - 9.00.3042), and it seems to work as intended. I replaced your data source with a Konesans data generator, which should not be material to the problem you're experiencing, and the data flowed to the file I specified.

There were a couple of things I observed. You had the default directory set to C as in just the letter C, not the directory "C:\". This didn't prevent it from prompting me for the file location, but it did not default to the root of C:, but rather used the default location in my Windows user profile. Can you change this default directory value to the exact value "C:\" and see if that makes a difference?

Also, I saw that you had a breakpoint set in your script file. The breakpoint was disabled, so it shouldn't have any impact on the package, but it is worth removing this breakpoint completely if the above suggestion doesn't work.

Please let me know either way what you find.

hth,

Tim

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

Cheryl Reed Newbie Points: 6 More actions · Answer 8

Tim,

Thanks for trying. Since the package started failing, I have tried several variations for initial directory. What I don't understand is why, all of a sudden, it stopped working? Maybe Vista updates? (it was running successfully on Vista before...)

I'll post an update if I figure this out...

Cheri

Cheryl Reed Newbie Points: 6 More actions · Answer 9

So I know it's failing/hanging on the ShowDialog statement. Are there any log files I can check for further information??

COZYROC One Orange Chip Points: 28499 More actions · Answer 10

Cheryl Reed (3/13/2009)
So I know it's failing/hanging on the ShowDialog statement. Are there any log files I can check for further information??

From where do you execute the package? Are you sure the dialog is not sitting somewhere behind and waiting for input?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/