SSIS 2016 Script task runs but file not created

Question

Post reply

SSIS 2016 Script task runs but file not created

dwilliscp

SSCrazy Eights

Points: 9270
More actions
October 15, 2019 at 7:47 pm

#3689021

So I have a Visual Studio 2017 Package Deployment package (ProcessOrders) that has a Script Task Editor that will write out a file, using..
System.IO.File.WriteAllBytes(fileOut, unicodeCONVERT);
When I run this in Visual Studio 2017 with the system account we use for SSIS, it runs fine. But when our Windows Server 2016, running SQL Server 2016 runs.. it generates no error, but no file.
All it is logging is the "OnPreValidate" and "OnPostValidate"... with empty message boxes.
How do I get it to tell me what the error is when writing?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 246693 More actions · Answer 1

Are you using SQL Agent job to do this?

If so, it's likely that the SQL Agent user does not have the necessary rights to create the file.

Or, if the file path includes a network drive letter, that the drive is not mapped for the SQL Agent user (in which case, use a UNC path instead).

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 2

So I doublechecked and the SQL Server running the job … is using the service account. (same one I tested Visual Studio with)

Also both the file it is reading and the one that it should be writing are both UNC paths.

I have a different package.. that runs FileInfo commands and if the file is more that 5 days old.. deletes that file extract. This is working fine. Same location as the file I am trying to write.

Both scripts no not have any special logic trying to detect failure.. I was assuming that SSIS would detect the script failure and log the error. Is there something that I could pull and write out to the SSIS logging?

Phil Parkin SSC Guru Points: 246693 More actions · Answer 3

I'm assuming that SSIS does not consider what happened to be an error ... otherwise, by default, your package would fail.

Assuming this package is deployed to SSISDB, please check the All Executions report & look for any useful messages associated with the script task.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 4

I am using Package Deployment.. so just sending it to SSIS the old way. When I open up the package, and right clicking and then picking logging, it shows the script starting and then finishing.. but the message section is blank for both rows.