Category Archives: SQL Server Integration Services – SSIS

Exporting Power BI Data to SQL Server

 

Exporting Power BI Data to SQL Server

In the previous blog posts I explained how to export Power BI data to Excel and CSV here and here. As promised in this post I explain how to export data from Power BI Desktop to SQL Server.

Hans Peter Pfister has already explained how to export data from Power BI Desktop to SQL Server using R scripts. Although Hans has done a brilliant job, it might be a bit hard to make it work if you don’t have any R experience and you don’t even know how to install and call R libraries. That’s so true about myself, I’m NOT an R guy, but, who knows, maybe I will be. Smile 

But, what if you don’t want to go with R? If you are more involved with BI than analytics, then using R might not really be your cup of tea. Luckily, there is another way to export your Power BI data to SQL Server which is more BI friendly. You can export Power BI data to SQL Server using SSIS (SQL Server Integration Services). So if you are familiar with SSIS, then it might be your your preferred choice.

With respect to Hans, in this post, I explain his method of exporting data from Power BI Desktop to SQL Server more in details so that anyone who is not that familiar with R can make it work. I also explain how to export data from Power BI Desktop to SQL Server using SSIS. If there is any other methods you’re aware of please let me know in the comment section below.

Exporting Data from Power BI Desktop to SQL Server with R

As stated before, Hans has already explained this method here. So I don’t explain exactly what he did, but, I use his method to export data from existing Power BI Desktop model to SQL Server and I explain it step-by-step.

Requirements

To make this method work you need to:

  • Latest version of Power BI Desktop, you can download it from here
  • Have access to an instance of SQL Server, either on your own machine or on a server in your local network to export the data to
  • Either install R for Windows, you can download it from here OR using an existing R-Server OR install SQL Server 2016 R Services
  • Install RODBC library for R, you can download the library from here

Note: I haven’t installed R Studio and nothing went wrong.

Installing RODBC Library for R and SQL Server R Services

As mentioned earlier, you can install R OR SQL Server R Services OR R-Server, but, as I haven’t tried R-Server myself I just explain how to install RODBC in R and SQL Server R Services.

You have to download the library from the link provided above, then extract the contents of the zip file which contains a “RODBC” folder. Then all you really need to do is to copy the “RODBC” to the “library” folder exists in either R or SQL Server 2016 folders in your “Program Files” folder.

Library folder in R

Library folder in SQL Server 2016

How Does It Work?

Open an existing Power BI Desktop model that you’re willing to export its data to a SQL Server table and follow the steps below: (I use “Internet Sales” model created on top of AdventureWorksDW2016CTP3. You can download my Power BI Desktop model at the end of this post.)

Continue reading Exporting Power BI Data to SQL Server

Deploying SSIS Projects to Different Environments

In this post I’m explaining how you can deploy a developed SSIS project to several different environments. It might have happened to you that there are several environments that you need to deploy the SSIS projects to. Assume that you have DEV, QA, UAT and PROD environments. Some organisations might have even more environments. Also, there are many cases that you might have several PRODs that the SSIS packages should be deployed to all of them. So the scenario is that whenever you create a new SSIS project in DEV area or you may modify the existing projects, you need to deploy each SSIS project to QA for testing purposes. So, if you have 3 new SSIS projects or you’ve just modified 3 existing projects, you’ll need to deploy each project separately. It is the same story for QA guys after finishing the test cases and after the SSIS projects pass all the test cases. They’ll need to deploy all projects to UAT. Again it is the same story with UAT and PROD. It is getting harder when you need to deploy all the projects in several different PROD environments.

Using the solution below, you can easily deploy all SSIS projects from an environment to another environment or even several different environments.

Continue reading Deploying SSIS Projects to Different Environments

Operating on different source SQL Server instances in a single SSIS package

In some cases we need to do a single task for lots of SQL Server instances. Assume that we have a web based programme. The programme’s database is distributed across the country and we have 10 different virtual (VM) servers to host the programme’s databases. The programme is working based on some configurations that are stored in a CONFIG database. The CONFIG databases are hosted by 20 different SQL Server instances to serve 20 different clients. The SQL server instances are all named SQL server instances hosted by those 10 virtual servers. We need to update the CONFIG database for all regions on a monthly basis. The database structure of all CONFIG databases is the same. In this case a simple way is to create an SSIS package for each source server to collect the data from all source databases one-by-one. This means that we will have 10 copies of the same SSIS package that each package is pointing to a server as a source server. We need 10 packages because we can retrieve the CONFIG database list by writing a T-SQL script or using an extra Foreach Loop Container. So we need a SSIS package per server.

