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.

What you'll learn

In this code lab you will learn:

• How to connect a SSRS report to SQL Server
• Migrate SQL Scripts
• How to update the SSRS report and connect to Snowflake

Prerequisites

• Report Builder Desktop
• AdventureWorks views and procedures on SQL Server and Snowflake. For instructions on how to set this environment, check this repository.

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:

• 1 Data Source
• 2 Datasets:
  • BIEmployee
  • BISales
• Some charts and a table

Afterwards, open labssrs_report_sqlserverproc.rdl. In this report you'll see the following:

• 1 Data Source
• 1 Dataset (from a stored procedure)
• A chart and a table

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.

Validating dataset configuration

Check the Sales view call on the report:

Open Datasets > Right click BISales > Query > Edit as Text

Check the Employee view call on the report:

Open Datasets > Right click Employee > Query > Edit as Text

Check StoreProcResultset Dataset on labssrs_report_sqlserverproc.rdl

The report that uses the call of a stored procedure is labssrs_report_sqlserverproc.rdl.

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:

Validate that there are no errors

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.

Snowflake 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.

1. Please visit this link and download the drivers. Follow the instructions there for more details.

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.

1. After the driver has been installed, create the new driver connection. To achieve this, click the Windows button and then search for ODBC. Make sure to choose the bits version that best fits your needs. You can identify them by the parenthesis at the end. If you installed both versions, it doesn't matter which one you chose, ODBC should automatically create it for both.

1. Click Add... to create a new connection. In here, select the Snowflake driver, as per the following image.

1. Add the connection parameters.

1. Click the 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.)

1. Open the datasource folder
2. Right click datasource properties
3. Select connection Type as ODBC
4. Build
5. Check User user or system data source name
6. Open the drop down menu and check the odbc created on previous steps
7. On Login information enter the credentials to access snowflake database.
8. click Test connection.
9. Click Ok and apply the changes.

Updating Views as Dataset

1. Open datasets Folder
2. Choose the data set > Right click
3. Select query on the left panel. Enter the datasource (Snowflake) credentials if neeeded.
4. Check command type as Text
5. Click Query Designer
6. Copy the query from the queries below, depending on the dataset that you're modifying. These scripts are the results of the SnowConvert migration.
7. Test the query by clicking the ! symbol.
8. Click Ok.

-- 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

Updating Stored Procedure as Dataset

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.

1. Right Click on Datasets folder > click on Add Dataset
2. Choose the Dataset > Right click > New dataset
3. Type the desired Name.
4. Choose Use a dataset embedded in my report. radio button.
5. On Datasource drop down list choose the Datasource pointing to snowflake on the report.
6. Open the Query Designer
7. Write down the snowflake syntax for calling a procedure and click on ! to test it out. Command type should be set to Text.

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.

1. If needed, delete the original dataset and create a new one, the important part is to leave this dataset at the bottom (after the dataset in charge of the procedure execution). If it were to be deleted, name it as same as the original one. This deletion may lead to issues, further steps have the solution if that happens.
2. Right click > Query , on the query designer add the following query. This would be a query to the temporary table created by the stored procedure on the step before.

SELECT * FROM HUMANRESOURCES.TEMP_0

1. Click on ! to test out the select statement.
2. If everything works , press Ok to 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.
3. Run the report to test out the changes. You should see the following result.

1. If you get an error message saying that a chart needs to be updated similar to this one, then you need to update the chart. To do this, right click the chart and modify the dataset name and try again.