A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Showing posts with label powershell. Show all posts
Showing posts with label powershell. Show all posts

Friday, March 16, 2012

Recover CLR assemblies from SQL Server

This is an amazingly short post covering how to get save a CLR assembly out of SQL Server via PowerShell. Original code was on a stackoverflow question I don't have reference to at the moment. Run as is, this code will extract all the user assemblies in a given database catalog and save them out to the C:\ssisdata folder. If you know the particular assembly you want, you can uncomment lines 17 (tsql), 32 (PowerShell) and update line 50 to point to the correct assembly name. Run the query template if you don't know all the assembly names in a given database. And obviously, fix line 48 to point to the correct database.
   1:  #[Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.ManagedDTS") | out-null
   2:   
   3:  $assemblyListQuery = "SELECT A.name FROM sys.assemblies A WHERE A.is_user_defined = 1 ORDER BY 1 ASC "
   4:   
   5:  $queryTemplate = "
   6:  SELECT 
   7:      AF.content
   8:  ,    A.name    
   9:  FROM 
  10:      sys.assembly_files AF 
  11:      INNER JOIN 
  12:          sys.assemblies A 
  13:          ON AF.assembly_id = A.assembly_id 
  14:  WHERE 
  15:      AF.file_id = 1
  16:      AND A.is_user_defined = 1
  17:      --AND A.name = @assemblyName
  18:  "
  19:   
  20:  Function Save-Assemblies()
  21:  {
  22:      param
  23:      (
  24:           [string]$connectionString
  25:      ,   [string]$query
  26:      )
  27:          $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
  28:          $command = New-Object System.Data.SqlClient.SqlCommand($query)
  29:          
  30:          $connection.Open()
  31:          $command.Connection = $connection
  32:          #$hush = $command.Parameters.AddWithValue("@assemblyName", $assemblyName)
  33:          $reader = $command.ExecuteReader()
  34:          
  35:          while ($reader.Read())
  36:          {
  37:              #$bytes = New-Object System.Data.SqlClient.SqlBytes()
  38:              $bytes = $reader.GetSqlBytes(0)
  39:              $name = $reader.GetString(1)
  40:              $bytestream = New-Object System.IO.FileStream("C:\ssisdata\$name.dll", [System.IO.FileMode]::Create)
  41:              $bytestream.Write($bytes.Value, 0, $bytes.Length)
  42:              $bytestream.Close()
  43:          }
  44:              
  45:  }
  46:   
  47:   
  48:  $bnode = "Data Source=localhost;Initial Catalog=FOO;Integrated Security=SSPI;"
  49:   
  50:  $assemblyName = "NiftyAssembly"
  51:      
  52:  Save-Assemblies $bnode $queryTemplate

Now that you have the assembly, use a tool like Reflector or ILSpy to recover as much of the source code as you can. Happy decompiling.

Thursday, August 11, 2011

PowerShell SSIS deployment and maintenance

More than a year ago, I wrote my little post on deploying an SSISManifest file via a PowerShell script which in turn repeatedly invoked dtutil. That was fine and dandy but I've grown since then, taken a new job and discovered that they didn't install all of SQL Server when they created developer machines. We have Visual Studio Team Foundation Server Database Edition aka Data Dude and we have BIDS and the SQL Server client tools installed but there is no joy in Mudville when I attempt to use any of the tools outside of Visual Studio (dtutil, dtexec, dtsinstall).

Invariably, whatever command I attempt to execute will result in an error message stating I don't have integration services installed. dtutil.exe -> "The SQL Server SSIS Package Utilities require Integration Services to be installed by one of these editions of SQL Server 2008 R2: Standard, Enterprise, Developer, or Evaluation. To install Integration Services, run SQL Server Setup and select Integration Services." dtsinstall.exe -> "ERROR: The Package Installation Wizard requires Integration Services to be installed by one of these editions..." dtexec.exe -> "To run a SSIS package outside of Business Intelligence Development Studio you must install Standard Edition of Integration Services or higher." I'm sure from a licensing perspective, this post probably violates the spirit but I'm going to sidestep that for now and assume you, the awesome reader (Hi Mom!), is interested in this solution so that you can have more power over your SSIS installations.

