This blog is moving!

Hello all, a quick heads up to let you know that the blog posts have been moved to our new home at http://www.bitracks.com/blog.

I’ll keep this wordpress blog up for a few more weeks to let everyone update their bookmarks. See you on the new site!

If only moving these kinds of packages was as easy as bimlSnaps package generation…

Using Json for Table Data Movement in SQL Server

Have you ever wanted to copy lots of tables from one SQL Server database to another, but found the Import/Export/Scripting options lacking? Depending on the number of tables you need to move, this can be a time consuming, error-prone, and a generally inflexible process.

What if you could just:

  1. Wrap-up all/some/or most tables from your ‘source’ database into a single json file
  2. Insert (import) this single file into a ‘destination’ database
  3. Replace tables in the ‘destination’ database based on the imported json contents?

Enter: Json Table Transfer Solution (JTTS) for SQL Server 2016 (or later)

This ‘JTTS’ capability consists of 2 stored procedures and 1 SSIS Package; all downloadable here Note: this ‘JTTS’ solution is a small subset of our larger BimlSnap product – so you get other goodies with this link. BTW, our ‘JTTS’ feature is independent of all our ‘Biml’ centric features. 

Full Disclosure:

  • This Json based approach has not been tested on all table/data types
  • May not be practical for larger tables
  • May be the easiest way on the planet to move lots of smaller tables between SQL Server databases

From 10,000 feet…

The diagram below illustrated the process flow for ‘JTTS’ data movement between 2 SQL Servers:

Minimum Requirements

SQL Server 2016+ ‘Source’ Database Prerequisites:

  • Stored Procedure: [jtts].[Json Table Export]
  • Table: [jtts].[json_repository]
SQL Server 2016+ ‘Destination’ Database Prerequisites:
  • Stored Procedure: [jtts].[Json Table Import]
  • Table: [jtts].[json_repository]
SQL Server Data Tools (SSDT) to run the SSIS package:
  • Transfer Json Repository Row

Step by Step

(1) In SSMS: Run the stored procedure: [jtts].[Json Table Export] from the ‘source’ database (see the stored procedure for parameter definitions and examples): 

    EXEC [jtts].[Json Table Export] ‘Test’, ‘all’, ”, ‘Y’, ‘N’

(2) In SSDT: Open the project shown below, and set the Project Parameters to point to the right ‘source’, and ‘destination’ databases, as well as the required ‘label’. Once configured, execute the ‘Run All – Transfer Json Repository Row‘ package

SSIS Project
Parameter Settings

(3) In SSMS: Run the stored procedure: [jtts].[Json Table Import] from the ‘destination’ database (see the stored procedure for parameter definitions and examples):

    EXEC [jtts].[Json Table Import] ”, ‘Y’, ‘N’, ‘N’, ”, ‘N’, ‘Test’, ‘SRV1’, ‘bimlsnap_v2’

Possibilities to Consider when using this ‘JTTS’ Repository

  • Multiple ‘point-in-time’ versions of database ‘table data’ could be captured and restored as needed
  • Json flat files can be created from a single Json (varbinary) column, and then transferred via e-mail, SFTP, or similar
  • This ‘JTTS’ solution is compatible with SQL Server’s ‘FileTable’ storage feature (see the stored procedure’s usage details for examples)
  • The JTTS format stores both table data and the exported table definitions

Front-end Option

As a part of the aforementioned download, you get a front-end program for BimlSnap. Using this front-end, simply choose the ‘Import/Export’ menu item to access the same features discussed above:

SSRS Report Usage Summary

Reports are a little like ice cream flavors; their popularity comes and goes. As a result, you may have many reports in your SSRS catalog which are no longer being used, and without an easy way to identify.

On the flip side, there may be reports which are very popular on your site, but are lagging in terms of run-time performance. Outside of the common complaint “the system is slow”, these problematic reports may simply just be “flying under the radar”, with users resigned to waiting.

To help, there are of course queries that can be run, based on the 3 versions of the database view: [dbo].[ExecutionLog…]. But why not use SSRS itself to hit an ExecutionLog, and more easily surface these analytics? After all, there’s no reason IT can’t use the “BI stack” to help our ‘business’.

To make this easy, I’ve created two .RDLs (Summary and Detail):

UsageReport

A “Go to report” action will open the “Detail report”:

UsageDetail

Note that you can specify how long SSRS keeps its log history. The screen below can be found using SQL Server Management Studio (SSMS), connecting to ‘Reporting Services…’, viewing the ‘server properties’, and then clicking on ‘Logging’:

SSRS Execution Logging

If you would like a free copy of these .RDLs, just send an email to: info@bitracks.com and place “Report Usage RDLs” in the Subject line.

http://www.bitracks.com/

Creating SSIS Packages from a Stored Procedure (via BIML)

Did you ever want to perform a quick ETL task, such as moving the contents of a table between SQL servers? The options for completing this task include:

  • Using Linked Servers
  • Generating a SQL script with INSERT statements
  • Running the Import/Export Wizard
  • Using ‘copy and paste’ with the table objects in Management Studio
  • Manually building an SSIS package

