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