Tuesday, 28 March 2017

Case
I am trying to export my SSRS report to Excel, but I'm getting an error. How Can I solve it?

Excel Rendering Extension: Unknown image format image/x-png

Solution
You are using one or more PNG pictures in your reports and SSRS doesn't know how to render these because they have an unknown mime format: image/x-png 1) Solution Explorer
Go to your SSRS project in Visual Studio and search for PNG images in your Solution Explorer.

Solution Explorer

2) Properties
Now go to the properties of this image file (F4) and locate the property MIME Type.

image/x-png

3) Change MIME Type
Change the MIME Type from image/x-png to image/png and repeat this for all PNG images.

image/png

4) Deploy and test
Now deploy your change project and reopen the report to test the excel export

The selected tables from the Adventure Works database are present in the destination database and all required schema’s and specific datatypes, if applicable (we will be using a few tables from the Person schema that do not use custom datatypes)

2) - Create the Meta tables

During this step we will be creating the meta tables 'Layer' and 'TableList'. The first table will contain the id, name and prefix of the layers present in the Datawarehouse environment. In this blog the only entry present is the Staging area id and name. Normally this table also holds the name of for example the Datawarehouse and Datamart layer or any other layers present in a Business Intelligence environment.
The column 'LayerId' is used in the other table named 'TableList' and is used to make the distinction between the table names present in the respective layer. In this blog there will only be Staging Area tables described.
The table 'TableList' contains the following columns;

LayerId - The id of the layer the table belongs to

TableName - The name of the table

SchemaName - The name of the schema of the table

TableType - The type of the table (fe. user table)

LoadOrderNr - The order in which the tables are loaded (or created by other processes)

WhereClause - Any optional where clause that is used during the load proces (Default this column must be set to '1 = 1')

ActiveInd - Indicates if the table is active or inactive during the BIML creation proces

InsertDate - The date when the table entry was inserted in the 'TableList' table

Note: Some of the above columns are not or less applicable to the BIML script in this post, but they are used in other generic scripts used to create and load datawarehouse packages (more about this in future posts)

4) - Fill the meta tables with the required meta data

After creating the database and metadata tables, they need to be filled with the meta data that will be used by the BIML script in the next step ('BIML Load_STG_Tables_From_Microsoft.biml').

The script provided below inserts the layer information used in this blog and the table meta information of those tables for which the SSIS load proces will be created.

If you want to test the Where Clause functionality you can replace the value '1 = 1' with '1 = 1 AND ShoppingCartItemID = 2' in the column 'WhereClause' in the table 'TableList' for the tablename 'sales.ShoppingCartItem'. This will place a filter on the table.

The BIML script will use the meta table information to create one SSIS package with the name 'SSIS STG Load STG Tables SQL.dtsx'

5) - Create the BIML script

Once the previous steps have been executed it is time to create the BIML script. The BIML script starts with declaring the information needed to create the connection strings to the different database and the server(s) where they recide on. For this example all the databases are SQL Server 2016 databases. It would also be possible to store that information in a meta table but for this post the information is placed inside the BIML script.
The BIML script will create one package with the name 'SSIS STG Load STG Tables SQL'
and for each table in the 'TableList' table a sequence container will be created with two SSIS components. The first component is a SQL Task component that will use a T-SQL command to truncate the target table. The second component is a Data Flow Task containing a Source and Destination component which will load the data from the target to the source table. Alle the sequence components are executed parallel to each other.

6) - Generate the package using BIML Express

Once the BIML file has been created it is time to generate the SSIS package by using BIML Express in Visual Studio 2015.
The package can be simply generated by right clicking the BIML package and selecting 'Generate SSIS Packages'.

Using BIML Expres to generate the package

The package has been generated by BIML

Using BIML Expres to generate the package

Summary

In this post we create the following components;

Repository database and one schema

Staging database and one schema

Two meta tables to be used by the BIML script from this post (and possible future posts)