CODEWITHSUNDEEP
sql-serverpowershellssis
user3243678
user3243678

Reputation: 11

Reading an SSIS package using powershell when the package is stored in an Integration Service server

I'm using below powershell script to read Version Build from a dtsx pkg(read as an xml). I'm able to read the file as long as it's in my local or shared path. However, one of the packages are on an Integration Services server path and I'm not able to simply use get-content on that path. Below is the code I'm using for local/shared path files.

$xml = [xml](get-content *filepath*)
$value = $xml.Executable.Property | Where-Object {$_.Name -like 'VersionBuild'}
$ver_dev = $value.'#text'

I read on the internet about net use but I guess that's just for mapping shared path, the integration server path is not really a shared path as we can only access it using SQL server(as per my understanding), the path is as displayed in sql server is as follows Server_path

I also came across some code invoking packages in SQL server using powershell, however I am novice in powershell(only started for this version compare purpose) and could hardly understand anything.

The ocde in itself is simple and frankly that's what motivated me to automate this Version compare, however I'm stuck on reading from this server path. Any help would be greatly appreciated. Thanks

Upvotes: 1

Views: 2031

Answers (1)

TechSpud
TechSpud

Reputation: 3518

If your packages are stored in an Integration Services Catalog, the code in this link below will save an .ispac file (which is a zip file) to local storage and extract the contents. The code below, based on that, goes as far as downloading a specific .ispac project file. From there, you'll need to extract the dtsx package (as in the linked example).

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$SsisServer = 'SERVERNAME'
$Folder = 'FOLDER'
$ProjectName = 'PROJECT'

## Build the list of SQL Server names.
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server $SsisServer

$SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
[System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null

$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

$Catalog = $IntegrationServices.Catalogs["SSISDB"]
$oFolder = $Catalog.Folders[$Folder]
$oProject = $oFolder.Projects[$ProjectName]
$ISPAC = $oProject.GetProjectBytes()
[System.IO.File]::WriteAllBytes(($DownloadFolder + "\" + $oProject.Name + ".ispac"),$ISPAC)

If your packages are stored in msdb, the code below, from Jamie Thomson, will download all packages to local storage.

Param($SQLInstance = "localhost")

#####Add all the SQL goodies (including Invoke-Sqlcmd)#####
add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue
add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue
cls 

$ssisSQL = "WITH cte AS (
        SELECT    cast(foldername as varchar(max)) as folderpath, folderid
        FROM    msdb..sysssispackagefolders
        WHERE    parentfolderid = '00000000-0000-0000-0000-000000000000'
        UNION    ALL
        SELECT    cast(c.folderpath + '\' + f.foldername  as varchar(max)), f.folderid
        FROM    msdb..sysssispackagefolders f
        INNER    JOIN cte c        ON    c.folderid = f.parentfolderid
    )
    SELECT    c.folderpath,p.name,CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as pkg
    FROM    cte c
    INNER    JOIN msdb..sysssispackages p    ON    c.folderid = p.folderid
    WHERE    c.folderpath NOT LIKE 'Data Collector%'"

$Packages =  Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query $ssisSQL

Foreach ($pkg in $Packages)
{
    $pkgName = $Pkg.name
    $folderPath = $Pkg.folderpath
    $fullfolderPath = "c:\temp\$folderPath\"
    if(!(test-path -path $fullfolderPath))
    {
        mkdir $fullfolderPath | Out-Null
    }
    $pkg.pkg | Out-File -Force -encoding ascii -FilePath "$fullfolderPath\$pkgName.dtsx"
}

Upvotes: 1

Related Questions