Using SSIS to Import HTTP-Based XML Document

Introduction

The post, XML Document Sourced SSRS Report Using HTTP URL, demonstrated how a SQL Server Reporting Services (SSRS) data source can be used to reference an Extensible Markup Language (XML) document located via Hypertext Transfer Protocol (HTTP) protocol-based URL. Another approach to reporting off an XML document is to first extract the XML data using SQL Server Integration Services (SSIS), store it in a relational engine such as such Microsoft SQL Server and then point your SSRS data sources to SQL Server.  

Similar to the aforementioned post, the XML document that we will be using is the sitemap file of select SIFISO blogs which is located here.

The aforementioned document looks as shown below:

14

Source Code Changesets

The changesets associated with the source code for this article can be found on GitHub and CodePlex as shown below, respectively:

98

Walkthrough

Okay, let’s get started.

Add new SSIS package and assign it a name (I have labelled mine, ImportSitemap_data)

10

Navigate to SSIS Toolbox in SQL Server Data Tools (SSDT) SSIS Designer. Drag and drop a data flow task into the control flow. I prefer to assign meaningful names to my SSIS components so I have renamed my data flow task to Data Flow Task – Import Sitemap data.

1dft

Let’s configure the newly added data flow task.

Click on the Data Flow tab next Control Flow. Navigate to SSIS Toolbox window. Drag and drop an XML Source component into the data flow. Rename the XML Source component to XML Source – blog sitemap.

At this point your package should look as follows:

1211

Let’s configure the XML Source component.

Under connection manager tab, set Data access mode: property to XML file location.

Set the XML location to http://www.blogs.selectsifiso.com/sitemap.xml.

You will soon be required to provide an XML Schema Definition file (XSD). The easiest way is to generate the XSD file using the XML location document. During the generating of the file, you will be asked for save-file location (I saved mine in c drive).

4

Click on the columns tab to refresh the metadata and then click OK to commit the configuration settings.

5

That’s basically conclude the settings for extracting the XML data.

However, for the purposes of troubleshooting and ensuring that the package works, we can go on and add a Row Sampling transformation component.

Connect the XML source component to Row Sampling.

2

Configure the Row Sampling component as follows:

6

7

Finally, add a data viewer to take a look at the data as you extract it during runtime:

dataviewer

Your data flow should look as follows:

11

Conclusion

That’s all folks.

I hope you were able to learn something from this post.

Until next time, cheers.

XML Document Sourced SSRS Report Using HTTP URL

Introduction

In my Business Intelligence development experience, most of my SQL Server Reporting Services (SSRS) Reports have largely been based off a relational data sources such Microsoft SQL Server and Oracle database. However, in some instances whereby an SSRS report is based off Operational Data Source (ODS) or Online transaction processing (OLTP) system, Extensible Markup Language (XML) could be a designated data source. There are numerous ways of source XML as a data source which includes embedded XML data, and Web service endpoints that return XML data. However, in this post, I will take you through an example of sourcing an XML document via Hypertext Transfer Protocol (HTTP) protocol-based URL.

The XML document that we will be using is that of the select SIFISO blogs which is located here.

The aforementioned document looks as shown below:

14

Source Code Changesets

The changesets associated with the source code for this article can be found on GitHub and CodePlex as shown below, respectively:

11_a tfs_11

 

Walkthrough

Okay, let’s get started.

Create new SSRS report and configure your (shared) report data source as follows:

2

You don’t have to edit the credentials tab.

Let’s move on to the shared data set. I’ve decided to retrieve all top levels of data from our data source hence the shared data set query is defined as shown below:

4

Whilst on the shared data set – click on the fields tab such that the below is shown:

6

 

That concludes our configuration of XML data set.

Now we move on to visualizing the XML data and for that I decided to use an SSRS chart control. We understand that search engines uses a sitemap to crawl and rank a given website – thus an updated and refreshed sitemap can increase the site ranking on search engines. Therefore, a possible business case from our XML data could be: “how often is the sitemap refreshed?”.

We go on solving this by adding a new report item and configure the report data to look as follows:

8

 

Add a chart control into the body of your report and configure the chart properties as follows:

10

 

I have chosen a Bar chart type but feel free to choose a different chart type else:

11

 

Define your series group as shown below:

12

Let’s define our measure as follows:

13

 

The design view of your report should look as follows (provided you used the same chart type as I did):

9

The preview/run time view should be able to address the business case and thus look as follows:


1
  

Conclusion

That’s all folks.

I hope you were able to learn something from this post.

Until next time, cheers.

Load Integration Services Assembly File Into Visual Studio 2010 Project

Abstract

SQL Server Integration Services 2012 comes with a new API for scripting packages which is called MOM – Managed Object Model. This API is accessed through the Microsoft.SqlServer.Management.IntegrationServices.dll assembly file. However, when you create an SSIS 2012 project/package using Visual Studio 2010, the assembly file is missing.

In this post, I will show you one of the ways of loading the assembly into project.

Requirements

