SQL Server Integration Services then (2005) and now (2015)

In 2005 there was no program called SQL Server Data Tools and Microsoft had just come up with .NET based technology. Microsoft Business Intelligence’s SQL Server Integration Services had Visual Studio components that had replaced the Data Transformation Services of SQL Server with tighter integration with SQL Server 2005. It was a big leap. I wrote my first book on SQL Server Integration Services 2005 during 2007. It had a lot of reviews good/bad but it survived and quite a good number of folks thought it was useful and it still sells.

SSIS2005.00
Fast forward to 2015, a lot of data related methodologies has changed. In the interim, I have not paid attention to the details although I am aware of the changes. In the intervening years technology has been on a rapid pace with the advent of Cloud Services, Streaming data, BigData, NoSQL etc.

It is no wonder that Microsoft has worked on their tools to address these developments and fortify Visual Studio Tools. This post is about a comparative look at the Toolbox items then and now.  Of course there is lot more to it than just adding new tools.

In Visual Studio 2005 IDE there were a set of Business Intelligence project templates to address the various needs at that time:

•    Analysis Services Project
•    Integration Services Project
•    Report Model Project
•    Import Analysis Services Database…
•    Report Server Project Wizard
•    Report Server Project

The Visual Studio 2005 IDE, a very thorough discussion of this in video format may be found here.

“The full comprehensive and detailed use of the Visual Studio 2005 IDE is described in my book, "Beginners guide to SQL Server Integration Services 2005" published by Packt Publishing Inc. The book has 20 of the more commonly used SSIS tasks that you may come across in your developmental work. Targeted to beginners as well as for those moving from DTS to SSIS, the book is essentially hands-on with screen shots that explains the subject matter. By the end of the book you would have learnt those 20 tasks and ready to probe more on your own.
- See more at: http://www.sswug.org/articlesection/default.aspx?TargetID=44780#sthash.ik4QFFCB.dpuf”

In the case of Integration Services, basically the project was based on Packages that used Data Sources, Data Destination and transformations that worked between the Sources and Destinations while Data Flow Tasks did the actual task of moving the data based on the choice of data flow task.
The package was an assembly consisting of several objects and, other packages could be nested as well.

Specifically, a package can consist of:

•    Connections
•    Control Flow Elements
•    Data Flow Elements
•    Event Handlers
•    Variables
•    Configurations

Here are a few images relevant to Integration Services 2005 for Data Flow:
Data Flow Sources

SSIS2005_DataFlowSources.jpg

Control Flow Tasks

 

SSIS2005_ControlFlowTasks.jpg

 Data Flow Transformations

SSIS2005_DataflowDestinations.jpg

Data Flow Destinations

 

SSIS2005_DataflowDestinations.jpg

In Visual Studio the appropriate tool for working with SQL Servers as described here is the SQL Server Data Tools to match the version of Visual Studio edition. A stand-alone version is also available which gives access to Visual Studio Shell.

The download mentioned here gives access to SSDT that can work with SQL Servers from version 2005 to SQL Server 2016 CTP3:

•    SSDT Preview for Visual Studio 2015
SQL Platform support:
SQL Server 2005 – 2016 CTP 3.0
Analysis Services for SQL Server 2008 - 2016 CTP 3.0
Integration Services for SQL Server 2016 CTP 3.0
Reporting Services for SQL Server 2008 - 2016 CTP 3.0
Azure SQL Database
Azure SQL Data Warehouse

The following paragraphs use the above preview installed on a Windows 10 (x64) OS platform on a Toshiba Laptop.

Herein follows a description of the Toolbox of the Integration Services for SQL Server 2016 CTP3.0. The Business Intelligence templates in this version of SSDT is shown in the next image.

SSDT_01.jpg

The two Analysis Services project refer to two modes of working with the services. Examples in my Reporting Services 2012 book provide specific examples of these two types of projects.

The Reporting Services Wizard gives guidance at each step,  you can also use the Report Server Project to fashion your own.

Herein we look at the Toolbox items in the SQL Server Integration Services arsenal to compare with those in SSIS 2005 and identify the new ones.

Common folder in Data Flow  (Click Data Flow tab in designer):

SSIS_02.PNG
Other Transforms group in Data Flow :

SSIS_03.PNG

Other Sources group in Data Flow:

 

 SSIS_04a.PNG

 Other Destinations group in Data Flow:

SSIS_05.PNG
 Control Flow Tasks (access the following when Control Flow Tab is chosen in the package designer) in SSIS 2005:
The following image shows the Control Flow items in the SSIS 2005 designer:

controlFlow 001.jpg

Visual Studio BI 2005 also had a Maintenance Plan related controls as shown here.

MaintenancePlan 001.jpg

In SSDT 2015 the tasks are divided into two groups, Common and Other Tasks. The following image shows the tasks in the two groups. Those highlighted in light blue are the same ones found in the Maintenance Plan tasks in 2005 and the ones in Yellow are those related to Control Flow Items in SSIS 2005.

SSIS_10_CF3.PNG

SSIS_9_CF5a.PNG

The For Loop Container, ForEach Loop Container and Sequence Container in the Control Flow Items of VS 2005 have been placed in a separate Containers category as shown.

 SSIS_8_CF3.PNG

Visual Studio has gone through many iterations from 2005 to 2015 and some of these changes have entered the IDE during this period.  While there are additions to the toolbox there are no removals with many of the items rearranged or grouped differently.

The difference between Power Pivot and Power Query

There may be a some overlapping functionality in accessing data as both of them have to access data sources but Power Query is more like SSIS (SQL Server Integration Services) a la EXCEL while Power Pivot (SQL Server Analysis Services) is more like SSAS.

The variety of data sources that Power Query can connect to manage is much wider than that of Power Pivot including the social media created data like Facebook and unstructured data.
Power Pivot has a more powerful analytical role than Power Query which you  can easily make out by just looking at the user interfaces.

However both of them are downloaded as add-ins for MS EXCEL 2010 or 2013 as shown (MS EXCEL 2010 in this case).

You may follow these link for more details:

Connecting to SQL Server 2012 Express from Power Pivot - 1
http://hodentekmsss.blogspot.com/2014/01/connecting-to-sql-server-2012-express.html

Connecting to SQL Server Express 2012 from Power Pivot - 2
http://hodentekmsss.blogspot.com/2014/01/connecting-to-sql-server-express-2012.html

On USING SSIS to transfer data between SQL Server 2008 and MySQL

There are a large number of posts on various difficulties experienced while transferring data from MySQL using Microsoft SQL Server Integration Services. While the transfer of data from MySQL to Microsoft SQL Server 2008 is not fraught with any blocking issues, transfer of data from SQL Server 2008 to MySQL has presented various problems.


For data interchange with MySQL there are two options one of which can be accessed in the connection wizards of SQL Server Integration Services assuming you have installed the programs. The other can be used to set up a ODBC DSN as described further down. The two connection options are:

• MySQL Connector/ODBC 5.1
• Connector/Net 5.2 New versions 6.0 & 6.1

In this article we will be using the ODBC connector for MySQL which can be downloaded from the MySQL Site. The connector will be used to create an ODBC DSN. The article describes in detail how the transfer is carried out. Read on ....
this article brought to you by PACKT PUBLISHING.


MySQL Data Transfer using Sql Server Integration Services (SSIS)


Pick up the mechanics of working with SQL Server Integration 2005 under 10 days reading my no brainer book on SQL Server Integration Serivces.

My presentation at NJ SQL Server Group on April 15, 2008

I will be introducing my book to the group at 6:30 PM at their location in Parsipanny, NJ. I will be mostly talking about some of the wizards of SSIS and will show how beginners can get their feet wet on this very important product from Microsoft.

I will be demonstrating some easy to follow examples using the BETA version of SQL Server 2008 and VS 2008. If you live around Parsipanny you are welcome, but make sure you send an email to the organizer (http://njsql.org/default.aspx).

About my new book on SSIS

About this book

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 provides you with the basic knowledge that you should have before you move on to more advanced ETL [Extraction, Transformation, and Loading]. The book will also provide you with a comprehensive description of the many designer windows that you may encounter while working with the designer. A majority of SSIS tasks are covered in this book and they are described fully in the summary of table contents section. You start building packages right from Chapter 2 and continue on to Chapter 20 gathering and building upon your knowledge in each step.






The book will give you step-by-step explanation of the examples in the book. In each chapter you will get a little background of the SSIS task that you are going to create. You will then step through the numbered steps, creating, configuring, executing, and reviewing the results. The book has minimal coding (only two chapters) and exclusively uses the Visual Studio 2005 Designer.

This book is written for beginners in the developer track who are looking to get an exposure to SQL Server Integration Services; DBA's who are testing water with the Visual Studio IDE but without a wide programming experience; SQL 2000 Data Transformation Services users who are trying to move into SQL Server 2005 Integration Services; Microsoft programming professionals in Small Businesses who wear multiple hats (jack of all) - developer, programmer, and dba with a little bit of experience in each of these.