Friday 22 April 2016

Get packages from SSIS Catalog

Case
I'm at a client and they lost the original SSIS Visual Studio projects. I have the packages deployed in the SSIS catalog. Is there a way to retrieve the packages from the catalog and create SSIS project from them?
Integration Services Catalog













Solution
First you should tell your client that they need to start using TFS (or similar versioning software)! And then you could use one of these three methods to recreate the SSIS projects. The catalog contains the packages, connection managers and parameters, but not the original Visual Studio (SSDT) projects, but recreating is quite easy.

A. Integration Service Import Project Wizard
B. Export in SSMS
C. PowerShell to the rescue



A. Integration Service Import Project Wizard
If your development PC, with SSDT/Visual Studio installed, can connect to the SQL Server with the catalog on it then this method is the easiest. Open SSDT, because it has an SSIS project with an import wizard!

1) Create a new Integration Services project
Instead of using the standard "Integration Services" - project, we need to use the "Integration Services Import Project Wizard" - project. For the project name you should use the name of the project in the SSIS catalog and for the solution name you could use the name of project folder from the SSIS catalog.
Integration Services Import Project Wizard




















2) Wizard
Now the wizard will appear which allows you to select an SSIS Catalog project and export its content to your new SSIS project in Visual Studio. With just a few clicks your project is ready to use.
Animated GIF of wizard


























B. Export in SSMS
If you cannot reach the server from within Visual Studio then there is an other option. Start SQL Server Management Studio and go to the SSIS Catalog. It has an option the export projects to an ISPAC file.

1) Right click your project and choose Export
When you export the project, you need to save the ISPAC file to a diskdrive or network folder. Use the project name from the catalog as the filename. 
Add caption
















1b) Integration Service Import Project Wizard
If you saved the ISPAC file to a folder which can be reached from your development PC you could either go to method A and use the wizard to import the ISPAC file to your project or continue to step 2 and use an alternative method to recreate an SSIS project in SSDT.


2) Extract ISPAC file
If you saved the file as XXXX.ispac then you first need to rename the .ISPAC file to .ZIP, after that you can to extract all files from that zipfile.
Unzip the ISPAC file




















3) Create new SSIS project
Because the solution and project files aren't saved within the Catalog you need to create a new/empty project in SSDT. For the projectname you should use the name of the project in the SSIS catalog and for the solution name you could use the name of project folder from the SSIS catalog.
Create new SSIS project in SSDT/Visual Studio
















4) Add items from unzipped ISPAC
First remove the default package.dtsx file and then right click the project and choose Add, Existing Item... Then browse to your unzipped ISPAC file and select all files, except "@Project.manifest" and "[Content_Types].xml" (if you forget that the will end up in the Miscellaneous folder from your project).

If you import the Project.params file it will notice that there is already one. Replace it and after that reload the file.











After that you have your SSIS project back and it's probably time to put it under Source Control. Don't forget to change/check the package protection level of your packages and project.
Add project/solution to source control












C. PowerShell
If you have a lot ispac files to download then you could also use PowerShell. The only thing this solution doesn't do is creating a project in Visual Studio. You could either use method A or B for that or you could even try to create the XML for the xxxx.dtproj file with PowerShell. (download example)

 
#PowerShell: DownloadIspac.ps1
################################
########## PARAMETERS ##########
################################ 
# Change Server, folder, project and download folder
$SsisServer = "MyServer\SQL2014" # Mandatory
$FolderName = "MyFolder"         # Can be empty to download multiple projects
$ProjectName = "MyProject"       # Can be empty to download multiple projects
$DownloadFolder = "D:\MyIspacs\" # Mandatory
$CreateSubfolders = $true        # Mandatory
$UnzipIspac = $false             # Mandatory