How to install database diagram support programmatically

In the previous article we discussed about how migrating a database diagram into another database. In this article I’ll quickly show you how to install database diagram support. As mentioned in the” How to copy or migrate database diagrams into another database” article, we need to install database diagram support to be able to see the migrated database diagrams. We also explained a very easy way to install database diagram support from SSMS in the “How to store a SQL Server database diagram into a file and share it with others?” article. Now, assume that we want to migrate the database diagrams into several SQL Server instances. It seems that it might be better if we can implement the whole process programmatically. This was exactly my question when I wanted to deploy several database diagrams from a database hosted in development environment into a copy of that database hosted by test or UAT (User Acceptance Test) environments. For instance, just assume that there are a bunch of database diagrams created by developers in development environment. So, you’ll have an exact copy of the database structure in test environment. Your testers need to use the database diagrams created by the developers. So far, so good. This part of the challenge is covered in the previous articles. However, we still need to install database diagram support manually and this is what we don’t like! The solution is really easy. Run the following code and you are done! You can also add the following code to the execute SQL task from your SSIS package if you decided to implement the solution in an SSIS package (take a look at “Migrating database diagram by creating a simple SSIS package” No. 8).

Continue reading How to install database diagram support programmatically

How to copy or migrate database diagrams into another database

We discussed in one of the previous articles called How to store a SQL Server database diagram into a file and share it with others?”   we can store database diagrams in files and share the files with others. In this article I’m describing very fast and easy ways to make a copy of existing database diagrams into another database. The possible scenarios are:

1.       We want to create a copy of database diagrams into another database in the same SQL Server instance

2.       We want to make a copy of database diagrams in another instance of SQL server

In both cases we need to have write access permission on the destination database.

Migrating database diagrams between two databases in the same instance

We just need to run the following T-SQL script:

use DESTINATIONDB

IF OBJECT_ID(N’dbo.sysdiagrams’) IS  NULL

begin

              CREATE TABLE dbo.sysdiagrams

              (

                     name sysname NOT NULL,

                     principal_id int NOT NULL,

                     diagram_id int PRIMARY KEY IDENTITY,

                     version int,

      

                     definition varbinary(max)

                     CONSTRAINT UK_principal_name UNIQUE

                     (

                           principal_id,

                           name

                     )

              )

EXEC SYS.SP_MS_MARKSYSTEMOBJECT ‘sysdiagrams’ Creating a system object

End

Insert into sysdiagrams (name,principal_id,version,definition)

select name,principal_id,version,definition from SOURCEDB.dbo.sysdiagrams

 

 

The above solution works even if you did not install diagram support and you’ll have the copy of diagrams in place immediately after installing diagram support. To install database diagram support:

1.       Expand the destination database

2.       Right click on “Database diagrams”

3.       Click “Install Diagram Support”

Continue reading How to copy or migrate database diagrams into another database

How to send more precise SSIS logs (errors) through email

First of all you need to read my previous article called “How to send SSIS logs (errors) through email” as the processes are pretty the same. However, you need to create some changes in the Execute SQL Task (ref.: section “J” number 5 and 12 of “How to send SSIS logs (errors) through email”). As an Execute SQL Task is used to collect the logs stored in SQL Server you need to be familiar with parameter mapping in Execute SQL task and know how it works. Assume that we need to just send the “Error” logs that are happened between “Event Handler Start Time” and the current time for the current package execution. As you can imagine it is slightly different from what we did in the previous article to email the SSIS logs to the system administrators. As there was just one system variable that we mapped in parameter mapping section in the Execute SQL Task. But, here we need to have one more system variable mapped to a parameter. Please note that we are using OLEDB connection to connect to the SSIS log database that we created before to store SSIS logs. So there are some important points with OLEDB Connection and Execute SQL Task parameter mapping and its SQL statement.

Continue reading How to send more precise SSIS logs (errors) through email

How to send SSIS logs (errors) through email

First of all I want to explain the process of sending SSIS Logs through email for better understanding. As you might know you can save SSIS logs in several ways for administration purposes. In this article our focus is on how to send SSIS logs to the administrator whenever an event like a package failure is occurred. Just assume that we have several SSIS packages and we are storing the logs in a SQL Server database. Almost all of the packages are running over night. Now, what if a package or some packages failed? Generally one of the most essential activities is that a notification email should be sent to the system administrators to let them know that something’s wrong with the package execution. It could be more helpful to send them the relevant information about the failure. Reading the following lines you can handle this important part of the process in your organisation.

Continue reading How to send SSIS logs (errors) through email