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


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.


The package needs to be run in 32-bit


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=, 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

# Execute SSIS package ($environment is not assigned)
$executionId = $package.Execute("false", $environment)