SSIS Junkie : Powershell

Export all SSIS packages from msdb using Powershell

jamiet — Wed, 02 Feb 2011 07:45:00 GMT

Have you ever wanted to dump all the SSIS packages stored in msdb out to files? Of course you have, who wouldn’t? Right? Well, at least one person does because this was the subject of a thread (save all ssis packages to file) on the SSIS forum earlier today.

Some of you may have already figured out a way of doing this but for those that haven’t here is a nifty little script that will do it for you and it uses our favourite jack-of-all tools … Powershell!!

Imagine I have the following package folder structure on my Integration Services server (i.e. in [msdb]):

There are two packages in there called “20110111 Chaining Expression components” & “Package”, I want to export those two packages into a folder structure that mirrors that in [msdb]. Here is the Powershell script that will do that:

Param($SQLInstance = "localhost")

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

$Packages =  Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query "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%'"

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"
}

To run it simply change the “localhost” parameter of the server you want to connect to either by editing the script or passing it in when the script is executed. It will create the folder structure in C:\Temp (which you can also easily change if you so wish – just edit the script accordingly). Here’s the folder structure that it created for me:

Notice how it is a mirror of the folder structure in [msdb].

Hope this is useful!

@Jamiet

UPDATE: THis post prompted Chad Miller to write a post describing his Powershell add-in that utilises a SSIS API to do exporting of packages. Go take a read here: http://sev17.com/2011/02/importing-and-exporting-ssis-packages-using-powershell/

Generate multiple SqlCmdVars files in your database projects

jamiet — Wed, 19 Jan 2011 20:24:59 GMT

Earlier today I was doing a little work using datadude/DBPro/Visual Studio Database Tools/pick your name and had a need to write a Powershell script that I think might be useful to other folks so I’m sharing it here.

Often when you’re putting together database projects you will have a need for multiple .sqlcmdvars files – one for each environment that you are deploying to. It can be a real pain in the neck maintaining multiple .sqlcmdvars files because you need to make sure that:

each file has the same list of sqlcmd variables in it
the variables that need to be different per environment have got the correct values in them

If you have multiple environments and multiple projects then this can become a real headache to maintain – indeed, the project that I am working on at the moment has got 192 sqlcmdvars files. You can trust me that that is pretty excessive however this problem is still one that many people are going to encounter.

So, when you have 192 occurences of the same problem it seems logical to write a script to carry out the repetitive task of editing those files and that is exactly what I did today. My Powershell script requires that you maintain ONE .sqlcmdvars file per project and will then take care of generating the others and also replacing environment-specific variable values where appropriate. You need to supply a list of a projects, a list of environments and, for each environment, a value for each variable that you want to change. the example below has only one project, three environments (thus three .sqlcmdvars files) and one environment-specific variable. If you have anything more complex than that then the script is very easy to change and is self-explanatory.

Here’s a screenshot of my demo project:

Note the following:

There are three sqlcmdvars files {development.sqlcmdvars,production.sqlcmdvars,test.sqlcmdvars}
development.sqlcmdvars contains a variable called $(EmailAddress) that is not in either of the other two
My Powershell script is stored as a solution file

Here is the script:

#This script copies the contents of each <defined>.sqlcmdvars into all the other .sqlcmdvars files for that project
#It should live in the root folder of your solution
#If your sqlcmdvars files are under source control don’t forget to check them out
cls
$masterSqlCmdVars = "development"    #Edit these two
$variableName = "EmailAddress"        #values accordingly

$DbArray = ('20110119 Test Sqlcmdvars Generator') #List all your .dbproj files
foreach ($Db in $DbArray)
{
    #List of environments and the property values for each respective environment
    $EnvArray = (
                    ('test','test@example.com'),
                    ('production','production@example.com')
                )
    foreach ($Env in $EnvArray)
    {
        #STEP 1 - copy the file
        $EnvName = $Env[0]
        $VarValue = $Env[1]
        $source = "$Db\Properties\$masterSqlCmdVars.sqlcmdvars"
        $destination = "$Db\Properties\$EnvName.sqlcmdvars"
        "Copying $source to $destination"
        copy $source $destination

        #STEP 2 - edit with new variable values
        $xml = [xml](Get-Content $destination)
        $root = $xml.get_DocumentElement();
        foreach ($property in $root.Properties.ChildNodes)
        {
            if ($property.PropertyName -eq $variableName)
            {
                $property.Propertyvalue = $VarValue
            }
        }
        "Saving modified XML to $destination"
        $destination = resolve-path $destination
        $xml.Save($destination)
    }
}

