World of Whatever

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

Find ramblings

Loading...

Monday, September 19, 2016

Generating an SSISDB dacpac

Generating an SSISDB DACPAC

Creating a DACPAC is easy*. Within SSMS, you simply select the database node, Tasks, Extract Data-Tier Application. I had need to get a database reference to the SSISDB for some reporting we were building out so I clicked along my merry way.

Set the properties you're interested in, which is really just file name

The next screen simply validates what you selected previously. It'd be nice if they took their cues from the SSISDeploymentWizard folks and built out your commandline options here but no worries.

And we wait for it to build our package, wait, what? Error?


TITLE: Microsoft SQL Server Management Studio ------------------------------ Validation of the schema model for data package failed. Error SQL71564: Error validating element Signature for '[internal].[check_is_role]': The element Signature for '[internal].[check_is_role]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[deploy_project_internal]': The element Signature for '[internal].[deploy_project_internal]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[set_system_informations]': The element Signature for '[internal].[set_system_informations]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[start_execution_internal]': The element Signature for '[internal].[start_execution_internal]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[stop_operation_internal]': The element Signature for '[internal].[stop_operation_internal]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[validate_package_internal]': The element Signature for '[internal].[validate_package_internal]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[validate_project_internal]': The element Signature for '[internal].[validate_project_internal]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[check_schema_version_internal]': The element Signature for '[internal].[check_schema_version_internal]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[get_database_principals]': The element Signature for '[internal].[get_database_principals]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[get_space_used]': The element Signature for '[internal].[get_space_used]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[get_principal_id_by_sid]': The element Signature for '[internal].[get_principal_id_by_sid]' cannot be deployed. This element contains state that cannot be recreated in the target database. (Microsoft.SqlServer.Dac) ------------------------------ BUTTONS: OK ------------------------------

This was with the August release of SSMS 2016 which was beset with some defects so I thought I'd try SSMS 2014 but I got the same results. The best I could gather from searching about was that there was some validation occurring that you should be able to disable but I couldn't find any switches to throw in the GUI. But as I've already said Never trust the SSMS GUI you knew this. To the command line we go!

Generating a DACPAC from the command line

To generate a dacpac from the command line you need to find a version of SQLPackage.exe dir /s /b C:\sqlpackage.exe and I went ahead and made sure I used a version that matched my database instance.

I use named instances on my machines (DEV2012/DEV2014/DEV2016) so the following block shows my extraction of each SSISDB into a version named file.

C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin>.\sqlpackage /Action:Extract /SourceDatabaseName:"SSISDB" /SourceServerName:localhost\dev2012 /TargetFile:"C:\Src\SSISDB_2012.dacpac"
Connecting to database 'SSISDB' on server 'localhost\dev2012'.
Extracting schema
Extracting schema from database
Resolving references in schema model
Successfully extracted database and saved it to file 'C:\Src\SSISDB_2012.dacpac'.

C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin>.\sqlpackage /Action:Extract /SourceDatabaseName:"SSISDB" /SourceServerName:localhost\dev2014 /TargetFile:"C:\Src\SSISDB_2014.dacpac"
Connecting to database 'SSISDB' on server 'localhost\dev2014'.
Extracting schema
Extracting schema from database
Resolving references in schema model
Successfully extracted database and saved it to file 'C:\Src\SSISDB_2014.dacpac'.

C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin>.\sqlpackage /Action:Extract /SourceDatabaseName:"SSISDB" /SourceServerName:localhost\dev2016 /TargetFile:"C:\Src\SSISDB_2016.dacpac"
Connecting to database 'SSISDB' on server 'localhost\dev2016'.
Extracting schema
Extracting schema from database
Resolving references in schema model
Successfully extracted database and saved it to file 'C:\Src\SSISDB_2016.dacpac'.

DACPACs are handy as database references but getting the SSISDB extracted took more than a simple point and click.

*for certain definitions of "easy"

Thursday, September 15, 2016

Biml adoption routes

Biml adoption routes

