Tag Archives: SSIS

ISPACs – Got To Love Em

5 Replies

Being a DBA and working with various teams, I have become accustomed to deploying SSIS packages. I’ll even go back further and, dare I say, DTS packages (I hope everyone did not just fall on the floor while reading that). Now-a-days this Database Professional does more deploying packages than developing them; with that said I was pleased when 2012 came along.

When the newer version of SSIS was released it offered several new features, one of those features was building a deployment model thus producing an ISPAC file. I remember first coming across this and thinking, “Wow, this is all packaged up”. While I won’t go into the specifics of how to build the deployment I will show you the step by step process of deploying the ISPAC file.

Step 1

Locate the ISPAC file. Once located double click on the ISPAC file to gain entry into the deployment wizard.

Ispac

 

Step 2

What kind of Integration Services project do you want to deploy? Since we are dealing with an ISPAC file on our local directory we will select the source path we found from Step 1. Simply browse to the path location and click ok.

Source

 

Step 3

So you have your ISPAC file selected and you want to deploy it; where does it go? You have the capability to supply any Server Name for rapid deployment. The path reflects the SSISDB catalog which is required for SSIS 2012 packages to be deployed. If your SSIS package is already on the server you can simply choose to overwrite the existing file at deployment time.

Destination

 

Step 4

After you select the destination path; it is then time for review. The review section will provide you with a great overview of the Source path of the file being deployed and the destination location.

Review

Step 5

Once verified and the deployment button is hit we are off to the races. The ability to have quick insight into the deployment and the methodology to save the report for future use is stellar. If your deployment did fail; at this step you would be able to dive into the error in the result pain on the right hand side.

Deploy

 

Seems simple enough? The deployment method in SSIS 2012 has proved beneficial for myself and is a welcomed aspect. Earlier this year I completed a post on the utilization of PowerShell and how to deploy 2012 SSIS packages and that can be found here.

I tell you what; check out what my other colleagues have to say on something they learned recently around SSIS:

 

CollaborateImageOn a SQL Collaboration Quest

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.

Utilizing PowerShell With 2012 SSIS Deploys

6 Replies

toolsThis venture was first inspired by watching and reading Steve Jones’ (Blog|Twitter) PowerShell challenge. I always knew that PowerShell was a powerful tool and one that I needed to jump into at some point in my career. I dabbled with it some last year and did some small things, but it became more evident heading into this year that I needed to get more accustomed to all the intricacies and the usefulness I could gain from it as a data professional.

One major paint point in automation portion of deploys was the fact of deploying .ispacs in SSIS 2012. Now granted the actual deploy is not cumbersome, in my opinion, Microsoft has done a decent job this go around with how to deploy with the wizard etc. However, a good developer friend of mine decided that he wanted to start out on the PowerShell venture as well and has since taught me a few things regarding deployments and how to utilize power shell. Who knew that developers and DBA’s could actually work on something and an idea together; just another step in bridging that gap.

Keep in mind that the below code has dummy environmental variable and parameters for obvious reasons. This is just to provide an example and for one tweak it in hopes it may help another data professional at a future time.

The Code

First line is the most important for this venture. It sets the parameter of what action will be taken in the script.

param([Int32]$step = 1)
  1. If the value is 1 then the process will create the folder, deploy the package, set up the environment variable and parameter, reference parameters (do everything)
  2. If the value is 2 then the process will just re-deploy the package
  3. If the value is 3 then the process will change the environment variable/parameter
  4. If the value is 4 then the process will re-deploy the package and change the environment variable

After setting the variable for what action is being taken the next few steps will depict variable setup, loading the integration services assembly, storing the integration services assembly to avoid typing it every time, creating the connection to the deployed server, and creating the integration services object.

# Variables
$ProjectFilePath = "This is the location of where the ispac is located. For me it is getting the latest in TFS and then found on my C:\"
$ProjectName = "Insert the project name"
$FolderName = "Folder that the package will reside"
$EnvironmentName = "The environment name of the package"
$RPTServerName = "Server being deployed to"

# Load the IntegrationServices Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;

