I am having issues with an SSIS package I have created that ultimately will check if an excel file exists, if not send an email to indicate failure. If it exists the data is loaded into a table and the excel file deleted. This works when I execute the package in Visual Studio but not when I deploy it to SQL Server. The issue being that the file is not being detected as existing in the later case. Hoping someone might be able to help. My first post on Stack Overflow so please be gentle :).
There is a variable defined called FileExists of type Boolean which is initially set to False. A script task with the below code is the first step in the control flow and this sets the variable to True/False depending on whether a file exists (for which the path is set in another variable FilePath).
<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _ <System.CLSCompliantAttribute(False)> _ Partial Public Class ScriptMain Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase Public Sub Main() If (File.Exists(CStr(Dts.Variables("FilePath").Value))) Then Dts.Variables("FileExists").Value = True Else Dts.Variables("FileExists").Value = False End If Dts.TaskResult = ScriptResults.Success End Sub #Region "ScriptResults declaration" Enum ScriptResults Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure End Enum #End Region End Class
Everything works fine when I execute the package in Visual Studio 2017. The file is detected or not. A variable is set to true or false correspondingly and the SSIS process follows the appropriate route.
When deploying the package on SQL Server 2016 (the VS project type is set to 2016) and executing there the variable is always returning as false. As the variable is declared initially as false, either it is not set to false or file exists is returning false. No errors are flagged during execution.
This is to run on SQL server 2016. The package was created in VS 2017 with the project set with the target server as 2016 (and hence VB version 2015).
I was convinced this must be permissions (to the file location or otherwise) but I have appended the username executing the package to the emails distributed later in the Control Flow. These were my credentials when executed from VS and from the server.
I also thought I had it cracked when I realised I was using VB 2017 on SQL Server 2016 but converting the project to use SQL 2016 as the target server did not resolve the issue.