One of the reasons I like using Biml to generate SSIS packages is that that is no "wrong" approach to doing so. Instead, the usage and adoption of Biml should be tailored to the individual, team or organization that is using it. Despite my having used Biml for four years now, I still use it in the following ways based on the use case.

How does this work again?

As a refresher, Biml is XML that describes business intelligence artifacts. Specifically, we are going to use it to generate SSIS packages. This Biml is fed through the Biml compiler via BIDS Helper, BimlExpress, Mist, BimlStudio or a command-line compilation and SSIS packages are generated. Once generated, those SSIS packages will be indistinguishable from packages generated using BIDS/SSDT. There is no requirement to have any special server software installed to run these packages. This is entirely a developer-centric tool.

Forward only

For those just getting started with Biml, this is probably the best investment for their energy. Just this past June, I was working with a client on a very brief engagement where I was using SAP B/W as a source. Despite my best efforts, I couldn't get the CustomComponent properties "just right" for the emitted SSIS package to work.

Whatever your reason, this approach is that you will use Biml to generate as much of your SSIS package as you can and then finish coding it by hand. This is how my first few projects were implemented, by the way. For my SAP B/W packages, I stubbed in a dummy source in my data flow but the rest of my package was ready to go --- my auditing, logging, row counts, even the destination was ready. All I had to do with the designer was to open the package, replace the data flow source with the SAP B/W connector and double click the destination to have the columns route properly by name matching. Visually, I think of this approach looking like

There is a clean break between the source Biml and the package to show we've modified the generated object. If we were to regenerate, we'd have to reapply the same modifications to get the package back to the current state.

Cyclical

This approach is for those who are getting their feet under them and want to get it all "right." The arrow from SSIS back to the Biml file shows the cycle of

  1. Modify Biml
  2. Generate package
  3. Test for correctness

I found this useful as I was learning the complete syntax for all the tasks and components I wanted to represent in Biml.

Metadata driven

This approach "puts it all together." From bottom to top, we take the Biml files we developed in the Cyclical phase and make them into "patterns." That doesn't have to be a complex endeavor, it could be as simple as putting a variable in for package name.

In the center, we have .NET scripts. This doesn't mean you need to be a developer and understand the intricacies of callbacks, lambda functions and asynchronous programming. If you can comprehend how to declare a variable and how to write a foreach loop, you know enough to get this done.

At the top is a metadata repository. Fancy words that mean "Excel". Or a CSV. Or a database table. Or any other place you might have recorded information that describes what you need to build. If the patterns are a cookie cutter, the .NET scripts the hand that pushes the cutter, then the metadata is your order telling you how many of each type of cookie to create.

All three of those work together to generate "flat" Biml which then takes the above route of being fed to the compiler and emitted as SSIS packages. You won't see the flat biml getting spat out, it's all going to be in computer memory but the process remains the same.

Use it

I think regardless of how you use Biml, it's worth your time to adopt it into your organization. It hastens your development speed, it drives consistency and there's no long-term commitment involved.

Thursday, September 8, 2016

What table raised the error in SSIS?

Can I find the name of the table in SSIS that threw an error on insert?

There is a rich set of tables and views available in the SSISDB that operate as a flight recorder for SSIS packages as they execute. Markus Ehrenm├╝ller (t) had a great question in Slack. In short, can you figure out what table is being used as a destination and I took a few minutes to slice through the tables to see if I could find it.

If it's going to be anywhere, it looks like you can find it in catalog.event_message_context

If someone is using an OLE DB Destination and uses "Table or view" or "Table or View - fast load" settings, the name of the table will be the event message_context table. If they are using a variable name, then it's going to be trickier.

SELECT
    EMC.*
FROM
    catalog.event_message_context AS EMC
    INNER JOIN
    catalog.event_message_context AS AM
    ON AM.event_message_id = EMC.event_message_id
        AND AM.context_source_name = EMC.context_source_name
        AND AM.context_depth = EMC.context_depth
        AND AM.package_path = EMC.package_path
