This book aims at the new version of SSIS, which is SSIS 2012. There are a bunch of changes in SSIS 2012, this recipe covers some of the differences between SSIS 2012 and previous versions. The SSIS 2012 changes aren't limited to SSDT and design changes alone, there are also many changes while interacting with outside packages and package deployment, as well as new tasks and transformations; all of which we will explore in different recipes of this book in appropriate case scenarios. For this recipe, differences in SSDT as compared to previous versions are highlighted.
As this recipe compares the two versions, having SSIS 2012 and SSIS 2008 installed can be useful. SSIS 2012 is required but 2008 is optional (will help in comparison).
Create a New SSIS Project.
The first difference you'll notice in SSIS 2012 is that the SSDT is Visual Studio 2010 with a lot of improvements in the Editor. Earlier versions of SSIS work with previous versions of Visual Studio. SSIS 2008 worked with Visual Studio 2008, and SSIS 2005 worked with Visual Studio 2005.
When a new Package is created, the SSIS Toolbox is shown in the SSDT. As you would notice in the SSIS Toolbox, there are some categories that differ from the previous SSIS 2008 version. The previous version had only three sections: Control Flow Items, Maintenance Plan Tasks, and General. In SSDT 2010, sections are organized as: Favorites, Common, Containers, and Other Tasks. Favorites contain tasks that can be moved in here by right-clicking on items and then clicking on Move to Favorites. Common consists of a list of tasks that is among the most useful tasks. Containers have their own section in this version of SSDT, and all other tasks are placed in the Other Tasks section.
A quick description of each task or container is available under the SSIS Toolbox, as you can see in the following screenshot:
There are two icons for switching between the Toolbox pane and the Variables pane in Package Designer.
There is a scroll bar magnifier on the Control Flow tab which adds the ability to magnify a view of Control Flow, you can also choose auto fit.
There is a new tab in the SSDT 2012 Package Designer named Parameters; we will discuss this Parameters tab in later chapters.
There is also an Undo icon ; SSIS 2008 and 2005 suffered from the lack of undo operations, but in SSDT 2012 you can undo operations with Ctrl + Z or by clicking on the undo icon.
In the Solution Explorer, create a new Project Level connection. This is a shared connection which can be seen in all packages' connection manager's pane within the project.
Right-click on the blank Control Flow area and select Getting Started; you will see the new Getting Started pane on the right-hand side at the bottom of SSDT, which shows some help and links as seen in the following screenshot:
Drag-and-drop a Script Task from toolbox to control flow, and double-click on Script Task. In the Script Task Editor, in the Script Language property , you can select between Visual C# 2010 and Visual Basic 2010 both of which are under the .NET 4.0 Framework. We will explore all details about scripting SSIS in later chapters.
Go to the Data Flow tab, you will see this sentence: No Data flow task has been added to this package. Click here to add a new data flow task. Just click on the link and a new empty data flow will be created.
Notice that there are two new options Source and Destination in the SSIS Toolbox which can be found under the Favorites sections Source Assistant and Destination Assistant. With these assistants you can simply select a data source or destination as you want with a single assistant component.
We had already reviewed some changes to SSDT Design in the Control Flow and Data Flow tab areas. There are lots of other changes such as better annotations, grouping components in data flow, and many other improvements in the UI which we will explore with the many examples in this book in later recipes.
On the other hand, there are some major changes like new tasks and transformations, like Expression Task, CDC Control Task, Data Flow Components, and DQS Cleansing transformation which we will explore in appropriate chapters.
There are major changes in the deployment of packages and working with packages from outside and package execution, which will be explored in package deployment chapters later on.