Since each of these approaches have their own drawbacks, here is another option to consider: Use a stored procedure to dynamically build a SSIS package by leveraging the power of BIML. This approach is fast, easy to refresh, and the technique extensible (more on this later):

Step 1 – Execute a stored procedure providing server, database, and table names (the stored procedure code can be found at the end of this post):

EXEC [dbo].[Single Table Truncate and Load] 
@SrcServer = 'SRV1\express'
, @DstServer = 'localhost'
, @SrcDatabase = 'AdventureWorks2014'
, @SrcTable = '[dbo].[DatabaseLog]'

Step 2 – Click on the result set (link) in SSMS:

image

(The BIML file will open in a new window in SSMS)

image

Step 3 – Open Visual Studio, right click on the ‘SSIS Packages’ container, and choose ‘Add New BIML File’ (Note: BIDS Helper must be installed for this option to appear in SSIS). If you are new to BIML you may want to click here for details on this powerful feature in BIDS Helper.

image

Step 4 – Copy and paste the above BIML (XML) script into the new BIML file created in Visual Studio:

image

Step 5 – Save the BIML file, right click on it, and choose ‘Generate SSIS Packages’:

image

A ready-to-run package will then be created under the ‘SSIS Packages’ folder in Visual Studio, with the BIML defined Control Flow, Data Flow, and Connection Managers:

image

Now someone might ask, of all the initial options presented at the start of this post, why do you like the BIML approach best? So here you go…

  1. It’s very fast (perhaps 30 seconds start to finish)
  2. If the columns or data types in in the target table changes, you only need to re-run from ‘Step 5’ above, which will fully rebuild the SSIS package
  3. The logic is driven by a stored procedure, and powered by BIML. This provides reusability, as well as extensibility by just adding BIML (SSIS) tags.
    Tip: If you want some help authoring ETL packages with BIML tags, download a free trial of Mist from Varigence, use the SSIS package import feature, and review the resulting BIML mark-up.

To run the above walkthrough, you’ll need SQL Server Management Studio, BIDS or SSDT (with BIDS Helper installed), and the stored procedure which follows:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

-- ================================================================================================
-- Author:        Jim Miller (BITracks Consulting, LLC)
-- Create date: 02 Jan 2015
-- Modify date: 
-- Description:    Builds a single table SSIS Package (in BIML) for truncate and load
--                - Automated column mappings occur only for columns with the same names
--                - The destination Database name defaults to the same as the source Database name
--                - The destination Table name defaults to the same as the source Table name
--                - The resulting SSIS Package assumes all supplied Database/Table names exist
--
-- Sample Execute Command: 
/*    
 EXEC [dbo].[Single Table Truncate and Load] 
      @SrcServer   = 'SRV1\express'
    , @DstServer   = 'localhost'
    , @SrcDatabase = 'AdventureWorks2014'
    , @SrcTable    = '[dbo].[DatabaseLog]'
*/
-- ================================================================================================

ALTER PROCEDURE [dbo].[Single Table Truncate and Load]
(
      -- required parameters
        @SrcServer   AS NVARCHAR(256)
      , @DstServer   AS NVARCHAR(256)
      , @SrcDatabase AS NVARCHAR(256)
      , @SrcTable    AS NVARCHAR(512)

      -- optional parameters
      , @DstDatabase    AS NVARCHAR(256) = ''
      , @DstTable       AS NVARCHAR(512) = ''
      , @PackageName    AS NVARCHAR(256) = ''
      , @SrcProvider    AS NVARCHAR(32) = 'SQLOLEDB.1'
      , @DstProvider    AS NVARCHAR(32) = 'SQLOLEDB.1'
      , @IdentityInsert AS NVARCHAR(16) = 'true' 
 )
AS
SET NOCOUNT ON

-- If @DstDatabase was not supplied, set it to the same value as @SrcDatabase
IF ISNULL(@DstDatabase, '') = ''
    SET @DstDatabase = @SrcDatabase

-- If @DstTable was not supplied, set it to the same value as @SrcTable
IF ISNULL(@DstTable, '') = ''
    SET @DstTable = @SrcTable

-- If @PackageName was not supplied, set it to the same value as @DstTable
IF ISNULL(@PackageName, '') = ''
    SET @PackageName = @DstTable


-- Declare internal procedure variables
DECLARE @PackageBegin AS NVARCHAR(MAX)
      , @Connections AS NVARCHAR(MAX)
       , @TruncateTableTask AS NVARCHAR(MAX)
      , @DataflowTask AS NVARCHAR(MAX)
      , @Biml AS XML


-- Package
SET @PackageBegin = '<Packages><Package Name="' + @PackageName + '" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">'


-- Connections
SET @Connections ='  
    <Connections>
        <Connection Name="src" ConnectionString="Data Source=' + @SrcServer + ';Initial Catalog=' + @SrcDatabase + ';Provider=' + @SrcProvider + ';Integrated Security=SSPI;" />
        <Connection Name="dst" ConnectionString="Data Source=' + @DstServer + ';Initial Catalog=' + @DstDatabase + ';Provider=' + @DstProvider + ';Integrated Security=SSPI" />
    </Connections>'