WHERE
    -- Assuming OLE DB Destination
    AM.property_name = 'AccessMode'
    -- Father forgive me for this join criteria
    AND EMC.property_name =
        CASE
            AM.property_value
                -- Need to validate these values, look approximate
                WHEN 0 THEN 'OpenRowset'
                WHEN 3 THEN 'OpenRowset'
                --WHEN 4 THEN ''
                ELSE 'OpenRowsetVariable'
        END 
    AND EMC.event_message_id IN
    (
        SELECT
            DISTINCT
            EM.event_message_id
        FROM
            catalog.event_messages AS EM
        WHERE
            -- if you know the specific operation/execution id, use it here
            --EM.operation_id = @id
            1=1
            AND EM.message_type = 120
    );

Let's break that down. We filter against the catalog.event_messages table for message_type of 120 because 120 is "Error". If you know the explicit operation_id that you are interested, remove the 1=1 and patch in that filter.

We'll use that dataset to restrict the EMC aliased table to just things that were associated to the Error. The EMC set pulls back the row that will contain the table name. We need to further figure out which of the many property_values to display. This is where it gets ugly. I think what I have is working but I have a small SSISDB to reference at the moment. We need to pick either the property_name of OpenRowset or OpenRowsetVariable. That's why we join back to the event_message_context table and use the value of the AccessMode to determine what we need to filter the EMC against.

Play with it, see if this helps you. I've added it to my SSIS SSMS Template queries so feel free to mash up those data sets as you see fit. If you find a better way to do it, I'd love to hear about it.

Wednesday, August 10, 2016

Biml Hero Training, Day 1

In June of 2013, I created my first SSIS package with Biml. Three years later, I have come so far except that today was my first day of Biml Hero training. Holy cow there's a lot I have yet to learn. While I can't go into the details of the training due to the non-disclosure agreement, I wanted to take a moment and share some of the public things.

StructureEqual

The base object for all things biml, AstNode, StructureEqual method. If I understood it correctly, I could use this method to determine whether my biml representation of an object, like a table, is the same as a table that I just reverse engineered. That's pretty cool and something I'll need to play with. And remembering harder, Scott once said something about how you could use Biml as a poor man's substitute for Schema Compare. I bet this is the trick to that.

designerbimlpath

As Cathrine notes, setting this attribute will give intellisense a shove in the right direction for fragments.

Extension methods

Technically, I already picked this trick up at Cathrine's excellent session a

Topological sorting

This was an in-depth Extension method but as with any good recursive algorithm it was precious few lines of code. Why I care about it is twofold: execution dependencies and as I type that, I realize lineage tracing would also fall under this, and foreign key traversal. For the former, in my world, I find I have the best success when my SSIS packages are tightly focused on a task and I use a master/parent package to handle the coordination and scheduling of sub-package execution. One could use an extension method to discover all the packages that implement an Execute Package Task and then figure out the ordering of dependent tasks. That could save me some documentation headaches.

Foreign key traversal is something that I think would be rather clever to do in Biml. When I reverse engineer a database, I can already pull in foreign key columns. What I can't do, at least easily with the current version is to figure out what the referenced table/column is. Think about it, if I know column SellerId in FactSales is foreign keyed to column Id in DimSeller (this is exposed in sys.foreign_key_columns) and SellerName is defined as unique, I could automate the building of lookups (based on name matches). If my fact's source query looks like SELECT SaleDate, Amount, SellerName FROM stagingTable, I could see if column names matched and auto inject lookups into my fact load.

Those were my public highlights. Tomorrow's another day and I can't wait to see what we do.

Wednesday, August 3, 2016

Biml Reverse Engineer a database

Biml Reverse Engineer a database, a.k.a. Biml to the rescue

I'm at a new client and I needed an offline version of their operation data store, ODS, database schema. I don't know what I was expecting, but it wasn't 11,500 tables. :O That's a lot. First up to bat was Visual Studio Database Projects. I clicked Import and you really have no options to winnow the list of items your importing down via Import. Ten minutes later, the import timed out on spatial indexes. Which wouldn't be so bad except it's an all or nothing operation with import.