# 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
$sqlConnectionString = "Data Source=$RPTServerName;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Create the Integration Services object
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection
$catalog = $integrationServices.Catalogs["SSISDB"]

Create the folder, deploy the package, set up the environment variable and parameter, reference parameters (do everything)

if ($step -eq 1)

            {
                if ($catalog.Folders[$FolderName]) {Write-Host "SSIS folder $FolderName already exists"}
                else {
            Write-Host "Creating folder $FolderName ..."
            $folder = New-Object $ISNamespace".CatalogFolder" ($catalog, $FolderName, "Folder description")
            $folder.Create()

            Write-Host "Deploying " $ProjectName " project ..."
             # Read the project file, and deploy it to the folder
             [byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
             $folder.DeployProject($ProjectName, $projectFile)

            Write-Host "Creating environment ..."
            $environment = New-Object $ISNamespace".EnvironmentInfo" ($folder, $EnvironmentName, "Description")
            $environment.Create()

            Write-Host "Adding server variables ..."
            # Adding variable to our environment
            # Constructor args: variable name, type, default value, sensitivity, description
            $environment.Variables.Add(“Env1”, [System.TypeCode]::Int32, 0, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env2”, [System.TypeCode]::Int32, 0, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env3”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env4”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env5”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env6”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env7”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env8”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“DatabaseEnv1”, [System.TypeCode]::String, "DatabaseName1", $false, "Database1")
            $environment.Variables.Add(“DatabaseEnv2”, [System.TypeCode]::String, "DatabaseName2", $false, "Database2")
            $environment.Variables.Add(“DatabaseEnv3”, [System.TypeCode]::String, "DatabaseName3", $false, "Database3")
            $environment.Variables.Add(“Reporting_Deployed_Server”, [System.TypeCode]::String, $RPTServerName, $false, "Reporting_Deployed_Server")
            $environment.Alter()

            Write-Host "Adding environment reference to project ..."

            # making project refer to this environment
            $project = $folder.Projects[$ProjectName]
            $project.References.Add($EnvironmentName)
            $project.Alter()

            Write-Host "Adding reference to variables ..."

            $project.Parameters["Paramater1"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Paramater1")
            $project.Parameters["Paramater2"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Paramater2")
            $project.Parameters["Paramater3"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Paramater3")
            $project.Parameters["Paramater4"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Paramater4")
            $project.Parameters["Paramater5"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Paramater5")
            $project.Parameters["Paramater6"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Paramater6")
            $project.Parameters["Paramater7"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Paramater7")
            $project.Parameters["Paramater8"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Paramater8")
            $project.Parameters["DatabaseParamater1"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "DatabaseParamater1")
            $project.Parameters["DatabaseParamater2"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "DatabaseParamater2")
            $project.Parameters["DatabaseParamater3"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "DatabaseParamater3")
            $project.Parameters["Reporting_Deployed_Server"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Reporting_Deployed_Server")
            $project.Alter()
}
   Write-Host "All done." }

Redeploying the package

 elseif ($step -eq 2) # If you want to just Re-deploy the package
        {

                if (-not $catalog.Folders[$FolderName]) {Write-Host "SSIS folder $FolderName doesn't exists. SSIS folder $FolderName must exist before package can be deployed "}
                else {

    Write-Host "Deploying " $ProjectName " project ..."

    $folder = $catalog.Folders[$FolderName]
    $project = $folder.Projects[$ProjectName]
    # Read the project file, and deploy it to the folder
    [byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
    $folder.DeployProject($ProjectName, $projectFile)

    Write-Host $project.Name "was deployed with"
    Write-Host "Description: " $project.Description
    Write-Host "ProjectID: " $project.ProjectID
    Write-Host "All done."
      }
}

Changing the environment variable/parameter

   elseif ($step -eq 3) # If you want to change the environment variable/parameter
{

if (-not $catalog.Folders[$FolderName]) {Write-Host "SSIS folder $FolderName must exist before add/modify parameters and environment variables "}
                else {

$catalog = $integrationServices.Catalogs["SSISDB"]
$folder = $catalog.Folders[$FolderName]
$project = $folder.Projects[$ProjectName]

#Drop an environment if already exists
Write-Host "Drop Environment Variable ..."
if ($folder.Environments[$EnvironmentName]) { $folder.Environments[$EnvironmentName].Drop() }

#Create an environment
Write-Host "Creating environment ..."
$environment = New-Object $ISNamespace".EnvironmentInfo" ($folder, $EnvironmentName, "Description")
$environment.Create()

Write-Host "Adding server variables ..."
# Adding variable to our environment
# Constructor args: variable name, type, default value, sensitivity, description
            $environment.Variables.Add(“Env1”, [System.TypeCode]::Int32, 0, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env2”, [System.TypeCode]::Int32, 0, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env3”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env4”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env5”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env6”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env7”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env8”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“DatabaseEnv1”, [System.TypeCode]::String, "Reporting_TRS_IN", $false, "DatabaseEnv1")
            $environment.Variables.Add(“DatabaseEnv2”, [System.TypeCode]::String, "Reporting_TRSODS", $false, "DatabaseEnv2")
            $environment.Variables.Add(“DatabaseEnv3”, [System.TypeCode]::String, "ReportingAdmin", $false, "DatabaseEnv3")
            $environment.Variables.Add(“Reporting_Deployed_Server”, [System.TypeCode]::String, $RPTServerName, $false, "Reporting_Deployed_Server")
            $environment.Alter()

Write-Host "Adding environment reference to project ..."

<# making project refer to this environment $project = $folder.Projects[$ProjectName] $project.References.Add($EnvironmentName) $project.Alter()#>

Write-Host "Adding reference to variables ..."

            $project.Parameters["Parameter1"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter1")
            $project.Parameters["Parameter2"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter2")
            $project.Parameters["Parameter3"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter3")
            $project.Parameters["Parameter4"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter4")
            $project.Parameters["Parameter5"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter5")
            $project.Parameters["Parameter6"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter6")
            $project.Parameters["Parameter7"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter7")
            $project.Parameters["Parameter8"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter8")
            $project.Parameters["DatabaseParam1"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "DatabaseParam1")
            $project.Parameters["DatabaseParam2"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "DatabaseParam2")
            $project.Parameters["DatabaseParam3"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "DatabaseParam3")
            $project.Parameters["Reporting_Deployed_Server"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Reporting_Deployed_Server")
            $project.Alter()

Write-Host "All done."
}
}

Redeploy the package and change the environment variable\parameter

 elseif ($step -eq 4) # If you want to Re-deploy the package and change the environment variable/parameter
 {
if (-not $catalog.Folders[$FolderName]) {Write-Host "SSIS folder $FolderName must exist before add/modify parameters and environment variables or deploy package "}
                else {

            $catalog = $integrationServices.Catalogs["SSISDB"]
            $folder = $catalog.Folders[$FolderName]
            $project = $folder.Projects[$ProjectName]

            if ($folder.Environments[$EnvironmentName]) { $folder.Environments[$EnvironmentName].Drop() }

             Write-Host "Deploying " $ProjectName " project ..."
             # Read the project file, and deploy it to the folder
             [byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
             $folder.DeployProject($ProjectName, $projectFile)

            Write-Host "Creating environment ..."
            $environment = New-Object $ISNamespace".EnvironmentInfo" ($folder, $EnvironmentName, "Description")
            $environment.Create()

            Write-Host "Adding server variables ..."
            # Adding variable to our environment
            # Constructor args: variable name, type, default value, sensitivity, description
            $environment.Variables.Add(“Env1”, [System.TypeCode]::Int32, 0, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env2”, [System.TypeCode]::Int32, 0, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env3”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env4”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env5”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env6”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env7”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env8”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“DatabaseEnv1”, [System.TypeCode]::String, "Reporting_TRS_IN", $false, "DatabaseEnv1")
            $environment.Variables.Add(“DatabaseEnv2”, [System.TypeCode]::String, "Reporting_TRSODS", $false, "DatabaseEnv2")
            $environment.Variables.Add(“DatabaseEnv3”, [System.TypeCode]::String, "ReportingAdmin", $false, "DatabaseEnv3")
            $environment.Variables.Add(“Reporting_Deployed_Server”, [System.TypeCode]::String, $RPTServerName, $false, "Reporting_Deployed_Server")
            $environment.Alter()

            Write-Host "Adding reference to variables ..."
            $project.Parameters["Parameter1"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter1")
            $project.Parameters["Parameter2"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter2")
            $project.Parameters["Parameter3"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter3")
            $project.Parameters["Parameter4"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter4")
            $project.Parameters["Parameter5"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter5")
            $project.Parameters["Parameter6"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter6")
            $project.Parameters["Parameter7"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter7")
            $project.Parameters["Parameter8"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter8")
            $project.Parameters["DatabaseParam1"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "DatabaseParam1")
            $project.Parameters["DatabaseParam2"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "DatabaseParam2")
            $project.Parameters["DatabaseParam3"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "DatabaseParam3")
            $project.Parameters["Reporting_Deployed_Server"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Reporting_Deployed_Server")
            $project.Alter()
}
   Write-Host "All done." }

Conclusion

With any type of code you find on the internet it’s imperative that you test this on local machines or in a Dev sandbox before thinking of running in a QA, UAT, or Prod environment. What this has done is cut down my deployment times significantly and has helped improve efficiency. I always like a challenge and combing over this project and testing and re-testing proved fruitful in the end. For me it was worth it; hope others can get something out of it as well.

A big thanks to Steven Robinson who was the developer mentioned on this project; Steven is one of the hardest working developers I’ve been around and the communication between the two units played a key roll in achieving in the end success.

SSIS 2012 Style – Not So Bad After All

Leave a reply

Okay okay, maybe DTS left a bad taste in my mouth or maybe it was because I’ve developed over 256 DTS packages and I still twitch at night. Or maybe I used the first edition of SSIS and while it was better than DTS I still wasn’t a fan; then one of my old colleagues touched base with me and told me that I needed to give the new SSIS version a shot.  He was curious about what I thought, I reluctantly held him at bay for a while but then an opportunity presented itself one day last week. A current colleague approached me with an issue where he had some .txt files that contained SQL code in them that he was running manually.

The first thing that popped in my head was giving SSIS another shot, I initially said no way not going there but figured ah what the heck – lets give it a try. Now the task at hand was quite simple really and by no means did I over complicate anything. I just wanted to take the files and load them to help my buddy out. Took me all of 8 minutes to whip a quick package up. Below is a representation of how I accomplished this; I will try to elaborate step by step.

I’m assuming that one already knows how to open SSIS in Studio…….

Step 1: Connection Managers – will need 3 of them

  1. Connection Manager (new database connection) will house the server name and database on the server
  2. SMTP Connection Manager – this pertains to the email functionality and the SMTP server
  3. File Connection Manager – empty connection for existing file that will be used in a later step

Step 2: From the toolbox I selected my “For Each Loop Container” this will allow me to loop through “all” files that I’m looking for in my specified directory. Double click the Container and or go to the properties and you will be presented with the below window. For my testing purposes I created a test folder called Test1 on my local drive. The files that I need to loop through are .txt files; in my example I am going to load all .txt files within the directory.

Step 3: From the tool box drag the Execute SQL Task over into the For Each Loop Container.

Double click on the Execute task to go to the properties section. The connection will need to be the connection from the Connection Manager that was setup in Step 1. Next the FileConnection will need to be the SQL connection set up in the Connection Manager that was established also in Step 1.

Step 4: I always set up a SMTP Connection Manager so I can utilize the send mail task from the tool box. This allows for me to communicate on failure or success to the appropriate parties. Simply drag two send mail tasks from the tool box and in the properties section add your SMTP server and how you want to connect. Click on the for each loop container and drag the arrow to your send mail tasks; to change for failure right click the arrow and select on failure.

Pretty simple in a nut shell. I felt the tools were very easy to use and while SSIS does a lot more powerful operations this solution provided a way for me to loop through my files in the designated directory, load them in without issues, and a method for notification.

Not covered in here but I will touch on is the fact that I liked the ispacs that deployment utility created. I found it much easier than using the manifest etc. All in all I was quite pleased.