THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is a Data Philosopher at Enterprise Data & Analytics, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer and BimlHero; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server Integration Services Design Patterns, and author of Managing Geeks - A Journey of Leading by Doing, and the Stairway to Integration Services.

  • Managing the SSIS Catalog

    The SSIS Catalog is surfaced within the SSISDB database. In this post I describe some tips for managing your SSIS Catalog database, named SSISDB.

    Create the SSIS Catalog

    To create the SSIS Catalog, connect to an instance of SQL Server using SQL Server Management Studio (SSMS). Open the Object Explorer and right-click the “Integration Services Catalogs” node:

    CreateCatalog_0

    The Create Catalog dialog displays:

    SSISCatalogPristine

    The SSIS Catalog requires SQLCLR. Check the “Enable CLR Integration” checkbox to proceed:

    CreateCatalogEnableSQLCLR

    You can optionally select the “Enable automatic execution of the Integration Services stored procedure at SQL Server startup” option by checking its checkbox (I do):

    CreateCatalogEnableJob

    The SSIS Catalog requires a password. If you restore the SSISDB database (which wholly contains the SSIS Catalog), you will most likely need this password. So store it somewhere very safe.

    CreateCatalogAddPassword

    Click the OK button to create the SSIS Catalog.

    Note: I’ve created a short (< 2:00) video that walks through this process. You can view it here.

    Back It Up

    As with all SQL Server database, please back up SSISDB. What follows is a (very) basic guide describing one simple method to backup your SSISDB database. Please, please, please learn more about SQL Server backup and restore options and their implications before backing up an SSISDB database in your enterprise. Feel free to use the steps I describe on your laptop or a virtual machine. And please remember…

    Backups are useless. Restores are priceless. Conduct practice Disaster Recovery exercises in which you restore databases and then test functionality. You’ll be glad you did. Here is a link containing Microsoft’s advice on restoring the SSISDB database in SQL Server 2016.

    In SQL Server Management Studio (SSMS), right-click the SSISDB database, hover over Tasks, and click Back Up:

    SSISCatalog_BackItUp

    When the “Back Up Database  - SSISDB” dialog displays, set appropriate backup options. (In this screenshot, I am setting no backup options. You can break stuff here. Unintentionally. Easily. In Production. Again, read more about backing up databases – especially about backing up SSISDB).

    I’m going to walk you through a very simple example of an SSISDB backup on one of my demonstration virtual machines:

    SSISDBBackup1

    I remove the default Destination by clicking the Remove button.

    I click the Destination Add button and select a location and filename for my SSISDB backup:

    SSISDBBackupLocation

    After clicking OK, my very simple (please read waaaaay more than this post before attempting this at work or home!!) back up is configured:

    SSISDBBackupConfigured

    When I click OK, my backup completes successfully:

    SSISDBBackupComplete

    Deleting the SSIS Catalog

    If you want to delete the SSIS Catalog, drop the SSISDB database (Wait! Back it up first!):

    SSISCatalog_DropTheBase

    The Delete Object dialog displays. Click the OK button to attempt to drop the SSISDB database (and, thereby, the SSIS Catalog):

    DropSSISDB

    Starting Over with a Fresh SSIS Catalog Installation

    Perhaps you’re trying to build a presentation or demonstration about creating an SSIS Catalog. Maybe you just want a fresh start. Whatever the reason, you may find it annoying that once you’ve created and deleted an SSIS Catalog you are stuck with some of the settings:

    SSISCatalogStuckWithSettings1

    Clearing the SQLCLR Option

    Clearing the SQLCLR option (“Enable CLR Integration”) is accomplished by executing the following Transact-SQL (T-SQL) script:

    sp_configure 'clr enabled', 0; 
    GO 
    RECONFIGURE;

    These statements, when executed in SSMS, appear as shown here:

    DisableSQLCLRExec

    This solves some of the problem, but not all. The Create Catalog dialog still has that “Enable automatic execution of the Integration Services stored procedure at SQL Server startup” checkbox checked. Worse, now the checkbox is disabled!

    CreateSSISCatalogReset_Almost

    Fear not. This setting can be reset using a T-SQL script:

    EXEC sp_procoption
    @ProcName = 'sp_ssis_startup',
    @OptionName = 'startup',
    @OptionValue = 0;

    When executed, the resulting messages appear as shown:

    ResetCatalogJobSetting

    After you reset those settings you may demonstrate creating an SSIS Catalog in a pristine instance, as shown here:

    SSISCatalogPristine

    If you want, you may also delete the “SQL Server Maintenance Job” which is created when the SSIS Catalog is created:

    SSISDBJob

    This post provides some basic guidance on how to manage the SSIS Catalog in an instance of SQL Server. Please read more at Backup, Restore, and Move the SSIS Catalog MSDN article.

    :{>

    Related Training:
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago

    Learn More:

    A New Version of SSDT is Available
    Announcing SSIS Catalog Compare v1.0
    Stop an SSIS Package
    Stairway to Integration Services
    Stairway to Biml
    SQL Server Central

    Need help implementing an SSIS solution?
    Contact Enterprise Data & Analytics today!

  • 2017 SQL Skills SSIS Immersion Events

    I’m home after a day of travel that included waking up at quarter-til-early to catch a cab to SeaTac with Tim Mitchell [blog | @Tim_Mitchell], bumpy flights, and a two-hour drive back to Farmville. We had a fantastic time! Our students asked lots of interesting questions and Tim and I were, I believe, able to answer them all (with a little help – please continue reading…).

    The next SQL Skills SSIS Immersion Events are scheduled for late April / early May 2017 in Chicago. There are two: IESSIS1 and IESSIS2. As you can glean by perusing the links, IESSIS1 is designed to give those with no experience (or less experience) a firm foundation in the science and art of data integration using SSIS. IESSIS2 is geared for the more experienced data integration developer and architect.

    One cool advantage of delivering Immersion Events with SQL Skills: If you have a tangential question that involves advanced SQL Server knowledge, you can pop next door and find someone who knows these answer! That happened this past week. As Tim presented about New Features in SQL Server 2016, he mentioned the SSIS Catalog database (SSISDB) now plays nice with AlwaysOn. That raised a good question: What happens to executing SSIS packages during a failover? I wasn’t sure. Tim wasn’t sure. Jonathan Kehayias [@SQLPoolBoy] was delivering training right next door, so we asked him. He shared his thoughts and offered to run a test for our class. We did that and learned what happens, but we also learned some very interesting tidbits about configuring AlwaysOn for the SSISDB database. In Jonathan’s opinion, configuring AlwaysOn for SSISDB was “difficult,” perhaps even “tricky.”

    These are the types of things we share and learn (and I always learn something when delivering training) during IESSIS training events. We hope to see you at the next events in Chicago!

    :{>

    Related Training:
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago

    Learn More:

    A New Version of SSDT is Available
    Announcing SSIS Catalog Compare v1.0
    Stop an SSIS Package
    Stairway to Integration Services
    Stairway to Biml
    SQL Server Central

    Need help implementing an SSIS solution?
    Contact Enterprise Data & Analytics today!

  • A New Version of SSDT is Available

    Microsoft released an update to SQL Server Data Tools (SSDT) 2016 on 20 Sep 2016 – the tool used to design databases, SSIS, SSRS, and SSAS solutions.

    I can hear you thinking, “How do I get the update, Andy?” You can get version 14.0.60918.0 here.

    If you have the installer for SQL Server 2016 handy, you can get to the download link very easily. Start the installer and click the Installation page:

    InstallSQLServer2016

    There’s a link labeled “Install SQL Server Data Tools.” Click that link to go to the Download SQL Server Data Tools (SSDT) page. To get the Visual Studio 2015 version of SSDT 2016, click that top button / link:

    DownloadSSDT2016_1

    The button / link will take you to the Visual Studio-specific download page for SSDT:

    DownloadSSDT2016_2

    There’s a link under #3 (at the time of this writing) for downloading the web installer:

    DownloadSSDT2016_2ajpg

    Your mileage may vary, but I had trouble getting the web installer to work for the 20 Sep 2016 update. So I scrolled down a little and grabbed the ISO link under #4 (at the time of this writing):

    DownloadSSDT2016_3

    You can mount the ISO file using Windows Explorer:

    MountThe Download

    Once mounted the ISO file acts like an optical drive:

    MountedISO

    From here, you can execute the SSDTSETUP.EXE file and install the update.

    :{>

    Related Training:
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago

    Learn More:

    Biml Academy
    The Basics of Biml – the Execute SQL Task
    The Basics of Biml – Populating the Biml Relational Hierarchy
    Stairway to Biml
    Stairway to Integration Services
    Varigence.com
    BimlScript.com
    SQL Server Central

    Need help or training implementing a Biml solution?
    Contact Enterprise Data & Analytics today!

  • I Was Wrong (About the SSIS Catalog)

    I apologize for misleading people who read my writings and have heard me speak on the topic of the SSIS Catalog. Here’s what I recently realized: The execution_id is different from the operation_id in the SSISDB database.

    In the past, I complained about the execution_id and operation_id being the same value and meaning different things. To be sure, if you are designing a database, you need to name fields with identical definitions with the same name. But, as I said, execution_id and operation_id are different values.

    SSISCatalogJoins

    Above is a portion of the SSISDB.catalog.executions view. This view, in part, joins data in the internal.executions table to data in the internal.operations table. The JOIN operation is accomplished by mapping the values in the internal.executions.execution_id column to the internal.operations.operation_id column.

    I used to fuss about this join, stating something like “This is bad. If the data is the same, the field should be named the same.” Again, that is a correct statement. In this case, though, the data is not the same.

    Operations in the SSIS Catalog include way more than executions. Validations are operations, for example. Updating Catalog-wide settings are operations. Executions are operations, too; just not the only operations.

    As I regularly tell folks, I’m still learning.

  • Value

    Numbers don't lie!

    One of our mottos at Enterprise Data & Analytics is, “Deliver Value.” I can hear you thinking, “That’s nice, Andy. What does that mean?” I’m glad you asked.

    !Value

    Let’s start with what value is not, shall we?

    Value is not the least expensive. As a consultant, I often “bid” for consulting work, sometimes referred to as “gigs.” How does bidding work? Someone calls or emails. I usually set up a meeting to discuss the problem they are trying to solve. I listen – a lot. I inform the potential customer whether I can help or not. Usually, if I cannot help, I know someone who can; and often I can subcontract someone who can help. Next we talk about hourly rate.

    Let’s talk about hourly rate. And experience.

    I once had a conversation with a customer that went something like this:

    Customer: “We would like for you to help us develop a business intelligence and analytics solution.”

    Me: “Cool. I can help.”

    Customer: “So what is your hourly rate?”

    Me: “$___ per hour.”

    Customer: “Wow. We can hire several people to help with our business intelligence and analytics for that rate!”

    In this instance (and several similar instances), the client opted to hire several people at a lower rate. In this instance (and several similar instances), the client called back later and asked if I had some availability to help them. Why? Experience. The consultants they hired at the lower rate did not deliver. I’ve done this several times before. I know what to expect, and I recognize the unexpected.

    The value of that last phrase is not to be underestimated.

    Having experience means I immediately recognize something new and different. I raise the flag. Having experience also means I know what to expect. Experience often translates into saved time. My hourly rate may be double the competition, but I know how to deliver major portions of the project in 1/4th the time. (Some portions I know how to deliver in 1/100th of the time.) If I’m able, at (hypothetically) $300/hour, to deliver some aspect of the project in 25 hours; and the lower-rate consultants, at (hypothetically) $150/hour, are able to deliver the same functionality in 100 hours; which of us is the better value?

    Let’s do the math.

    Me:
    $300/hour * 25 hours = $7,500

    The “less expensive alternative”:
    $150/hour * 100 hours = $15,000

    Which of us is the better value, me or the less expensive alternative? To quote Foghorn Leghorn, “numbers don’t lie.” Foghorn is correct. I am the better value, even though I charge more per hour.

    But Wait, There’s More

    Lots more. You see, when a project is under development everyone is laser-focused on the costs of development. Why? Well, these costs are right there in front of everyone. The math is easy, it’s the number of hours invoiced multiplied by the number of hours. But is the cost of development the highest cost of a project?

    The answer is no. Most business intelligence, data warehouse, or analytics projects are used in the enterprise for five to ten years. In my experience, the costs of maintaining and supporting are often more than the costs of developing the solution in the first place.

    If you read that last paragraph and thought, “Of course you’re going to write that, Andy! You want us to hire you instead of your competition who charges a lower hourly rate!” If you thought that, don’t hire me. Hire someone you trust. Your data, in 2016, needs a consultant you trust. Your customers need a consultant you trust. The people behind the personally-identifying information (PII) in your databases need a consultant you trust. In my opinion (again, subjective), integrity should be your number one consideration when selecting a consultant for a data project. Please hire someone you trust. If that’s not me, I will understand.

    The combined costs of developing, supporting, maintaining, and extending a solution is called the total cost of ownership, or TCO.

    The costs of supporting, maintaining, and extending the solution are spread across the years the solution is in production. The individual costs are small – especially when compared to the hourly rate of a consultant – but they are manifold. Over time these costs can, and most often do (in my experience), overtake the costs of development. Designing for supportability, maintainability, and extensibility can save thousands of dollars (sometimes orders of magnitude more) in TCO.

    It’s not just costs of supporting, maintaining, and extending the solution, though. Think about the opportunity cost – the cost of opportunities lost because your team is spending extra time fiddling with this solution – when they could be thinking up killer applications and solutions that will make you a go-zillionaire!

    I design for supportability, maintainability, and extensibility. To design for TCO, one needs experience supporting, maintaining, and extending data-related projects. Not everyone has that experience. Some brilliant consultants have never led or managed a team or project from inside a large enterprise. I led a team of 40 ETL developers when I worked at Unisys. I can tell you, experience managing a team of developers is very different from being an independent consultant.

    Conclusion

    Because the money for development projects usually comes out of a different accounting bucket (the capital budget) and support, maintenance, and extending projects comes out of the operations budget, it’s understandable that TCO is often overlooked and development costs are often over-scrutinized.

    Please consider experience and the total cost of ownership when selecting a consultant. You’ll be glad you did.

    :{>

    Related Training:
    IESSIS1: Immersion Event on Learning SQL Server Integration Services - April 2017, Chicago

    Learn More:

    Biml Academy
    The Basics of Biml – the Execute SQL Task
    The Basics of Biml – Populating the Biml Relational Hierarchy
    Stairway to Biml
    Stairway to Integration Services
    Varigence.com
    BimlScript.com
    SQL Server Central

    Need help or training implementing a Biml solution?
    Contact Enterprise Data & Analytics today!

  • I’m Presenting at SQL Saturday Charlotte (#560) Next Saturday!

    I’m honored and all giggly with anticipation about presenting at SQL Saturday 560 in Charlotte next Saturday, 17 Sep 2016! I’m presenting Biml and SSIS Frameworks, and I hope to see many of you there.

    For the SSIS Frameworks part of my presentation, I’m using SSIS Framework Community Edition, a free and open source SSIS Framework from Enterprise Data & Analytics. SSIS Framework Community Edition (non-invasively) integrates into the SSIS Catalog and allows you to execute a collection of SSIS Packages (even if those packages reside in different folders and projects in the Catalog) by executing a single stored procedure. How neat is that? You can find links to the source code and documentation at the SSIS Framework Community Edition page.

    Here’s hoping I see you in Charlotte next weekend!

    :{>

    Related Training:
    IESSIS2: Immersion Event on Advanced SQL Server Integration Services

    Learn More:
    Biml Academy
    SSIS Framework Community Edition (FREE! and Open Source)
    The Basics of Biml – Populating the Biml Relational Hierarchy
    Stairway to Biml
    Stairway to Integration Services
    Varigence.com
    BimlScript.com
    SQL Server Central

    Need help or training implementing a Biml solution?
    Contact Enterprise Data & Analytics today!

  • The Basics of Biml – the Execute SQL Task

    A couple years ago, I wrote a series of guest posts about the Basics of SSIS for my friend Pinal Dave’s [Blog | @pinaldave] popular blog, SQL Authority. In that series, I pointed out that I use only a handful of SSIS Control Flow tasks when designing SSIS packages. You can find the series here:

    In this post, I’m going to demonstrate and discuss building the Execute SQL Task using Biml.

    SQL SERVER - The Basics of the SSIS Execute SQL Task - Notes from the Field #044 ssise1

    “So how does one build an Execute SQL Task using Biml, Andy?” I’m glad you asked. First you have to build an SSIS package in Biml. But before you build an SSIS package in Biml, you need to populate the Biml Relational Hierarchy. To play along at home grab the 14-day free trial of Mist here and download the World Wide Importers sample database here. Then go learn how to populate the Biml Relational Hierarchy by reading this post: The Basics of Biml – Populating the Biml Relational Hierarchy. You’ll be glad you did.

    The Prerequisites

    I’m going to continue building out the demo Mist project named BimlBasics – the one I started in The Basics of Biml – Populating the Biml Relational Hierarchy. (I told you you’d be glad you read that post!)

    Create an SSIS Package

    You can create an SSIS package using the Mist GUI. But for this exercise, let’s build one from the ground up using Biml. In Logical View, right-click Library—>Utilities and then click Add BimlScript:

    BimlBasics_ExecSql_2

    I named my BimlScript file “TruncateAndLoad_People.biml”:

    BimlBasics_ExecSql_3

    If I double-click the file in Logical View, the BimlScript Input Editor displays my file contents:

    BimlBasics_ExecSql_4

    If you position your cursor at the end of line 1 and press the Enter key a new, properly-indented line begins. Type “<” to begin adding Biml to your file. Note the IntelliSense provides a list of options available at this level of the Biml object model:

    BimlBasics_ExecSql_3

    Double-click Packages to begin creating a Packages collection:

    BimlBasics_ExecSql_4

    Type the “close tag” symbol – “>” – and Biml automagically generates the close tag: </Packages>:

    BimlBasics_ExecSql_5

    Position your cursor between the opening and closing Packages tags and press the Enter key. A new properly-indented line appears:

    BimlBasics_ExecSql_6

    Type the “open tag” symbol (“<”) and IntelliSense provides a list of valid Biml objects. Type of click Package to proceed:

    BimlBasics_ExecSql_7

    Type a space at the end of the word “Package” followed by the letter “N.” Note the Name attribute is highlighted by IntelliSense:

    BimlBasics_ExecSql_8

    Enter “Package1” as the Name attribute value:

    BimlBasics_ExecSql_9

    Close the Package tag:

    BimlBasics_ExecSql_10

    Press Enter to start a new, properly-indented line. Use IntelliSense (or type) to add a “<Tasks>” tag:

    BimlBasics_ExecSql_11

    Inside the Tasks tag, add an “<ExecuteSQL>” tag:

    BimlBasics_ExecSql_12

    Add the ConnectionName attribute:

    BimlBasics_ExecSql_13

    Note IntelliSense supplies valid values – WorldWideImporters, in this case – to the attribute:

    BimlBasics_ExecSql_14

    Supply a Name attribute for the Execute SQL Task and close the tag:

    BimlBasics_ExecSql_15

    Inside the ExecuteSQL tag, next a DirectInput tag:

    BimlBasics_ExecSql_16

    In Logical View, right-click the Application.People table, hover over Copy SQL Script, and then click Select SQL:

    BimlBasics_ExecSql_17

    Position your cursor between the DirectInput tags and paste the clipboard contents:

    BimlBasics_ExecSql_18

    Look at all that well-formatted T-SQL! How neat is that?

    BimlBasics_ExecSql_19

    We’ve been ignoring this message at the top of the BimlScript Input Editor for a while now:

    BimlBasics_ExecSql_20

    Click it.

    Observe the Preview Expanded BimlScript window:

    BimlBasics_ExecSql_21

    That right there is your Package1.dtsx Biml.

    Next Steps

    You do not yet have an SSIS package. You have the Biml template of a package. I can hear you thinking, “So how does this become an SSIS package, Andy?” I’m glad you asked!

    We now need to execute the BimlScript and then build the output.

    Execute the BimlScript

    First, we execute the BimlScript file. In Logical View, right-click the TruncateAndLoad_People.biml file (found in Library—>Utilities) and click Execute BimlScript:

    BimlBasics_ExecSql_22

    The Biml template of an SSIS package has now been executed. It generated the Biml version of an SSIS package. In the image below, I positioned the Logical View and Project View side-by-side for comparison:

    BimlBasics_ExecSql_23

    We now have a Biml representation of the SSIS package named Package1.dtsx.

    Build the Package

    To generate the actual SSIS package, right-click Package1 (found in Logical View—>Integration Services—>Packages) and then click Build:

     

    BimlBasics_ExecSql_24

    The Output window displays the results of the Build operation:

    BimlBasics_ExecSql_25

    An SSIS project is generated in the output folder of the BimlBasics Mist project folder:

    BimlBasics_ExecSql_26

    The package was generated just as we specified:

    BimlBasics_ExecSQL_27

    And it runs!

    BimlBasics_ExecSql_28

    Conclusion

    In this post, I discussed and demonstrated how to configure an SSIS package that contains an Execute SQL Task, one of the handful of tasks I use when designing SSIS packages.

    :{>

    Related Training:
    IESSIS2: Immersion Event on Advanced SQL Server Integration Services

    Learn More:

    Biml Academy
    The Basics of Biml – Populating the Biml Relational Hierarchy
    Stairway to Biml
    Stairway to Integration Services
    Varigence.com
    BimlScript.com
    SQL Server Central

    Need help or training implementing a Biml solution?
    Contact Enterprise Data & Analytics today!

  • The Basics of Biml – Populating the Biml Relational Hierarchy

    In this post, I’m going to demonstrate how to build the objects Business Intelligence Markup Language (Biml) requires before creating anything – the Biml Relational Hierarchy. The Biml Relational Hierarchy provides the foundation for all relational interaction between packages, cubes, dimensions, facts, and T-SQL.

    It’s important to note that Biml is useful for generating SSIS and SSAS, but Biml can generate any text – which includes .Net code (I’ve used Biml to generate C#) – that is based on a database schema.

    To play along at home grab the 14-day free trial of Mist here and download the World Wide Importers sample database here.

    The Biml Relational Hierarchy

    BimlRelationalHierarchyMist

    Once you create a new project in Mist (mine is called “BimlBasics”), the Logical View displays an empty Relational hierarchy. Note that the relational hierarchy is flat. There are good reasons for representing the hierarchy thus, but they are beyond the scope of this post (but trust me, it’s a good and smart thing). One thing I will share in this post: The objects beneath the Relational node reside in the “RootNode” object in Biml. Reeves Smith [@SQLReeves] explains RootNode in this (excellent) article: Stairway to Biml Level 5 - Biml Language Elements at SQL Server Central.

    The “logical flatness” of these objects in RootNode make them easier to access and manipulate programmatically. One way to illustrate the logical flatness of the RootNode’s relational hierarchy is shown here:

    BimlRelationalHierarchyFlat

    But, these objects – connections, databases, schemas, and tables (and columns) – are really part of a related hierarchy. Another way to think of the RootNode’s relational hierarchy is shown here:

    BimlRelationalHierarchyTiered

    “But Andy, there’s no “Columns” node in the flattened presentation.” True. The columns are there, though; they’re inside the Tables as we will see in a bit.

    Populating the Relational Hierarchy

    There are two ways to populate the relational hierarchy:

    • Graphically
    • Programmatically

    In this post, I am going to cover graphically populating the relational hierarchy. Scott Currie [Varigence] provided a remarkable example of programmatically populating the relational hierarchy in his BimlScript.com article Biml Basics for Relational DBs.

    Populating the relational hierarchy graphically is simple; just follow the steps as they are listed from top to bottom in the Logical View’s Relational node in the Mist IDE. Begin with Connections.

    Connection

    Right-click the Connections node, hover over Add Connection, and then click OLE DB:

    BimlRelational_Connection_1

    The “OleDbConnection1” window displays. You can enter a connection string value in the Connection String textbox or build a connection string. To build a connection string, first select a Provider from the dropdown:

     

    BimlRelational_Connection_2

    Enter a database instance in the Server textbox:

    BimlRelational_Connection_4

    Select an authentication method and enter credentials (if needed):

    BimlRelational_Connection_5

    The Database Name dropdown is empty until you click the Update link:

    BimlRelational_Connection_6

    After clicking the update link, the Database Name dropdown is populated with a list of databases:

    BimlRelational_Connection_7

    You can test the connection by clicking the Test button:

    BimlRelational_Connection_8

    If the test is successful, you will see the following dialog:

    BimlRelational_Connection_9

    You can rename the Connection in Logical View by right-clicking the connection and clicking Rename:

    BimlRelational_Connection_10

    I renamed my connection “WorldWideImporters”:

    BimlRelational_Connection_11

    Logical View now reflects a configured Database. Let’s configure a Database.

    Database

    To add a database to the relational hierarchy, right-click Databases in Logical View, and then click Add Database:

    BimlRelational_Database_1

    When the Database Properties window displays, Select the WorldWideImporters connection from the Connection property dropdown:

    BimlRelational_Database_2

    Edit the Name property of the Database (I named mine WorldWideImporters). When you navigate away from the Name property textbox you are prompted to confirm you want to also rename the Database object’s Biml file:

    BimlRelational_Database_3

    Click Yes.

    Logical View now reflects a configure Connection and Database:

    BimlRelational_Database_4

    Let’s next configure a schema.

    Schema

    To add a schema to the relational hierarchy, right-click Schemas in Logical View, and then click Add Schema:

    BimlRelational_Schema_1

    When the Schema Properties page displays, configure the Database property from the dropdown:

    BimlRelational_Schema_2

    Provide the name of the Schema in the Name property textbox (I chose the Application schema). When you navigate away from the Name property textbox, you are prompted to confirm you want to also rename the Schema object’s Biml file:

    BimlRelational_Schema_3

    Click Yes.

    Logical View now reflects a connection, database, and schema:

    BimlRelational_Schema_4

    Next, let’s import a table:

    Table

    There a couple ways to add a table to the relational hierarchy. If the table exists in the relational database, you can save time by importing the table. Right-click Tables in Logical View and then click Import Tables:

    BimlRelational_Table_1

    When the Import Tables dialog displays, select the connection (WorldWideImporters) from the Source Connection dropdown:

    BimlRelational_Table_3

    Note the Connection Established indicator changes from red to green when you successfully connect.

    Select the Project Database from the dropdown (WorldWideImporters):

    BimlRelational_Table_4

    the Importable Assets treeview displays the available schemas, tables, and views in the WorldWideImporters database. I’m going to import a single table named “People”:

    BimlRelational_Table_5

    Note that I could have skipped creating the Application schema in the Biml relation hierarchy as it is imported as part of the table import process. In this case, I choose to overwrite the schema object I created earlier with the imported version:

    BimlRelational_Table_6

    The Logical View now displays a completed Biml relational hierarchy that includes a connection, database, schema, and table:

    BimlRelational_Table_7

    Columns?

    I can hear you thinking, “Where are the columns, Andy?” I’m glad you asked. If you double-click the Application.People table, the graphical viewer will display the table designer, which displays many table objects including columns:

    BimlRelation_Column_1

    Conclusion

    Congratulations! You’ve just populated a Biml relational hierarchy.

    :{>

    Learn More:

    Biml Academy
    Stairway to Biml
    Stairway to Integration Services
    Varigence.com
    BimlScript.com
    SQL Server Central

    Need help or training implementing a Biml solution?
    Contact Enterprise Data & Analytics today!

  • Announcing SSIS Catalog Compare v1.0

    SSISCatalogComparev1

    We’re proud to announce the release of SSIS Catalog Compare v1.0!

    SSIS Catalog Compare compares the contents of two SSIS Catalog instances, surfacing SSIS project-related configurations metadata in treeview controls. Data Integration Lifecycle Managers, DevOps personnel, and configuration and release managers can script configurations metadata for deployment to another SSIS Catalog instance.

    CCDoc_53

    Users may also deploy SSIS Catalog artifacts from one Catalog instance to another.

    CCDoc_79

    Simplify Data Integration Lifecycle Management (DILM) in your enterprise! Download SSIS Catalog Compare v1.0 today!

    Click here to view a short (-ish) video describing the features of SSIS Catalog Compare v1.0, learn more, and download.

    :{>

  • Biml Academy 3 Will Be Held 7-11 Nov 2016

    BimlAcademyLogo

    Enterprise Data & Analytics and Varigence are honored to announce Biml Academy 3 will be held 7-11 Nov 2016!

    Register today!

    Learn more about Biml Academy and view recordings from previous sessions at Biml.Academy!

    A great big "THANK YOU" to SQL Server Central for help spreading the word!

    :{>

    Learn More:

    Stairway to Biml
    Stairway to Integration Services
    Varigence.com
    BimlScript.com
    SQL Server Central

    Need help or training implementing a Biml solution?
    Contact Enterprise Data & Analytics today!

  • Announcing the Biml Academy Website!

    BimlAcademyHeader

    Enterprise Data & Analytics and Varigence are honored to announce a new website focused on Biml Academy!

    Visit Biml Academy to register for the next Biml Academy event or to view recordings of previous Biml Academy presentations:

    BimlAcademySessions

    You can also follow Biml Academy on Twitter (@BimlAcademy)  and Facebook.

    A great big "THANK YOU" to SQL Server Central for help spreading the word!

    :{>

    Learn More:

    Stairway to Biml
    Stairway to Integration Services
    Varigence.com
    BimlScript.com
    SQL Server Central

    Need help or training implementing a Biml solution?
    Contact Enterprise Data & Analytics today!

  • Updates to SSIS Catalog Compare (Preview)

    The launch of SSIS Catalog Compare is approaching. If you purchase the beta (or preview) version before the v1.0 product launch for $95 USD, you will enjoy lifetime updates of the product and no yearly maintenance fees for support, updates, and upgrades. If you wait, you will have to pay more for the product and you will have to pay a yearly maintenance fee for product support, updates, and upgrades.

    The preview version (v1.0.6.0) surfaces more SSIS package metadata – Package Version, Package Version Comments, and Package Version GUID. The new properties are compared when one executes a Compare operation. The results of two different versions of the same SSIS project / packages are shown below:

    SSISCatalogComparePackageVersions

    “Why is this important, Andy?”

    I’m glad you asked. The purpose of SSIS Catalog Compare is to support DevOps and Data Integration Lifecycle Management (DILM) in your enterprise. A match between Package version metadata provides some level of comfort that the versions of the SSIS Packages in one SSIS Catalog are the same as the versions in another SSIS Catalog.

    Why did I write “some?” It’s possible to deploy different SSIS packages with the same Package Version metadata. It requires extra steps, but it is not impossible.

    Our goal in adding these fields is to provide due diligence with the results of comparing the contents of two SSIS Catalogs.

    :{>

    Learn More:

    SSIS Catalog Compare

  • Biml Academy 2 Webinar Recordings are Available!

    You can find the recordings for Biml Academy 2 at the links below:

    Thank you, SQL Server Central!

    Biml Academy would like to thank SQL Server Central [@SqlServerCentrl] for their help in spreading the word about Biml Academy!

    :{>

    Learn More:

    Stairway to Biml
    Stairway to Integration Services
    Varigence.com
    BimlScript.com

    Need help or training implementing a Biml solution?
    Contact Enterprise Data & Analytics today!

  • The Recording for Loading Medical Data to the Cloud is Available!

  • The Recording for Biml Academy 2–Lesson 5 is Now Available!

    The recording for Biml Academy 2 - Lesson 5 - Importing Data with Biml by Cathrine Wilhelmsen [Blog | @cathrinew] is now available!

    Cathrine did an outstanding job walking through several methods for importing database schemas using Biml. It’s worth a view (registration required).

    :{>

More Posts Next page »

This Blog

Syndication

News


My Companies



Community Awards

Friend of Red Gate

Contact Me

Archives

Privacy Statement