Fair enough, I'll use the Schema Comparison and only compare tables, that should make it less ugly. And I suppose it did but still, the operation timed out. Now what?

SSMS to the rescue. I right click on my database and select Generate Scripts and first off, I script everything but the tables. Which is amusing when you have 11.5k tables, checking and unchecking the table box causes it to spin for a bit. I generated a file for each object with the hope that if the operation goes belly up, I'll at least have some progress. Round 1, Winner! I had all my views, procedures, functions, data types (don't hate), all scripted out nice and neat. Round 2, I just selected tables. And failed.

Maybe I didn't want all the tables. They have the ODS broken out by schemas to identify the data source and I only wanted the CMS data for this first draft. I run back through the Generate Scripts wizard this time only selecting tables in the CMS schema. That significantly reduced the number of objects I needed to script but still, it failed. And my mouse finger was tired. There had to be a better way.

Of late, Biml seems to be that better way. In just a few lines, I created a connection to my database, reverse engineered the targeted schema and then wrote the SQL out to files (so I could then import them with a database project). How cool is that?

inc_Connections.biml

I first added a biml file to my SSIS project that contained an OLE DB Connection Manager to the database I was interested in.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="ODS" ConnectionString="Data Source=localhost\DEV2014;Initial Catalog=ODS;Provider=SQLNCLI11.0;Integrated Security=SSPI;"/>
    </Connections>
</Biml>

ExportTables.biml

Here's the "magic". There are three neat tricks in here.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#@ template tier="1" #>
<#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>
<#@ import namespace="System.IO" #>
<#
    var schema = new List<string>{"CMS"};
    var ODSCM = RootNode.OleDbConnections["ODS"];
    var ODSDB = ODSCM.GetDatabaseSchema(schema, null, ImportOptions.None);
    string fileNameTemplate = @"C:\Users\fellowsb\Documents\ODSDB\{0}_{1}.sql";
    string currentFileName = string.Empty;
    foreach (var table in ODSDB.TableNodes) 
    { 
        currentFileName = string.Format(fileNameTemplate, table.Schema.Name, table.Name);
        System.IO.File.WriteAllText(currentFileName, table.GetDropAndCreateDdl());
    } 
#>   
</Biml>

Tiering

The first neat thing is line 2. I have a directive that tells the biml compiler that this is a tier 1 file. I could have specified tier 3, tier 7, or tier 10, it really doesn't matter as long as this is greater than the value in inc_Connections.biml. Since I didn't specify a tier in that file, it's tier 0. I needed to use an explicit tier here because line 7 references an object in the RootNode (my connection manager) that won't be built until the connections file has been compiled. The take away for tiering: if you're objects in the Biml object tree, you might need to specify tiers to handle build dependencies.

GetDatabaseSchema

Cathrine Wilhelmsen (b|t) did an excellent job covering GetDatabaseSchema so I'll let you read her post and simply comment that this method allowed me to just reverse engineer the schema I was interested.

GetDropAndCreateDdl

The last bit of magic is GetDropAndCreateDdl. It's an extension method that allows me to take the in memory representation of the table and emit the TSQL required to create that object. I enumerate through my TableNodes collection and for each object, I call the GetDropAndCreateDdl method and dump that to a file.

Gist available

Wednesday, July 20, 2016

Biml Transformer Update a variable value

Biml

In preparation for Biml Hero training, I thought it would be a good idea to understand Biml Transformers. I have read that article many times but never really dug in to try and understand it, much less find a situation where it'd be something I needed. This post covers my first attempt to using one.

Use case: updating a variable value

Assume you have an SSIS variable in your Biml files that you need to update the value - the server died and you need a new server name patched in. You could do a search and replace in your biml, or apply a configuration once you emit and deploy the SSIS package but let's try the transformer.

Source biml