#################################################
########## DO NOT EDIT BELOW THIS LINE ##########
#################################################
clear
Write-Host "========================================================================================================================================================"
Write-Host "== Used parameters =="
Write-Host "========================================================================================================================================================"
Write-Host "SSIS Server             :" $SsisServer
Write-Host "Folder Name             :" $FolderName
Write-Host "Project Name            :" $ProjectName
Write-Host "Local Download Folder   :" $DownloadFolder
Write-Host "Create Subfolders       :" $CreateSubfolders
Write-Host "Unzip ISPAC (> .NET4.5) :" $UnzipIspac
Write-Host "========================================================================================================================================================"


##########################################
########## Mandatory parameters ##########
##########################################
if ($SsisServer -eq "")
{
    Throw [System.Exception] "SsisServer parameter is mandatory"
}
if ($DownloadFolder -eq "")
{
    Throw [System.Exception] "DownloadFolder parameter is mandatory"
}
elseif (-not $DownloadFolder.EndsWith("\"))
{
    # Make sure the download path ends with an slash
    # so we can concatenate an subfolder and filename
    $DownloadFolder = $DownloadFolder = "\"
}


############################
########## SERVER ##########
############################
# Load the Integration Services Assembly
Write-Host "Connecting to server $SsisServer "
$SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
[System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null;

# Create a connection to the server
$SqlConnectionstring = "Data Source=" + $SsisServer + ";Initial Catalog=master;Integrated Security=SSPI;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring

# Create the Integration Services object
$IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection

# Check if connection succeeded
if (-not $IntegrationServices)
{
    Throw [System.Exception] "Failed to connect to server $SsisServer "
}
else
{
    Write-Host "Connected to server" $SsisServer
}


#############################
########## CATALOG ##########
#############################
# Create object for SSISDB Catalog
$Catalog = $IntegrationServices.Catalogs["SSISDB"]

# Check if the SSISDB Catalog exists
if (-not $Catalog)
{
    # Catalog doesn't exists. Different name used?
    Throw [System.Exception] "SSISDB catalog doesn't exist."
}
else
{
    Write-Host "Catalog SSISDB found"
}


############################
########## FOLDER ##########
############################
if ($FolderName -ne "")
{
    # Create object to the folder
    $Folder = $Catalog.Folders[$FolderName]
    # Check if folder exists
    if (-not $Folder)
    {
        # Folder doesn't exists, so throw error.
        Write-Host "Folder" $FolderName "not found"
        Throw [System.Exception] "Aborting, folder not found"
    }
    else
    {
        Write-Host "Folder" $FolderName "found"
    }
}


#############################
########## Project ##########
#############################
if ($ProjectName -ne "" -and $FolderName -ne "")
{
    $Project = $Folder.Projects[$ProjectName]
    # Check if project already exists
    if (-not $Project)
    {
        # Project doesn't exists, so throw error.
        Write-Host "Project" $ProjectName "not found"
        Throw [System.Exception] "Aborting, project not found"
    }
    else
    {
        Write-Host "Project" $ProjectName "found"
    }
}


##############################
########## DOWNLOAD ##########
##############################
Function DownloadIspac
{
    Param($DownloadFolder, $Project, $CreateSubfolders, $UnzipIspac)
    if ($CreateSubfolders)
    {
        $DownloadFolder = ($DownloadFolder + $Project.Parent.Name)
    }

    # Create download folder if it doesn't exist
    New-Item -ItemType Directory -Path $DownloadFolder -Force > $null

    # Check if new ispac already exists
    if (Test-Path ($DownloadFolder + $Project.Name + ".ispac"))
    {
        Write-Host ("Downloading [" + $Project.Name + ".ispac" + "] to " + $DownloadFolder + " (Warning: replacing existing file)")
    }
    else
    {
        Write-Host ("Downloading [" + $Project.Name + ".ispac" + "] to " + $DownloadFolder)
    }

    # Download ispac
    $ISPAC = $Project.GetProjectBytes()
    [System.IO.File]::WriteAllBytes(($DownloadFolder + "\" + $Project.Name + ".ispac"),$ISPAC)
    if ($UnzipIspac)
    {
        # Add reference to compression namespace
        Add-Type -assembly "system.io.compression.filesystem"

        # Extract ispac file to temporary location (.NET Framework 4.5) 
        Write-Host ("Unzipping [" + $Project.Name + ".ispac" + "]")

        # Delete unzip folder if it already exists
        if (Test-Path ($DownloadFolder + "\" + $Project.Name))
        {
            [System.IO.Directory]::Delete(($DownloadFolder + "\" + $Project.Name), $true)
        }

        # Unzip ispac
        [io.compression.zipfile]::ExtractToDirectory(($DownloadFolder + "\" + $Project.Name + ".ispac"), ($DownloadFolder + "\" + $Project.Name))

        # Delete ispac
        Write-Host ("Deleting [" + $Project.Name + ".ispac" + "]")
        [System.IO.File]::Delete(($DownloadFolder + "\" + $Project.Name + ".ispac"))
    }
    Write-Host ""
}


#############################
########## LOOPING ##########
#############################
# Counter for logging purposes
$ProjectCount = 0

# Finding projects to download
if ($FolderName -ne "" -and $ProjectName -ne "")
{
    # We have folder and project
    $ProjectCount++
    DownloadIspac $DownloadFolder $Project $CreateSubfolders $UnzipIspac
}
elseif ($FolderName -ne "" -and $ProjectName -eq "")
{
    # We have folder, but no project => loop projects
    foreach ($Project in $Folder.Projects)
    {
        $ProjectCount++
        DownloadIspac $DownloadFolder $Project $CreateSubfolders $UnzipIspac
    }
}
elseif ($FolderName -eq "" -and $ProjectName -ne "")
{
    # We only have a projectname, so search
    # in all folders
    foreach ($Folder in $Catalog.Folders)
    {
        foreach ($Project in $Folder.Projects)
        {
            if ($Project.Name -eq $ProjectName)
            {
                Write-Host "Project" $ProjectName "found in" $Folder.Name
                $ProjectCount++
                DownloadIspac $DownloadFolder $Project $CreateSubfolders $UnzipIspac
            }
        }
    }
}
else
{
    # Download all projects in all folders
    foreach ($Folder in $Catalog.Folders)
    {
        foreach ($Project in $Folder.Projects)
        {
            $ProjectCount++
            DownloadIspac $DownloadFolder $Project $CreateSubfolders $UnzipIspac
        }
    }
}

###########################
########## READY ##########
###########################
# Kill connection to SSIS
$IntegrationServices = $null
Write-Host "Finished, total downloads" $ProjectCount

Output example

Sunday 3 April 2016

Custom SSIS Component: XML Validation Task

Case
A while ago I did a post on validating XML files in SSIS with nested XSD files. The out of the box XML Task doesn't honor nested XSD files with an (include or import). The Script Task workaround is simple, but since I use it a lot I decided to make a task for it.

Solution
The XML Validation Task allows you to specify the XML and XSD filepaths with a Connection Manager or string variable. After that it will either succeed or fail with an error message describing what's wrong with the XML file.
XML Validation Task V0.1

















Please email me (address is under the Help button) bugs and feature requests for this task.
Items to address:
  • More validation to make it really monkey proof
  • Different icon
Thinking about:
  • Providing multiple XSD's (but how many?)
  • Providing XML and/or XSD content (not path) via string variable
  • Option to throw warning instead of error






Disclaimer
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Downloads:
You can download a SSIS 2008, 2012, 2014 and 2016 version on the download page.

V0.1 Initial version for 2008 to 2016
V0.2 ? (Leave a comment)

Installation
The installer registers the DLL in the GAC and copies it to the task folder of SSIS (example: C:\Program Files\Microsoft SQL Server\100\DTS\Tasks\). After that you have to restart BIDS because it caches the GAC on startup. Restarting SQL Server Data Tools is not necessary.

Bugs or feature suggestions
Please contact me or leave a comment if you have any suggestions or found a bug in this Custom task.

Related Posts Plugin for WordPress, Blogger...