Need to make the "NT Service\SQLAgent$SQL2012" a read permission on the needed file

Problem: 

Try to automate an SSIS job in SQL Server agent. The SSIS Service account is used, which is NT Service\SQLAgent$SQL2012 (for a named instance SQL2012 in my case). Got an error on open a local flat file.

Solution: 

Sharing the folder containing the file with the 'Everyone' group with a read permission as the virtual account 'NT Service\SQLAgent$SQL2012' is not possible to be explicitly selected.

Please note that, unlike the virtual account for SSAS, the virtual account for SQL Server Agent (i.e., SQLServerSQLAgentUser$MachineName$InstanceName) is not visible in the local user and group list (see this for details). So we cannot give the virtual account a read permission as we do for the virtual account for SSAS service account.

You may need to check the "32-bit Runtime" option when you validate or execute the packages in SSMS deployed from SSDT

Problem: 

Everything runs fine in SSDT. Then I deploy the project to SSISDB Catalog in SSMS. But I keep getting error on validation or execution for one of the packages.

Reason: 

The package needs to be run in 32-bit

Solution: 

Check the "32-bit runtime" option for Execution as depicted below.

Similarly, if you run the package using DTExec, you need to use the 32-bit DTExec. In my case, the 32-bit DTExec is located at:

D:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DtExec /Server "localhost\SQL2012" /ISServer "\SSISDB\TK 463 Chapter 11\TK 463 Chapter 10\Master.dtsx" /Par $ServerOption::LOGGING_LEVEL(Int32);1

BTW, the 64-bit is at D:\Program Files\Microsoft SQL Server\110\DTS\Binn

There is no such a 32-bit vs 64-bit problem with PowerShell. When I run the following ps file, it works fine as long as I change the server from localhost to localhost\SQL2012 (a named instance in my case)

=========================================================================

# Assign SSIS namespace to variable

$ssisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

# Load the SSIS Management Assembly

$assemblyLoad = [Reflection.Assembly]::Load($ssisNamespace + ", Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

# Create a connection to a SQL Server instance

$connectionString = "Data Source=localhost\SQL2012;Initial Catalog=master;Integrated Security=SSPI;"

$connection = New-Object System.Data.SqlClient.SqlConnection $connectionString

# Instantiate the SSIS object

$ssis = New-Object $ssisNamespace".IntegrationServices" $connection

# Instantiate the SSIS package

$catalog = $ssis.Catalogs["SSISDB"]

$folder = $catalog.Folders["TK 463 Chapter 11"]

$project = $folder.Projects["TK 463 Chapter 10"]

$package = $project.Packages[“Master.dtsx”]

# Set package parameter(s)

$catalog.ServerLoggingLevel = [Microsoft.SqlServer.Management.IntegrationServices.Catalog+LoggingLevelType]::Verbose

$catalog.Alter()

# Execute SSIS package ($environment is not assigned)

$executionId = $package.Execute("false", $environment)

=========================================================================