Let's use the following simple biml as our package. It's three sequence containers in a serial connection and a single variable ServerName with a value of SQLDEV.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="OriginalPackage_LocalMerge" ConstraintMode="Linear">
            <Variables>
                <Variable DataType="String" Name="ServerName">SQLDEV</Variable>
            </Variables>
            <Tasks>
                <Container Name="SEQ 1" />
                <Container Name="SEQ 2" />
                <Container Name="SEQ 3" />
            </Tasks>
        </Package>
    </Packages>
</Biml>

Transformer biml

Our transformer is simple. We've specified "LocalMerge" as we only want to fix the one thing. That one thing, is an SSIS Variable named "ServerName".

What is going to happen is that we will redeclare the variable, this time we will specify a value of "SQLQA" for our Value. Additionally, I'm going to add a Description to my Variable and preserve the original value. The TargetNode object has a lot of power to it as we'll see over this series of posts on Biml Transformers.

<#@ target type="Variable" mergemode="LocalMerge" #>
<#
    string variableName = "ServerName";
    if (TargetNode.Name == variableName)
    {
#>
    <Variable DataType="String" Name="ServerName">SQLQA<Annotations><Annotation AnnotationType="Description">Value was <#=  TargetNode.Value#></Annotation></Annotations></Variable>
<#
    }
#>

So what's happening?

I think it's important to understand how this stuff works, otherwise you might not get the results you expect. The Biml compiler is going to take our source biml. If I had code nuggets in there, those get expanded and once all the "normal" biml is complete, then our transformers swoop in and are executed. This allows the transformers to work with the finalized objects, metadata is set and all that, prior to rendering actual SSIS packages.

Result

It doesn't look that amazing, I admit.

<Variable DataType="String" Name="ServerName">SQLQA
    <Annotations>
        <Annotation AnnotationType="Description">Value was SQLUAT</Annotation>
    </Annotations>
</Variable>
But conceptually, wouldn't it be handy to be able to selectively modify bits of a package? Someone didn't name their Tasks or Components well? You could have a transformer fix that. Someone forgot to add your logging/auditing/framework code? You could have a transformer fix that too!

Start thinking in Biml! Most of my answers on StackOverflow have biml in them simply because it makes describing ETL so much easier.

Caveats

Transformers require a license for Mist. They don't work in BIDS Helper, BimlExpress or BimlOnlien.

Friday, July 15, 2016

Building SSIS packages using the Biml object model

Programmatically building SSIS packages via the Biml Object Model

I thought it might be fun to try and figure out how to use the Biml Api to construct SSIS packages. This post is the first in the occasional series as I explore and find neat new things.

Getting Started

The most important precursor to doing this is you will need a licensed installation of Mist. Full stop. The assemblies we're going to use have security built into them to tell whether they are licensed and you cannot use the assemblies shipped with BidsHelper or BimlExpress as they've hardwired to the specific apps.

We're going to use two classes: AstRootNode and AstPackageNode.

Ast, what is that? Abstract Syntax Tree - it's a compiler theory thing.

AstRootNode? The root node is the <Biml /> tag. It contains all the collections in your biml declaration.

AstPackageNode? This is an instance of an SSIS package.

using Varigence.Languages.Biml;
using Varigence.Languages.Biml.Task;
using Varigence.Languages.Biml.Transformation;

...

            AstRootNode arn = new AstRootNode(null);
            AstPackageNode pkg = new AstPackageNode(arn);
            arn.Packages.Add(pkg);

            pkg.Name = "HelloWorld";

Now what?
You have two choices, you can get the Biml

Console.WriteLine(arn.GetBiml());

which results in

<Biml >
    <Packages>
        <Package Name="HelloWorld" />
    </Packages>
</Biml>

Or you can get the Xml

Console.WriteLine(arn.EmitAllXml());

which looks like

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="HelloWorld" />
    </Packages>
</Biml>

In this example, they're nearly identical except the Xml emission results in the namespace existing in the Biml declaration while the GetBiml call just returns Biml. Interestingly enough, if either is fed through BimlExpress, they'll both pass validation.