I've learned quite a bit of PowerShell syntax over the past 2 months in my new job. It's nothing I had to do, wasn't even a bullet point on their required skills but I've been needing to glue some things together and PowerShell seemed the perfect tool. As a developer, I really see PowerShell as a scriptable .NET language. It's decent for prototyping and shouldn't raise hackles like "here's an exe, just run it on the server, it's fine"

PowerShell SSIS deployment and maintenance

This script builds on the concepts of my earlier script but ditches wrapping the dtutil method in lieu of directly using the object model. Plus, it feels like the more correct solution. Save this script as something like C:\src\DeployManifest.ps1
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.ManagedDTS") | out-null

# This method will create a folder on SQL Server for SSIS package deployment
Function Create-FolderOnSqlServer
{
    param
    (
        [string]$Server,
        [string]$Folder
    )
    
    $folderDelimter = "\"
    
    try
    {
        # if they supply multiple-level of folders, might need to create whole structure
        # while recursion would be a more elegant solution, my mind is not there
        if ($Folder -ne $folderDelimter)
        {
            $folderList = $Folder.Split($folderDelimter)
        }
        else
        {
            $folderList = [array]$Folder
        }
        
        $app = New-Object Microsoft.SqlServer.Dts.Runtime.Application
        $parentFolder = "\"

        foreach($subFolder in $folderList)
        {
            # test for existing folders
            Write-Debug ([System.string]::Format("parentFolder:{0} folderDelimter:{1} subFolder:{2}", $parentFolder, $folderDelimter, $subFolder))
            $testFolder = $parentFolder + $folderDelimter + $subFolder
            if (!$app.FolderExistsOnSqlServer($testFolder, $Server, $null, $null))
            {
                Write-Debug ([System.string]::Format("Folder {0} does not exist on server, creating", $testFolder))
                $app.CreateFolderOnSqlServer($parentFolder, $subFolder, $Server, $null, $null);
                Write-Host ([System.string]::Format("Created folder {0} on server {1}", $testFolder, $Server))
            }
            else
            {
                Write-Debug "Folder exists on server, doing nothing"
            }
            
            $parentFolder += $folderDelimter + $subFolder
        }
    }
    catch 
    {
        Write-Error ([string]::Format("Failed to create folder {0} on server {1}", $folder, $server))
        Write-Error $_ | fl * -Force
    }
}

# Deploy a package to a folder
Function Copy-Package
{
    param
    (
        [string]$Server,
        [string]$PathToDtsx,
        [string]$ProjectFolder = "\\"
    )
    
    $events = $null
    $userName = $null
    $password = $null
    
    Write-Debug "--------------------------Copy-Package----------------------"
    Write-Debug ([string]::Format("File: {0}", $PathToDtsx))
    Write-Debug ([string]::Format("Server: {0}", $Server))
    Write-Debug ([string]::Format("Folder: {0}", $ProjectFolder))

    if ($ProjectFolder -ne "\\")
    {
        Create-FolderOnSqlServer $Server $ProjectFolder
    }

    Write-Debug ([string]::Format("Attempting to deploy {0} to server {1}", $PathToDtsx, $Server))
    
    try
    {
        $app = New-Object Microsoft.SqlServer.Dts.Runtime.Application
        $package = $app.LoadPackage($PathToDtsx, $null)
        
        $destinationName = "\\"
        if ($ProjectFolder -ne "\\")
        {
            $destinationName = $destinationName + $ProjectFolder + "__"
        }

        # \\folder\\packageName
        $destinationName = $destinationName + $package.Name
        Write-Debug ([string]::Format("Saving as {0}", $destinationName))
        $app.SaveToSqlServerAs($package, $events, $destinationName, $Server, $userName, $password);
        Write-Host ([string]::Format("Deployed {0}", $destinationName))
    }
    catch 
    {
        Write-Error ([string]::Format("Failed to deploy {0} to server {1}", $PathToDtsx, $Server))
        Write-Error $_ | fl * -Force
    }
}