It has an outer loop for all the projects and an inner loop for each environment. On each iteration it copies the contents of development.sqlcmdvars into $EnvName.sqlcmdvars and then updates the file with the new value for $(EmailAddress). If you have more variables that you need to edit then its not a great hardship to adapt the script accordingly.

Upon completion our test.sqlcmdvars and production.sqlcmdvars files have been edited as so:

That’s it. Pretty simple. I won’t go deep into explaining the script because if this is useful to you you will probably want to do that yourself. This demo project, including the Powershell script, is available for download at http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20110119/20110119%20Test%20Sqlcmdvars%20Generator.zip.

@Jamiet

UPDATE: I have discovered an alternative method courtesy of Robert Robelo and I like this one because it uses XPath which to me feels like a purer way of doing this – I can’t explain why so don’t ask! Here’s is Robert’s code which achieves the same as above:

cls
$masterSqlCmdVars = "development"    #Edit these two
$variableName = "EmailAddress"        #values accordingly

$DbArray = ('20110119 Test Sqlcmdvars Generator') #List all your .dbproj files
foreach ($Db in $DbArray)
{
    #List of environments and the property values for each respective environment
    $EnvArray = (
                    ('test','test@example.com'),
                    ('production','production@example.com')
                )
    foreach ($Env in $EnvArray)
    {
        #STEP 1 - copy the file
        $EnvName = $Env[0]
        $VarValue = $Env[1]
        $source = "$Db\Properties\$masterSqlCmdVars.sqlcmdvars"
        $destination = "$Db\Properties\$EnvName.sqlcmdvars"
        "Copying $source to $destination"
        copy $source $destination
        
        [xml]$xml = Get-Content $destination
        $nsMgr = New-Object Xml.XmlNamespaceManager $xml.NameTable
        $prefix = 'ns'
        $uri = 'urn:Microsoft.VisualStudio.Data.Schema.Package.SqlCmdVars'
        $nsMgr.AddNamespace($prefix, $uri)
        $xpath = "//${prefix}:Property[./${prefix}:PropertyName='$variableName']"
        $node = $xml.SelectSingleNode($xpath, $nsMgr)
        $node.PropertyValue = $VarValue
        $xml.Save($destination)
        Get-Content $destination
    }
}

I have also updated the downloadable file at http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20110119/20110119%20Test%20Sqlcmdvars%20Generator.zip with Robert’s code.

Querying Visual Studio project files using T-SQL and Powershell

jamiet — Mon, 17 Jan 2011 21:11:00 GMT

Earlier today I had a need to get some information out of a Visual Studio project file and in this blog post I’m going to share a couple of ways of going about that because I’m pretty sure I won’t be the only person that ever wants to do this. The specific problem I was trying to solve was finding out how many objects in my database project (i.e. in my .dbproj file) had any warnings suppressed but the techniques discussed below will work pretty well for any Visual Studio project file because every such file is simply an XML document, hence it can be queried by anything that can query XML documents.

Ever heard the phrase “when all you’ve got is hammer everything looks like a nail”? Well that’s me with querying stuff – if I can write SQL then I’m writing SQL. Here’s a little noddy database project I put together for demo purposes:

Two views and a stored procedure, nothing fancy. I suppressed warnings for [View1] & [Procedure1] and hence the pertinent part my project file looks like this:

  <ItemGroup>
    <Build Include="Schema Objects\Schemas\dbo\Views\View1.view.sql">
      <SubType>Code</SubType>
      <SuppressWarnings>4151,3276</SuppressWarnings>
    </Build>
    <Build Include="Schema Objects\Schemas\dbo\Views\View2.view.sql">
      <SubType>Code</SubType>
    </Build>
    <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procedure1.proc.sql">
      <SubType>Code</SubType>
      <SuppressWarnings>4151</SuppressWarnings>
    </Build>
  </ItemGroup>
  <ItemGroup>

