Welcome to the SSRS Migration Lab for Snowflake CodeLab. The main purpose of this lab is to learn how to convert a SSRS report pointing to a SQL database to a Snowflake database using minor DDL and DML changes.
In this code lab you will learn:
For this example we created an SSRS report connected to the database Adventureworks in a local SQL Server. Download the files located on this repo.
After downloading, open
labssrs_report_sqlserver.rdl. In here you will see:
labssrs_report_sqlserverproc.rdl. In this report you'll see the following:
In order to ensure that everything works fine, go to
Datasources > Datasources properties. On the connection string you will be able to identify the database server name and catalog (target database). Make sure to change the server name and catalog to your server and database in order for everything to work properly.
Open Datasets > Right click BISales > Query > Edit as Text
Open Datasets > Right click Employee > Query > Edit as Text
The report that uses the call of a stored procedure is
Open this report and then go to Open datasets > Right click on StoreProcREsultset > Query > Edit as Text
Take in consideration that in order to call a stored procedure using SQL Server and SSRS, the command type at this point should be
StoredProcedure as per the following image:
After configuring everything and modifying the Data Source, let's validate that everything runs correctly. To do this, click Run on the lower right corner of the report.
If everything works correctly, you should see something similar to this:
We are now going to convert the report's SQL queries to Snowflake queries using SnowConvert. If you don't have a SnowConvert license, you can continue with the next step. The other steps have the converted queries.
In order to do this, we first need to compile our queries and save them in a folder with each query in a different file. To do this, open the datasets and copy the queries. To do this
Right click the Dataset > Dataset Properties > Query Designer > Edit as Text.
Do this for all datasets, except the Stored Procedure (this one is not necessary) and save them as
.sql files. In the end you should have something like this:
Run the SnowConvert tool on this folder to get the Snowflake compliant queries. Here is a side-by-side view to compare how they differ from one another.
As you can see, the changes aren't that big. The biggest difference aside from the formatting is the change of the brackets to the quotes on line 8 (13 in the converted query). This is because Snowflake doesn't support brackets.
In this query, we can see the same result as the previous query, the only modifcation, asides from formatting, is the brackets change to quotes.
For more information regarding the SnowConvert tool, please visit SnowConvert information.
For this next section, we're going to migrate the report to connect to Snowflake using an ODBC connection.
Our next step is to create an ODBC connection for Snowflake. To do this you first need to install the ODBC driver on your computer.
Please take into consideration that Report Builder only works with 32-bit ODBC connections, and Power BI only works with 64-bit connections, so make sure to install the version that best fits your need. If you want to complete both Power BI and SSRS labs, install both versions. There's no issues by having both installations.
Add...to create a new connection. In here, select the Snowflake driver, as per the following image.
Test...button. If your connection was successful, you should get a message similar to this one.
Now we'll update the datasource in our reports to now point to Snowflake. To do this, go back to Report Builder and follow the steps below. (These steps apply for both reports.)
User user or system data source name
Okand apply the changes.
-- Dataset BIEmployee SELECT HumanResources.vPowerBIEmployee.FirstName , HumanResources.vPowerBIEmployee.LastName , HumanResources.vPowerBIEmployee.JobTitle , HumanResources.vPowerBIEmployee.BirthDate , HumanResources.vPowerBIEmployee.Gender , HumanResources.vPowerBIEmployee.Department , HumanResources.vPowerBIEmployee."Group Department" FROM HumanResources.vPowerBIEmployee;
-- Dataset BISales SELECT HumanResources.vPowerBISales.SalesOrderID , HumanResources.vPowerBISales.CustomerID , HumanResources.vPowerBISales.SalesPersonID , HumanResources.vPowerBISales.TerritoryID , HumanResources.vPowerBISales.SubTotal , HumanResources.vPowerBISales.TaxAmt , HumanResources.vPowerBISales.Freight , HumanResources.vPowerBISales.TotalDue , HumanResources.vPowerBISales.OrderQty , HumanResources.vPowerBISales.UnitPrice , HumanResources.vPowerBISales."Product Name" , HumanResources.vPowerBISales."Territory Group Name" , HumanResources.vPowerBISales."Territory Name" , HumanResources.vPowerBISales."Product Sub Category Name" , HumanResources.vPowerBISales."Product Category Name" FROM HumanResources.vPowerBISales;
These steps need to be done on all the datasets before running the report. After doing the changes, run the report and everything should run correctly. The result looks like this
After modifying the first report, we will now modify the new
labssrs_report_sqlserverproc.rdl to point to Snowflake.
To introduce the modifications to this report, it's important to explain how SSRS executes and pulls the information. The datasets refresh in order from the top of the list of datasets that use the same datasource connection, up to the last dataset on the list. This means that all queries will be executed on the same connection session. For this reason, we can query temporary tables created from above datasets on the same report, nevertheless this only applies for SSRS.
In this case, we need to create a dataset at the beginning of the list to call the procedure and the original one to feed under the table name response provided by the created dataset.
Use a dataset embedded in my report.radio button.
!to test it out. Command type should be set to
In this case, we output a table name because Snowflake does not return a dataset as a result of a stored procedure execution, such as SQL Server. The procedure was migrated in order to return a table name where the result data was stored in a temporary table, transient or physical table. This name will be used for the next dataset execution.
SELECT * FROM HUMANRESOURCES.TEMP_0
!to test out the select statement.
Okto save the changes. You may get an error message stating that the table doesn't exist. If this is the case, run the stored procedure manually on Snowflake by executing
CALL HUMANRESOURCES.SP_PowerBIEmployee();and test it out again.