Script Task to check file existance is OK in Visual Studio but fails when Deployed

Question

Script Task to check file existance is OK in Visual Studio but fails when Deployed

Tim Pain

Ten Centuries

Points: 1136
More actions
October 11, 2017 at 11:45 am

#329152

Hi,
Within a Foreach loop, I have a simple Script Task to check if a file exists. If it exists, then FTP the file. If does not exist, then add the full file path to a error log table.
The script -
If (File.Exists(Dts.Variables("User::strFullPath").Value.ToString())) Then
Dts.Variables("User::bolFileExists").Value = True
Else
Dts.Variables("User::bolFileExists").Value = False
End If
My test data has 6 files to find, of which only 4 exist.
In Visual Studio, this runs fine and FTP's 4 files and writes 2 entries to the error log.
However when Deployed to SQL Server 2016, the project runs OK, but fails to find any files. So all 6 entries get written to the error log.
Everything is on my laptop.
I've tried with local path names (C:\PathName\FileName.jpg) and UNC names (\\LAPTOP\ShareName\FileName.jpg).
Both the source Folder and Share have been given Read permission to Everyone.
In Visual Studio, I've set a break point on the Foreach loop and verified that the "strFullPath" variable is passing a valid path/filename - although at this level it does have additional "\". So \\LAPTOP\ShareName\FileName.jpg becomes \\\\LAPTOP\\ShareName\\FileName.jpg
Any idea why I'm missing that is stopping this working correctly when Deployed?
Many thanks
Tim

Tim

Viewing 15 posts - 1 through 15 (of 18 total)

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

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

Tim Pain - Wednesday, October 11, 2017 11:45 AM
Hi,
Within a Foreach loop, I have a simple Script Task to check if a file exists. If it exists, then FTP the file. If does not exist, then add the full file path to a error log table.
The script -
If (File.Exists(Dts.Variables("User::strFullPath").Value.ToString())) Then
Dts.Variables("User::bolFileExists").Value = True
Else
Dts.Variables("User::bolFileExists").Value = False
End If
My test data has 6 files to find, of which only 4 exist.
In Visual Studio, this runs fine and FTP's 4 files and writes 2 entries to the error log.
However when Deployed to SQL Server 2016, the project runs OK, but fails to find any files. So all 6 entries get written to the error log.
Everything is on my laptop.
I've tried with local path names (C:\PathName\FileName.jpg) and UNC names (\\LAPTOP\ShareName\FileName.jpg).
Both the source Folder and Share have been given Read permission to Everyone.
In Visual Studio, I've set a break point on the Foreach loop and verified that the "strFullPath" variable is passing a valid path/filename - although at this level it does have additional "\". So \\LAPTOP\ShareName\FileName.jpg becomes \\\\LAPTOP\\ShareName\\FileName.jpg
Any idea why I'm missing that is stopping this working correctly when Deployed?
Many thanks
Tim

Are you deploying to the SSIS Catalog on your laptop, or to a server somewhere?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Tim Pain Ten Centuries Points: 1136 More actions · Answer 2

To SQL running on my own laptop.
So at present, everything is on my laptop.

Thanks

Tim

Thom A SSC Guru Points: 99130 More actions · Answer 3

I'd hazard a that it's permissions. How are you running the task on your laptop, via agent, T-SQL or navigating to the SSIS Catalog?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

Tim Pain - Wednesday, October 11, 2017 1:03 PM
To SQL running on my own laptop.
So at present, everything is on my laptop.
Thanks

OK. If you compare the All Executions report for a run which completes as expected with one which does not, do you see any unexpected differences or anomalies?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Tim Pain Ten Centuries Points: 1136 More actions · Answer 5

Thom,

Permissions was my though as well, hence the Read permission for Everyone on the folder with the *.jpg files. Which is overkill, but in my mind should eliminate any Permissions issues?

Phil,

I've tried

Right clicking the package in the SSMS "Integration Services Catalogues" and selecting Execute and then supplying the required parameters. And
Executing the package, by setting the parameters with -
EXEC [SSISDB].[catalog].[set_execution_parameter_value] and then
EXEC [SSISDB].[catalog].[start_execution]

I could run it from the Agent for testing, but when I get it working and go to production, I plan to use the EXEC SSIDB.catalog option.

Both report Success in the All Executions report. Both show the Script Task being run. And bot then end up with 6 records in the error log saying that the image files were not found.

Thanks

Tim

Thom A SSC Guru Points: 99130 More actions · Answer 6