Note the <SuppressWarnings> elements – those are the bits of information that I am after.

With a lot of help from folks on the SQL Server XML forum I came up with the following query that nailed what I was after. It reads the contents of the .dbproj file into a variable of type XML and then shreds it using T-SQL’s XML data type methods:

DECLARE @xml XML; 
SELECT @xml = CAST(pkgblob.BulkColumn AS XML) 
FROM   OPENROWSET(BULK 'C:\temp\QueryingProjectFileDemo\QueryingProjectFileDemo.dbproj' -- <-Change this path! 
                   ,single_blob) AS pkgblob 
                   
;WITH XMLNAMESPACES( 'http://schemas.microsoft.com/developer/msbuild/2003' AS ns) 
SELECT  REVERSE(SUBSTRING(REVERSE(ObjectPath),0,CHARINDEX('\',REVERSE(ObjectPath)))) AS [ObjectName] 
       ,[SuppressedWarnings] 
FROM   ( 
       SELECT  build.query('.') AS [_node] 
       ,       build.value('ns:SuppressWarnings[1]','nvarchar(100)') AS [SuppressedWarnings] 
       ,       build.value('@Include','nvarchar(1000)') AS [ObjectPath] 
       FROM    @xml.nodes('//ns:Build[ns:SuppressWarnings]') AS R(build) 
       )q

And here’s the output:

And that’s it – an easy way of discovering which warnings have been suppressed and for which objects in your database projects. I won’t bother going over the code as it is fairly self-explanatory – peruse it at your leisure.

Once I had the SQL above I figured I’d share it around a little in case it was ever useful to anyone else; hence I’m writing this blog post and I also posted it on the Visual Studio Database Development Tools forum at FYI: Discover which objects have had warnings suppressed. Luckily Kevin Goode saw the thread and he posted a different solution to the same problem, one that uses Powershell. The advantage of Kevin’s Powershell approach is that it is easy to analyse many .dbproj files at the same time. Below is Kevin’s code which I have tweaked ever so slightly so that it produces the same results as my SQL script (I just want any object that had had a warning suppressed whereas Kevin was querying specifically for warning 4151):

cd 'C:\Temp\QueryingProjectFileDemo\'
cls
$projects = ls -r -i *.dbproj
Foreach($project in $projects)
{
  $xml = new-object System.Xml.XmlDocument
  $xml.set_PreserveWhiteSpace( $true )
  $xml.Load($project)

  #$xpath = @{Start="/e:Project/e:ItemGroup/e:Build[e:SuppressWarnings=4151]/@Include"}
  #$xpath = @{Start="/e:Project/e:ItemGroup/e:Build[contains(e:SuppressWarnings,'4151')]/@Include"}
  $xpath = @{Start="/e:Project/e:ItemGroup/e:Build[e:SuppressWarnings]/@Include"}
  $ns = @{ e = "http://schemas.microsoft.com/developer/msbuild/2003" }

  $xml | Select-Xml -XPath $xpath.Start  -Namespace $ns |Select -Expand Node | Select -expand Value
}

and here’s the output:

Nice reusable Powershell and SQL scripts – not bad for an evening’s work. Thank you to Kevin for allowing me to share his code.

Don’t forget that these techniques can easily be adapted to query any Visual Studio project file, they’re only XML documents after all! Doubtless many people out there already have code for doing this but nonetheless here is another offering to the great script library in the sky. Have fun!

@Jamiet

This blog post was mentioned on This Week on Channel 9. Fast forward to 10m49s.

A strategy for managing security for different environments using the Database Development Tools in Visual Studio 2010

jamiet — Wed, 21 Jul 2010 20:59:00 GMT

Intro

Of late I have been getting down and dirty with the Database Development tools in Visual Studio 2010. You may know this feature set by one of the plethora of other names it has had over recent years such as:

Visual Studio Team System for Database Professionals
DBPro
Datadude

For the rest of this post I’ll stick with the colloquial name that most people seem to recognise – datadude.

Regardless of which moniker you prefer I’m hoping you recognise the feature set that I am referring to here which includes:

Schema Compare
Data Compare
Offline database development
T-SQL refactoring
Database unit testing/Data generation
MSBuild integration
various other things…

I plan to write a detailed blog post in the coming weeks talking about my overall experiences with datadude but in the meantime I’m writing this blog post to focus on how I have chosen to manage security; by which I mean:

logins
users
role membership
user and role permissions

Security in Post-Deployment scripts

Datadude has built-in support for managing all of this stuff as we can see from this screenshot:

Notice that we have created a user, a login for that user and a role into which we later want to add the user. They exist as explicit objects within our project which means that datadude “knows” about them and that has a number of advantages including:

Datadude can include these objects in a schema compare
Datadude can do dependency tracking thus enabling offline development (e.g. If the login that is referenced in the CREATE USER statement does not exist then a design-time error will be raised)

That should be enough to justify using datadude to manage users and roles however I have come across a limitation that prohibits us from doing so; we do not deploy the same security model to each of our environments. The reasons for this are very familiar to you I’m sure, we have people that require different permissions on different environments (e.g. our developers will want to be able to issue DDL and UPDATE/INSERT/DELETE on our development environment but we definitely don’t want them to have the same on production).

As far as I can discern datadude does not have a good solution for dealing with this situation. I have not found a way of conditionally deploying objects depending on which environment we are deploying to. Actually that is not altogether true, we could have a Visual Studio configuration for each environment and include/exclude objects as we choose but that doesn’t play well with the build and deploy model of msbuild which requires that you build each one of those configurations; each of our builds takes about 10 minutes which, when multiplied by the number of environments, results in a total build time that takes too long for our continuous integration (CI) usage.

Hence we needed a better way to deploy our security model – we chose to use PostDeployment scripts. PostDeployment scripts do exactly what they say on the tin – they are scripts that get run after datadude has deployed the objects that it knows about. They get called (using SQLCMD syntax) from the Script.PostDeployment.sql file that exists in every datadude project:

My chosen approach is to have a script called SecurityAdditionsWrapper.sql that gets called from Script.PostDeployment.sql. SecurityAdditionsWrapper.sql checks a SQLCMD variable to determine which environment is being deployed to and then calls the appropriate SecurityAdditionsXXX.sql script that is responsible for deploying the security model for that environment (where XXX is the environment).

I then have 3 subfolders that contain all the individual CREATE USER, role membership and permissions scripts that get called from SecurityAdditionsXXX.sql as and when required.

The screenshot below shows the contents of Script.PostDeployment.sql & SecurityAdditionsWrapper.sql and also the files as they exist in Solution Explorer:

Some things to notice:

I have a SQLCMD variable called $(DeployType) that will contain the value “DEV”, “UAT” or “PRD”
All of my security scripts exist in Post-Deployment -> SecurityAdditions
In the commented-out code you can see that I was trying to be clever and dynamically build the name of the file to be executed using the $(DeployType) variable – the thinking being that if we later added a new environment type I wouldn’t need to change this script. Unfortunately even though this is valid SQLCMD syntax it does not work in datadude; I have reported that issue on Connect as a bug (with repro) at [Datadude] Valid SQLCMD syntax throws error at build time.

You may be wondering how we get a value into that $(DeployType) variable when using msbuild. Well, its pretty simple when you know how. First you need to include the variable in the sqlcmdvars file for your datadude project:

You then need to pass in the appropriate value to that variable at deploy-time. We are using VSDBCMD.exe to run our deployments and the syntax to pass in a value for a variable is /p:PropertyName=PropertyValue. We are calling VSDBCMD.exe from inside an msbuild script which therefore looks like this:

$(DeployType) is of course also a property within our msbuild file, which I am drawing attention to in the bold section above.

Wrap-Up

In conclusion, I am not saying that this is the correct way to deploy a SQL Server security model using datadude, it just happens to be the method that I have chosen. I would really like to how people have solved this using datadude so if you have any anecdotes to share please put them in the comments.

One more thing…

Cranking all of those files out by hand is a laborious undertaking, especially if you have lots of users with lots of permissions. In our case we introduced datadude to be used with an existing system hence we already had all our security setup on existing servers but we had no good way of getting it into the structure that I explained above. Powershell and SQLCMD to the rescue!

I have produced a series of scripts that interrogate a named instance and produce all of the files that I explain above. Namely:

A file for each user which gets dropped into %SolutionRoot%\%ProjectName%\Scripts\Post-Deployment\Users\
A file for each principle that issues all the GRANT/DENY EXECUTE/SELECT/INSERT/UPDATE/DELETE for that principle, it gets dropped into %SolutionRoot%\%ProjectName%\Scripts\Post-Deployment\PermissionsSets\
A file that specifies all of the role memberships, for all database principles.

Creating the CREATE USER file and the file containing the permissions for each principle is quite involved. For that we need some nested loops that loop over (a) all the databases for which you want to scripts users and (b) all the principles in each database. It then dumps the appropriate files into the appropriate folders and then you have the simple task of adding them to your project and calling them from your Post Deployment script.

The files are stored in a zip folder on my SkyDrive at http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20100721/GeneratePermissions.zip

To run the Powershell script simply unzip all the files into the root folder of your solution and issue:

>GeneratePermissions.ps1 -SQLInstance instanceName -Environment XXX

changing SQLInstance & XXX accordingly (the $Environment variable is simply a suffix that is added to the names of some of the generated files). The Powershell script contains more detailed notes within it in the comments section at the top.

Hope this helps!

Disclaimer: I’m giving this away for free and I won’t be supporting it so use at your own peril (in other words, don’t come here complaining that it doesn’t work on your environment). On the other hand if you find a problem and are able to fix it – please let me know

UPDATE: I did actually find some problems with the scripts and have now uploaded a new set. I'm confident that they'll work this time.

UPDATE 2: I've updated the scripts again. They now create the SecurityAdditionsXXX.sql file as well as the individual CREATE USER & permissions files; SecurityAdditionsXXX.sql contains all of the calls to the relevant scripts for environment XXX.

SecurityAdditionsXXX.sql also issues all of the role membership statements as well. So just to clarify - you now no longer have a need to run the script that generates the role memberships - GeneratePermissions.ps1 takes care of everything.

UPDATE 3: Peter Schott has taken this, adapted it for hiw own needs, and sent it back to me so that I can share it here. His modified version of the scripts is at http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20100721/GeneratePermissions%5E_PeterSchott.zip and includes these changes (mainly to deal with roles):

1. Changed the Powershell script:
* Added a section to handle role permissions. (really don't care for the way VS does it)
* Tweaked the Database Principle section - for some reason, the variable in the PS script I extracted wasn't set properly so I just changed the names around
* Added a bunch of lines of code to add to the Array. My Powershell's not too strong so wasn't sure about the best way to add new DBs/Projects to the DB Array. I ended up doing a bunch of sets. I know it's not ideal, but I just wanted to get it done one time.
* Tweaked the section that creates the Users. If one of those logins doesn't exist, the grant permissions script would stop. Now I check to make sure that the login exists before the script runs. I sometimes get empty (Login = []) sections. No ideas on that yet. I fix or delete that file as appropriate.

2. Added a "GetDatabaseRoleList.sql" file. Gets the roles for the DB so we can run it through the Permissions file

3. Permissions SQL file. I tweaked this with a query that UNIONs the object permissions with the Schema permissions. I didn't break it down further
like you did, but our permissions are so messed up right now that it doesn't matter much to me. (we have far too many permissions granted to
individual users instead of roles because the prior DBA didn't understand the point of DB Roles for permissions and even then far too many permissions because we had a job that nightly granted all permissions on all objects to certain usernames :-P )

And one more change that needs to be made which Peter informed me of later:

Ended up changing the GeneratePermissions.ps1 file to change all of the Out-File
to "Out-File -width 500 ".

After doing that, the permissions weren't truncated at 80 characters. Also tweaked the Invoke-SQLCmd functions to use -MaxCharLength 500 so the output line would be long enough. That may have been overkill or there may be a better way to handle, but some of my permission lines were definitely longer than the allowed line lengths.

@Jamiet

P.S. I have used Powershell in the past but this is the first time I have really got into the inner workings of it and I’ve got to tell you – its just fantastic. The productivity you get is just incredible – if you don’t believe me have a look at the script herein and see how easy it is to generate a dataset from SQL Server and then loop over it inside your script. Its two lines of code - really fantastic stuff – I recommend any SQL Server pro go out and spend some time getting to know Powershell.