# This method cracks open an SSIS manifest file and deploys all packages to
# the indicated server
Function Deploy-Manifest
{
    param
    (
        [string]$Server,
        [string]$ManifestFile,
        [string]$ProjectFolder = "\\"
    )
    # This deploy script assumes all packages are in the same folder as the 
    # manifest

    $baseFolder = [System.IO.Path]::GetDirectoryName($ManifestFile)

    [xml] $list = Get-Content $ManifestFile

    Write-Debug "--------------------------Deploy-Manifest----------------------"
    Write-Debug ([string]::Format("    Server {0}", $Server))
    Write-Debug ([string]::Format("    ProjectFolder {0}", $ProjectFolder))
    foreach($package in $list.DTSDeploymentManifest.Package)
    {
        $basePackage = [System.IO.Path]::GetFileNameWithoutExtension($package)
        
        # This might need to be a relative path
        $fullyQualifiedPackage = [System.IO.Path]::Combine($baseFolder, $package)
        Write-Debug ([string]::Format("    Package {0}", $fullyQualifiedPackage))
    
        Copy-Package $Server $fullyQualifiedPackage $ProjectFolder
    }
}

# this script is reponsible for enumerating subfolder
# for each manifest it finds, it will invoke the deploy method on it
Function Walk-SubFolder
{
    param
    (
        [string]$RootFolder,
        [string]$DeployServer
    )
    
    $searchPattern = "*.ssisdeploymentmanifest"
    
    foreach($manifestFile in [System.IO.Directory]::GetFiles($RootFolder, $searchPattern, [System.IO.SearchOption]::AllDirectories))
    {
        [xml] $list = Get-Content $manifestFile
        # hard coding the root folder DBServer
        $deployFolder = ([string]::Format("Default\{0}", $list.DTSDeploymentManifest.GeneratedFromProjectName))
        Write-Host ([string]::Format("Deploying manifest file {0}", $manifestFile))
        Deploy-Manifest $DeployServer $manifestFile $deployFolder
    }
}

What I like about this approach is that it let's me get my job done, it allows for unattended installs of SSIS and it handles deploys to subfolders. It still has warts though, I think it's fairly obvious I've not spent a great deal of time polishing it. I'm still learning PowerShell so I was trying to escape backslashes early on but they don't seem to matter, or at least the assembly's methods don't seem to mind doubled and singled backslashes. The error handling is something I've scrounged from the interblags so I don't know if it's the best approach or not.

Usage

The purpose of DeployManifest.PS1 is to crawl all the subfolders from a given root, looking for anything that has an .ssisDeploymentManifest file. For every one of those it finds, it will rip the XML (manifest) apart and extract the project name and the list of packages. It will create a nested folder on the server (Default\ProjectName) and then deploy all the packages Assumptions:
  • Deployments will go to Default\ProjectName
  • The ssisDeploymentManifest will be in the same folder as the dtsx packages it lists
  • You have run this command in PowerShell: Set-ExecutionPolicy Remote-Signed
C:\Src
      \ModuleA\Solution1\ProjectFoo\
                                    ProjectFoo.ssisDeploymentManifest
                                    Package1.dtsx
                                    Package2.dtsx
      \ModuleB\Solution2\Sales\
                               Sales.ssisDeploymentManifest
                               SalesComputeCommission.dtsx
                               SalesGenerateManagerSummaries.dtsx
              \Staging\Staging\
                               Staging.ssisDeploymentManifest
                               StagingLoadFiles.dtsx
Given a structure like the above, we could chose to deploy everything or just a specific project based on the starting folder (Src, ModuleA, Staging). Create a second PowerShell script, this one will actually use the library we built above and saved to C:\Src\DeployManifest.ps1 This script is handy in that it can be dropped at any location in that Src tree and it will deploy everything below it. Simply save the following script as DeployPackages.ps1 and try invoking it from different locations. If it's in \Src, it should find 3 manifests, create 4 folders (Default + 3 project subfolders) and serialize 5 packages into msdb (msdb.dbo.sysssispackages or msdb.dbo.sysdtspackages90 depending on your SQL Server version)
# This script attempts to deploy everything below this folder that has an SSIS manifest file

# This is the "dot include" mechanism for PowerShell which is 
# the PowerShell way of re-using an existing script
. "C:\Src\DeployManifest.ps1"

$deployServer = "localhost\DEV2008R2"
$baseFolder = ".\"
Walk-SubFolder $baseFolder $deployServer

