SQL SERVER 2012 SSIS 之 POWERSHELL

一，连接服务器，执行PACKAGE

1，建立连接

2，取回对象

3，创造执行对象

4，调度计划，执行PACKAGE

# Load the IntegrationServices Assembly            $loadStatus = [Reflection.Assembly]::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 ..."            [byte[]] $projectFile = [System.IO.File]::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 = [Reflection.Assembly]::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 ..."            [byte[]] $projectFile = [System.IO.File]::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 from  environment variables                        $package = $project.Packages[“ComplexPackage.dtsx”]                        # setting value of parameter to constant            $package.Parameters["Servername"].Set(                [Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::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”, [System.TypeCode]::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 this  environment variable            $package.Parameters["CoolParam"].Set(                [Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::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