Retrieve SSIS package contents

Here’s a quick .NET console application to retrieve the DTSX XML from SQL Server for an SSIS package:

using System.Collections.Generic;
using System.Data;
using System.IO;
using Microsoft.Data.SqlClient;

async IAsyncEnumerable<byte[]> ReadFileAsync()
int startingByte = 1;
while (true)
byte[] bytes;
using var conn = new SqlConnection("server=XXXXX;database=msdb;integrated security=true;");
await conn.OpenAsync().ConfigureAwait(false);
using var comm = conn.CreateCommand();
comm.CommandText = @"
            SELECT substring(packagedata, @StartingByte, 8000) [FileContents]
            FROM msdb.dbo.sysssispackages
            WHERE name = 'MyPackageName' -- assuming your package has a unique name on the server
comm.Parameters.Add(new SqlParameter("@StartingByte", SqlDbType.Int) { Value = startingByte });
using var rdr = await comm.ExecuteReaderAsync().ConfigureAwait(false);
if (!await rdr.ReadAsync().ConfigureAwait(false))
bytes = (byte[])rdr[0];
if (bytes == null || bytes.Length == 0)
startingByte += bytes.Length;
yield return bytes;

var fileWriter = File.OpenWrite("output.xml");
await foreach (var byteArray in ReadFileAsync())
fileWriter.Write(byteArray, 0, byteArray.Length);

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s