Sorry for late reply.

If you're using T-SQL to run the task, however, then I'm pretty confident that the problem is our old friend "Mr Double-hop" (Phil, would you agree?). This is a kerboros related issue, and you need to allow multiple jumps via it. What is (likely) happening at the moment is that the access to the directory is being made anonymously (and thus denied).

You'll need to speak to your Network Administrator on enabling authentication via "double hopping". They should be aware of what it is, and how configure it correctly.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

Thom A - Thursday, October 12, 2017 9:28 AM
Sorry for late reply.
If you're using T-SQL to run the task, however, then I'm pretty confident that the problem is our old friend "Mr Double-hop" (Phil, would you agree?). This is a kerboros related issue, and you need to allow multiple jumps via it. What is (likely) happening at the moment is that the access to the directory is being made anonymously (and thus denied).
You'll need to speak to your Network Administrator on enabling authentication via "double hopping". They should be aware of what it is, and how configure it correctly.

I've had Kerberos issues in the past when attempting to execute packages directly from SSMS, yes. But running in SQL Agent has always been pretty reliable, whether executing packages directly, or via a stored proc (which does the usual Catalog.CreateExecution stuff).
What puzzles me is that this is all happening locally, so nothing should be getting in the way. The Network Admin is probably not the guy to turn to with this one!
Question for the OP: In the All Executions report, under 'All Messages', what appears under 'Caller'?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Tim Pain Ten Centuries Points: 1136 More actions · Answer 8

Thom,

Thanks for your input. Its maybe sort of confirmed by the report All Executions - Overview, showing CALLER_INFO as blank.
Although, I've also found that if I set up an Agent job and run it from that, then CALLER_INFO is set to SQLAGENT, but it still does not work.

I'm still working through it, but if I deploy the package to the File System and then execute that, via DTEXEC, so far it seems to work as expected. Its not quite what I wanted, but it's something I can certainly live with.

Thanks

Tim

Phil Parkin SSC Guru Points: 246960 More actions · Answer 9

Tim Pain - Thursday, October 12, 2017 10:45 AM
Thom,
Thanks for your input. Its maybe sort of confirmed by the report All Executions - Overview, showing CALLER_INFO as blank.
Although, I've also found that if I set up an Agent job and run it from that, then CALLER_INFO is set to SQLAGENT, but it still does not work.
I'm still working through it, but if I deploy the package to the File System and then execute that, via DTEXEC, so far it seems to work as expected. Its not quite what I wanted, but it's something I can certainly live with.
Thanks

But what about Caller? CALLER_INFO appears to be blank for me too, so that's probably a red herring.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Tim Pain Ten Centuries Points: 1136 More actions · Answer 10

Phil,

As mentioned above, if its called via the Agent, then SQLAGENT.
It's is called directly or via the Catalog.Create stuff, then CALLER_INFO is blank.
Thanks

Tim

Tim Pain Ten Centuries Points: 1136 More actions · Answer 11

Sorry, misread what you had asked.
Caller is me "MicrosoftAccount\MyName@gmail.com"

Tim

Phil Parkin SSC Guru Points: 246960 More actions · Answer 12

Tim Pain - Thursday, October 12, 2017 10:54 AM
Sorry, misread what you had asked.
Caller is me "MicrosoftAccount\MyName@gmail.com"

Is that account a local admin?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Thom A SSC Guru Points: 99130 More actions · Answer 13

Sorry, I hadn't noticed that this was all being run locally, so no, network guy is the wrong person.

If, however, the OP can use DTExec to do it, this still likely points to Kerboros Double hop. Even if you are running locally (SSMS on the same Machine as the SQL Instance, which, in turn is the same machine the file is on) the double hop issue will still arise. The credentials are still going SSMS->SQL Server->File System, it doesn't matter if it's all the same server, at the point of SQL Server-> File System you're at the point of the "double hop". I would put money on betting that if the OP tried to execute the task via Agent it would also work.

I'm not going to lie, I have no idea on how to resolve the matter of the double hop. Sorry (I realise not being helpful there). I am, however, intrigued to hear if it does work via Agent (as I suspect).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Tim Pain Ten Centuries Points: 1136 More actions · Answer 14

Phil - Yes the account is local admin

Thom - Everything is on the laptop, so I should be able to kill the WiFi connection and it should still run.
Using the Agent, I get the same results as executing the Package i.e.: It runs without errors, but fails to find any of the 6 files and then outputs 6 records to the error log.

Thanks

Tim