SQL SERVER 2012 SSIS 之 POWERSHELL
一，连接服务器，执行PACKAGE1，建立连接
2，取回对象
3，创造执行对象
4，调度计划，执行PACKAGE
# Load the IntegrationServices Assembly
$loadStatus = ::Load("Microsoft"+
".SqlServer.Management.IntegrationServices" +
", Version=11.0.0.0, Culture=neutral" +
", PublicKeyToken=89845dcd8080cc91")
# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
Write-Host "Connecting to server ..."
# Create a connection to the server
$constr = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"
$con = New-Object System.Data.SqlClient.SqlConnection $constr
# Create the Integration Services object
$ssis = New-Object $ISNamespace".IntegrationServices" $con
## Drop the existing catalog if it exists
# Write-Host "Removing previous catalog ..."
# if ($ssis.Catalogs.Count -gt 0)
# {
# $ssis.Catalogs["SSISDB"].Drop()
# }
# Provision a new SSIS Catalog
Write-Host "Creating new SSISDB Catalog ..."
$cat = New-Object $ISNamespace".Catalog" ($ssis, "SSISDB", "#PASSWORD1")
$cat.Create()
# Create a new folder
Write-Host "Creating Folder ..."
$folder = New-Object $ISNamespace".CatalogFolder" ($cat, "Folder", "Description")
$folder.Create()
# Read the project file, and deploy it to the folder
Write-Host "Deploying ExecutionDemo project ..."
] $projectFile = ::ReadAllBytes("C:\Demos\Demo.ispac")
$folder.DeployProject("ExecutionDemo", $projectFile)
# Run the package
Write-Host "Running package ..."
# When executing, we need to specify two parameters
# 1 arg is a bool representing whether we want to run
# 32bit runtime on 64 bit server
# 2 arg is a reference to an environment if this package depends on it
$executionId = $package.Execute("false", $null)
Write-Host "Package Execution ID: " $executionId
二，执行参数复杂PACKAGE
1，重复上述步骤，调度计划
2，设置常量或环境参数
# Load the IntegrationServices Assembly
$loadStatus = ::Load("Microsoft"+
".SqlServer.Management.IntegrationServices" +
", Version=11.0.0.0, Culture=neutral" +
", PublicKeyToken=89845dcd8080cc91")
# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
Write-Host "Connecting to server ..."
# Create a connection to the server
$constr = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"
$con = New-Object System.Data.SqlClient.SqlConnection $constr
# Create the Integration Services object
$ssis = New-Object $ISNamespace".IntegrationServices" $con
## Drop the existing catalog if it exists
# Write-Host "Removing previous catalog ..."
# if ($ssis.Catalogs.Count -gt 0)
# {
# $ssis.Catalogs["SSISDB"].Drop()
# }
# Provision a new SSIS Catalog
Write-Host "Creating new SSISDB Catalog ..."
$cat = New-Object $ISNamespace".Catalog" ($ssis, "SSISDB", "#PASSWORD1")
$cat.Create()
# Create a new folder
Write-Host "Creating Folder ..."
$folder = New-Object $ISNamespace".CatalogFolder" ($cat, "Folder", "Description")
$folder.Create()
# Read the project file, and deploy it to the folder
Write-Host "Deploying ExecutionDemo project ..."
] $projectFile = ::ReadAllBytes("C:\Demos\Demo.ispac")
$folder.DeployProject("ExecutionDemo", $projectFile)
#### NEW STUFF STARTS FROM HERE ####
# we can specify the value of parameters to be either constants or
# to take the value fromenvironment variables
$package = $project.Packages[“ComplexPackage.dtsx”]
# setting value of parameter to constant
$package.Parameters["Servername"].Set(
::Literal,
"Foobar");
$package.Alter()
# binding value of parameter to value of an env variable is a little more complex
# 1) create environment
# 2) add variable to environment
# 3) make project refer to this environment
# 4) make package parameter refer to this environment variable
# These steps are shown below
# 1) creating an environment
$environment = New-Object $ISNamespace".EnvironmentInfo" ($folder, “Env1”, “Env1 Desc.”)
$environment.Create()
# 2) adding variable to our environment
# Constructor args: variable name, type, default value, sensitivity, description
$environment.Variables.Add(“Variable1”, ::Int32, “10”, “false”, “Desc.”)
$environment.Alter()
# 3) making project refer to this environment
$project = $folder.Projects[$SSISProjectName]
$project.References.Add($SSISEnv, $folder.Name)
$project.Alter()
# 4) making package parameter refer to thisenvironment variable
$package.Parameters["CoolParam"].Set(
::Referenced,
$SSISEnvVar)
$package.Alter()
# retrieving environment reference
$environmentReference = $project.References.Item($SSISEnv, $folder.Name)
$environmentReference.Refresh()
# executing with environment reference – Note: if you don’t have any env reference,
# then you specify null as the second argument
$package.Execute("false", $environmentReference)
Write-Host "Package Execution ID: " $executionId
页:
[1]