What's left? I have the basics of package delete and a recursive folder delete but I'm reworking it to make it more PowerShell-onic (function names and parameters don't meet conventions etc)

Wednesday, May 25, 2011

Export to CSV from SSMS via PowerShell

On stackoverflow, someone asked how they can get a "real" csv export out of SSMS. Heck, that's easy, that's ummmmmm hmmmm, no. No, that's not going to work either. After some hemming and hawing and staying up too late, here's my initial pass on the solution

Export to CSV from Management Studio

  1. Modify my Powershell export query to csv script to accept a parameter and save that to an accessible location. I used C:\tmp\ExportToCSV.ps1
    # 2011-05-25
    # Bill Fellows
    #
    # This PowerShell script is designed to demonstrate how to run an
    # *arbitrary* query against a database and dump to a csv. 
    #
    # Usage:  Save this file as C:\tmp\ExportToCSV.ps1
    # Start Powershell (Win-R, powershell)
    # Execute the script (C:\tmp\ExportToCSV.ps1)
    
    #
    # If the above fails due to
    # "cannot be loaded because the execution of scripts is disabled on this system"
    # run this command within Powershell
    # Set-ExecutionPolicy RemoteSigned
    # See also http://technet.microsoft.com/en-us/library/ee176949.aspx
    
    # http://www.vistax64.com/powershell/190352-executing-sql-queries-powershell.html
    $server = "localhost"
    $database = "master"
    $query = "SELECT 'highlight some text' AS [I am query] "
    
    if ($args.length -gt 0)
    {
        $query = $args[0]
    }
    
    # powershell raw/verbatim strings are ugly
    # Update this with the actual path where you want data dumped
    $extractFile = @"
    C:\test.csv
    "@
    
    # If you have to use users and passwords, my condolences
    $connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
    $connectionString = [string]::Format($connectionTemplate, $server, $database)
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString
    
    $command = New-Object System.Data.SqlClient.SqlCommand
    $command.CommandText = $query
    $command.Connection = $connection
    
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $command
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $connection.Close()
    
    # dump the data to a csv
    # http://technet.microsoft.com/en-us/library/ee176825.aspx
    $DataSet.Tables[0] | Export-Csv $extractFile
    
  2. Create an external tool that starts PowerShell
    1. Tools, External Tools...
    2. Add
    3. Title: ExporToCSV
    4. Command: %windir%\System32\WindowsPowerShell\V1.0\powershell.exe
    5. Arguments: -File C:\tmp\ExportToCSV.ps1 $(CurText)
    6. Initial Directory:
  3. Highlight the query and under Tools, select ExportToCSV. You should now have a file at C:\test.csv

Limitations

  1. Connection is not dynamic. I have a message out to another developer to see if they have any insight into how I can feed the current connection along and will update if I learn more.
  2. SSMS will prompt you to save every open unsaved window. I typically have 30+ different windows open so you can imagine how painful that can be. The external tool will work just fine whether you save or cancel out of the save.

Tuesday, March 1, 2011

Powershell export query to csv

This morning I saw a tweet from Matt Velic (blog|twitter) and he was trying to run a query, dump the results to a csv and then ftp the file.

sqlcmd csv export

Assuming no commas in the source data, one can do this without using powershell with our old friend SQLCMD.
sqlcmd -S localhost -d master -E -W -w 999 -s "," -Q "SELECT D.* FROM sys.databases D" -o "C:\sqlcmd.csv"

powershell export query results to csv

I'm still a neophyte at this powershell stuff, but here's my 30 second take at it. I like this solution better than the sqlcmd simply because the export library looks cleaner.
# 2011-03-01
# Bill Fellows
#
# This PowerShell script is designed to demonstrate how to run a query
# against a database and dump to a csv
#
# Usage:  Save this file as C:\sandbox\powershell\databaseQuery.ps1
# Start Powershell (Win-R, powershell)
# Execute the script (C:\sandbox\powershell\databaseQuery.ps1)

#
# If the above fails due to
# "cannot be loaded because the execution of scripts is disabled on this system"
# run this command within Powershell
# Set-ExecutionPolicy RemoteSigned
# See also http://technet.microsoft.com/en-us/library/ee176949.aspx


# http://www.vistax64.com/powershell/190352-executing-sql-queries-powershell.html
$server = "localhost"
$database = "master"
$query = "SELECT D.* FROM sys.databases D"

# powershell raw/verbatim strings are ugly
# Update this with the actual path where you want data dumped
$extractFile = @"
C:\test.csv
"@

# If you have to use users and passwords, my condolences
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

# dump the data to a csv
# http://technet.microsoft.com/en-us/library/ee176825.aspx
$DataSet.Tables[0] | Export-Csv $extractFile

Update: Export to CSV from SSMS via PowerShell. This modification to the script allows you to select a query within SSMS and export it to a CSV

Monday, May 3, 2010

PowerShell, dtutil, SSISDeployManifest - How to deploy SSIS packages with PowerShell

I have made no progress since 2008 on the SSIS Uploader. I still think it has merit and I hope to finish it some day. Today is not that day.

Background

At work, we are in the midst of rolling out SQL Server 2008. No R2, just vanilla SQL Server 2008 (named instance) in a side-by-side installation with the 2005 default instance. We are not in production yet but today marked the first issue we had encounted with it. We deploy SSIS packages via the dtsinstaller. It consumes a manifest file (XML) and deploys all of the specified packages into the destination location: file system or into SQL Server (msdb) the installer handles them both. We encountered the following error message today "Unable to find an entry point named 'DTSSKUIsWKGorUnder' in DLL 'dts'. (DTSInstall)." When Google turns up one hit and it's to a Connect article, that's never an encouraging sign. Labouring under the delusion that dtutil will work better than the fancy dtsinstaller, the thought struck me that I could get the benefits of a command line deploy and still incorporate the deployment manifest. For extra points, it seemed like a good reason to use PowerShell to glue it all together.

PowerShell, dtutil, SSISDeployManifest - How to deploy SSIS packages with PowerShell

The following script is my first pass at enumerating the elements of a SSISDeploymentManifest file and using dtutil to install those packages to a 2008 named SQL Server instance. Ain't that a mouthful?

# 2010-04-30
# Bill Fellows
# This PowerShell script is designed to shred an SSISDeploymentManifest
# and deploy file based SSIS packages into a SQL Server instance.
#
# Being the extreme novice with PowerShell, everything is hardcoded and 
# it assumes you have dtutil installed on the same drive as this script.
# Possible enhancements would be to look in the registry for location, 
# accept parameters, encapsulate code into a cmdlet, others as they come up

$format = """\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe"" /file ""{0}"" /DestServer {1} /COPY SQL;""{2}"""

# This deploy script assumes all packages are in the same folder as the 
# manifest
$fileName = "C:\src\SSISHackAndSlash\SSISHackAndSlash\bin\Deployment\SSISHackAndSlash.SSISDeploymentManifest"

# The destination server, this should be a parameter
$destinationServer = "localhost\SQL2008"

$baseFolder = [System.IO.Path]::GetDirectoryName($fileName)

[xml] $list = Get-Content $fileName

foreach($package in $list.DTSDeploymentManifest.Package)
{
    $basePackage = [System.IO.Path]::GetFileNameWithoutExtension($package)   
    
    # This might need to be a relative path
    $fullyQualifiedPackage = [System.IO.Path]::Combine($baseFolder, $package)
    
    $cmd = [string]::Format($format, $fullyQualifiedPackage, $destinationServer, $basePackage)
 
    cmd /c $cmd
}

The script is simple enough. Given a manifest file in the form of
<?xml version="1.0"?> 
<DTSDeploymentManifest GeneratedBy="HOME\administrator" GeneratedFromProjectName="SSISHackAndSlash" GeneratedDate="2010-04-30T21:19:08.6336677-05:00" AllowConfigurationChanges="true">
   <Package>Package.dtsx</Package>
   <Package>Package2.dtsx</Package>
   <Package>Package7.dtsx</Package>
</DTSDeploymentManifest>
it will simply run the following commands
"\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe" /file "C:\src\SSISHackAndSlash\SSISHackAndSlash\bin\Deployment\Package.dtsx" /DestServer localhost\SQL2008 /COPY SQL;"Package"
"\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe" /file "C:\src\SSISHackAndSlash\SSISHackAndSlash\bin\Deployment\Package2.dtsx" /DestServer localhost\SQL2008 /COPY SQL;"Package2"
"\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe" /file "C:\src\SSISHackAndSlash\SSISHackAndSlash\bin\Deployment\Package7.dtsx" /DestServer localhost\SQL2008 /COPY SQL;"Package7"

What I like about this solution
  • Scriptable---our deploys no longer require UI interaction so RDP rights no longer required
  • Uses manifest file---Our devs are trained to look at the manifest as the definitive source for SSIS deployments
  • It uses PowerShell
The big drawback in my mind, is that there is something buggered up with our servers. Ultimately, this is a workaround until we resolve that issue but if it becomes our defacto deploy method, I think we'll be better off.