-- Truncate Table Task
SET @TruncateTableTask = '
    <ExecuteSQL Name="Truncate" ConnectionName="dst">
        <DirectInput>TRUNCATE TABLE ' + @DstTable + '</DirectInput>
    </ExecuteSQL>'


-- Dataflow Task
SET @DataflowTask = '
    <Dataflow Name="Copy">
        <Transformations>
            <OleDbSource Name="Source" ConnectionName="src">
                <ExternalTableInput Table="' + @SrcTable + '" />
            </OleDbSource>
            <OleDbDestination Name="Destination" ConnectionName="dst" KeepIdentity="' + @IdentityInsert + '">
                <InputPath OutputPathName="Source.Output" SsisName="Destination Input" />
                <ExternalTableOutput Table="' + @DstTable + '" />
            </OleDbDestination>
        </Transformations>
    </Dataflow>'


-- Assembly
SET @Biml =   '<Biml xmlns="http://schemas.varigence.com/biml.xsd">'
            + @Connections
            + @PackageBegin
            + '<Tasks>'
            + @TruncateTableTask
            + @DataflowTask
            + '</Tasks></Package></Packages></Biml>'


-- Return BIML package definition
SELECT @Biml

 

Roundtrips between Mist and Visual Studio

If your primary SSIS development platform continues to be Visual Studio (BIDS or SSDT), but you would like to leverage Biml, BimlScript, Mist (from Varigence), and code Transformers, then it’s helpful to understand how to make roundtrips between Visual Studio and Mist.

To illustrate, let’s assume you have the following package in SSDT (or BIDS), which in the screenshot below, simply extracts configuration and metadata from existing SQL Servers and databases:

Start by opening Mist 3.4, and using the helpful ‘Import’ feature. With this capability, you can load DTSX packages into Mist and have it auto generalte your Biml files. In this example, we’ll use a single package called: ‘DatabaseMetadata.dtsx loaded from Visual Studio’s project folder:

Once the package is imported you will have 3 ‘Biml’ files. One file for each of the connections, and one for the actual package:

You can double click any of the Biml files to view the contents. Note that 2 tabs automatically open in Mist. The first is a ‘form’ or ‘graphical’ representation of the object, while the other has the actual Biml code. When viewing the Biml code, notice how compact and readable it is when compared to a DTSX file:

As an interesting point of comparison, all three of the generated Biml files total only 161 lines of code. The original DTSX file in Visual Studio actually had 2109 lines of code as shown in part below:

Now that you are working in Mist, you can directly edit the Biml files, make changes using script based Transformers (see more at: http://bimlscript.com/Walkthrough/Details/68 ), emit (export) to other versions of BIDS or SSDT, and even work with external XML based tools.

In the event you have multiple SSIS packages, Mist Transformers are especially helpful. This is because you can make the same change to 2, 10, or hundreds of SSIS package using a single script. For example, let’s say you would like to add 2 variables to all of the packages within your Mist project. The following Transformer will do just that:

Once you are ready to move back to Visual Studio, you can right click the package file, and choose ‘Build’:

This step will create an ‘Output’ folder under the Mist project folder structure, containing a new Visual Studio project:

Alternately (instead of creating a new project), you can cut and paste individual Biml files directly into an existing Visual Studio project. To do this:

  1. Create 3 Biml files in your BIDS or SSDT project. This can be done by right clicking the SSIS Packages container, and clicking Add New Biml File:

    Note that if you do not see the “Add New Biml File” option, you’ll need to install ‘Bids Helper’ (available in CodePlex: http://bidshelper.codeplex.com/ ).

  2. For ease of use, rename the 3 Biml files to reflect the logical names provided in Mist (note: they do not need to be exact as it’s just a mnemonic):
  3. Copy and paste the contents of these 3 Biml files from Mist to the corresponding Visual Studio (Biml) files. At this point you can still work directly with the Biml files:

Now you are ready to re-generate the SSIS package. To do this:

  1. Optionally rename the package inside the Biml file (otherwise, in this case, you would overwrite the original package in Visual Studio)
  2. Hold down [Shift] and click on the 3 Biml files, and then right click the selection and choose ‘Generate SSIS Packages’:

If there is a package name conflict, Bids Helper will prompt you to verify that it’s ok to ‘overwrite’:

Note that the re-created package has been automatically re-drawn as it was re-created programmatically (i.e. no longer ‘hand-crafted’)

You are back where you started, but now with the potential to leverage Biml, BimlScript, Mist, and associated toolsets.

In conclusion, being able to ’roundtrip’ into the world of Biml and Mist provides many options for creating and maintaining SSIS packages. The more packages you need to manage, the greater the advantage when using the Biml related tools. Even if Biml is only a future consideration, the above exercise ensures that your SSIS packages are Biml compliant, and ready for the Biml ecosystem when you find it advantageous.