Article

We begin by launching Microsoft Visual Studio and create a new Integration Services Project which is located under Business Intelligence Projects category.

After you have assigned a project name, proceed to click and drag the Script Task into Control Flow pane from toolbox.

I have called the package “sS_LoadAssembly.dtsx” and the Script Task “Scripting SSIS 2012” as shown below.

In Solution Explorer, right click the package “sS_LoadAssembly.dtsx” as shown below.

Click “View Code”

An XML file called “sS_LoadAssembly.dtsx[XML]” is opened

Take note of the elements under node “DTS:ObjectData” – there is currently a single element called “ScriptProject”

Let’s go back to the file called “sS_LoadAssembly.dtsx[Design”

Right click the script task and click on “Edit”

Under the Script Task Editor change the “ScriptLanguage” to “Microsoft Visual C# 2010″.

Click Edit Script.

Close the script.

Save the changes.

Go back to the “sS_LoadAssembly.dtsx[XML]” XML file.

You will notice that additional elements have been addedd under node “DTS:ObjectData”

We are interested in the node called “ItemGroup”

Let’s add a reference to the Microsoft.SqlServer.Management.IntegrationServices.dll assembly file as follows:

  • <Reference Include=”Microsoft.SqlServer.Management.IntegrationServices, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91″ />

Save all changes.

Go back to the file called “sS_LoadAssembly.dtsx[Design”

Right click the script task and click on “Edit”

Click Edit Script.

Collapse “Namespaces”

Insert the following:

  • using Microsoft.SqlServer.Management.IntegrationServices;

Conclusion

Voilà! Now you can go ahead and access the new API for scripting SSIS 2012.

Cheers.

Sifiso.

Download & Save SSIS Packages from SQL Server 2008 Instance to XML Format

Abstract

This article explains how to Programmatically download SQL Server Integration Services (SSIS) packages from an instance of SQL Server 2008 and save them into an XML file using a Microsoft’s SSIS Script Task component.

Requirements

Article

If the above requirements are all met, we will begin by launching Microsoft Visual Studio edition.

Create a new project Integration Services Project which is located under Business Intelligence Projects.

After you have named the new project, proceed to click and drag the script task in Control Flow pane of the new package.

Right click the script task and click on “Edit”

Under the Script Task Editor change the “ScriptLanguage” to “Microsoft Visual C# 2008”.

In Project Explorer import relevant references and ensure that you have declared namespaces as below:

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Dts.Tasks;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Collections.Generic;
using System.Collections.Specialized;

After declarations, create instances of application and SSIS package:

Application SIFISO_app = new Application();
Package p = new Package();

Create local variables that will be used store sql server instance name and database. (For the purposes of this discussion, the database name refers to msdb):

string set_server = “your_sql_server_instance_name”;
string config_database = “msdb”;

The below script will continue to demonstrate creating and establishing an OLE DB connection as below (please note: folderid – ‘00000000-0000-0000-0000-000000000000’ – is the default root folder):

SqlConnection connectiont = new SqlConnection(
string.Format(“Data Source={0};Initial Catalog={1};Integrated Security=SSPI;”, set_server, config_database));
SqlCommand commandt = new SqlCommand(
“select * from msdb.dbo.sysssispackages where folderid =’00000000-0000-0000-0000-000000000000′”, connectiont);
connectiont.Open();

SqlDataAdapter adap = new SqlDataAdapter(commandt);

DataSet ds = new DataSet();
adap.Fill(ds);

if (ds.Tables[0].Rows.Count > 0)
{
int intCount = 0;
while (intCount < (ds.Tables[0].Rows.Count)) { try { string pkg_name = ds.Tables[0].Rows[intCount].ItemArray[0].ToString(); string pkg_full_name = @"\\" + pkg_name + ""; string pkg_full_save_loc = @"C:\Sifiso\Documents\SSIS_Package_Backup\PROD\" + pkg_name + ".dtsx"; //Change package protection level to - DontSaveSensitive //This is to prevent specifying passwords for individual packages Package importPackage = SIFISO_app.LoadFromSqlServer(pkg_full_name, set_server, null, null, null); importPackage.Name = pkg_name; importPackage.ProtectionLevel = DTSProtectionLevel.DontSaveSensitive; SIFISO_app.SaveToXml(pkg_full_save_loc, importPackage, null); intCount++; } //Exception Handling //create a table that will store list of package names that failed to download catch { commandt = new SqlCommand("insert into [SIFISO_TEST].[dbo].[not_copied_tables] values('" + ds.Tables[0].Rows[intCount].ItemArray[0].ToString() + "')", connectiont); commandt.ExecuteNonQuery(); intCount++; } } }

We then save the package into a file system.

Dts.TaskResult = (int)ScriptResults.Success;
SIFISO_app.SaveToXml(“C:\\TEMP\\pkg_ArchiveToXML_Csharp.dtsx”, p, null);

Conclusion

It’s that simple!

You can now execute your script task and your packages will be saved into the file system specified.