3

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.

New contributor
Andy Haines is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0

You have to give permission to the SQL Database Engine Service account NT SERVICE\MSSQL$<Instance Name> (Where <Instance Name> should be replaced by the installed instance name):

Your Answer

Andy Haines is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.