SSIS Integration

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow the complete package is ready to build and execute. Press F5 to start executingthe project and we can see the screen looks like the screen below.© Karthikeyan Anbarasan, www.f5Debug.net 300

SQL Server Integration Services (SSIS) – Step by Step TutorialNow let’s see the end result of the package after execution, to check that we need togo to the destination database and run the destination query and we can see theresult as shown in the screen below.w w w .f5 d e b u g .n e t© Karthikeyan Anbarasan, www.f5Debug.net 301

SQL Server Integration Services (SSIS) – Step by Step TutorialConclusionIn this chapter we have seen on how to use the Merge Join transformation task andthe key configurations used in order to use this task handy.w w w .f5 d e b u g .n e t© Karthikeyan Anbarasan, www.f5Debug.net 302

SQL Server Integration Services (SSIS) – Step by Step Tutorial Chapter 53 MULTI CAST TRANSFORMATIONIntroductionIn this chapter we are going to see how to use the Multicast transformation in SSISpackaging. Multi cast transformation is used when a portion of the data needs to betransferred to some other flow which is something similar to Conditional task which dothe same process.The difference between the Multicast and the conditional split transformations aremulticast direct each row of the source to the every output where as in the conditionalsplit it directs a row to a single output. So using the multicast we can create data ofseparate logic and try to have it secured across the flow. This transformation can havea single input with multiple outputs across to pass the data.Let’s jump start to see this sample on how to set the properties of the control.StepsFollow steps 1 to 3 of the first chapter to open the BIDS project and select the rightproject to work on integration services project. Once the project is created, we will seeon how to use the Multi Cast Transformation task. Once you open the project just dragand drop the Multi Cast transformation control and a source provider as shown in thebelow image.w w w .f5 d e b u g .n e t© Karthikeyan Anbarasan, www.f5Debug.net 303

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow we need to configure the source OLEDB data source as shown in the screenbelow.© Karthikeyan Anbarasan, www.f5Debug.net 304

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tOnce the source is configured now drag and drop 3 destinations providers as shown inthe screen below.1 – OLEDB provider task to update the table.2 – Flat file provider to update a file.3 – Excel provider to update to an excel file.© Karthikeyan Anbarasan, www.f5Debug.net 305

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow let’s configure each destination based on the requirement as shown in the screenbelow. Here we created a new table where it will copy the complete dataset to thedestination table using the multicast provider.© Karthikeyan Anbarasan, www.f5Debug.net 306

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow let’s configure the flat file destination provider as shown in the screen below.© Karthikeyan Anbarasan, www.f5Debug.net 307

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow let us configure the excel destination provider as shown below. 308© Karthikeyan Anbarasan, www.f5Debug.net

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow all the destination providers are configured. Now go back to the Multicastprovider and double click to see the configuration as shown in the screen below.© Karthikeyan Anbarasan, www.f5Debug.net 309

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tOnce everything is configured we can see our screen look like the screen below.© Karthikeyan Anbarasan, www.f5Debug.net 310

SQL Server Integration Services (SSIS) – Step by Step TutorialNow the package is ready to execute. Press F5 and we can see the screen looks likebelow which indicates that the execution is completed.w w w .f5 d e b u g .n e tTo see the output of different destinations we can go ahead to the respective sectionsas shown in the screen below.© Karthikeyan Anbarasan, www.f5Debug.net 311

SQL Server Integration Services (SSIS) – Step by Step TutorialOutput1: TableOutput 2: Flat filew w w .f5 d e b u g .n e t© Karthikeyan Anbarasan, www.f5Debug.net 312

SQL Server Integration Services (SSIS) – Step by Step TutorialOutput 3: Excel destination filew w w .f5 d e b u g .n e tConclusionIn this chapter we have seen how to use the Multi Cast transformation task and thekey configurations used in order to use this task handy.© Karthikeyan Anbarasan, www.f5Debug.net 313

SQL Server Integration Services (SSIS) – Step by Step Tutorial Chapter 54 TRANSFORMATION CATEGORIZEDIntroductionIn this chapter we are going to see on the different transformation on how they arecategorized to use it across the package. This categorization is based on the usage ofthe transformation and the process on how it can be used across. The briefdescriptions of each transformation are available in my previous chapters.w w w .f5 d e b u g .n e tWe can partition the transformation into 7 types as shown below.1. Row Transformations2. Rowset Transformations3. Split and Join Transformations4. Quality Transformations5. Mining Transformations6. Synchronous and Asynchronous Transformations7. Other TransformationsNow these transformations are further sub categorized as below.Row Transformations – Used to update column values and is applied to each rowin the input dataset.  Character Map 314  Copy Column  Data Conversion© Karthikeyan Anbarasan, www.f5Debug.net

w SQL Server Integration Services (SSIS) – Step by Step Tutorial w w Derived Column .f5 Script Component d OLE DB Command e bRowset Transformations – Used to create a new Rowset u g Aggregate .n Sort e Percentage Sampling t Row Sampling  Pivot  UnpivotSplit and Join Transformations – Used to distribute rows to different outputs,create copies of the transformation inputs.  Conditional Split  Multicast  Merge  Merge Join  Union All  LookupData Quality Transformations – Used to perform data quality operations  Fuzzy Lookup  Fuzzy GroupingData-Mining Transformations – Used to perform data-mining operations  Data-Mining Query  Term Extraction  Term LookupSynchronous and Asynchronous – Used to determine how rows are processed.  Synchronous transformations  Asynchronous transformations  Partially blocking transformations  Blocking transformations© Karthikeyan Anbarasan, www.f5Debug.net 315

SQL Server Integration Services (SSIS) – Step by Step TutorialOther Transformations – Some extra transformations  Export Column  Import Column  Audit  Row Count  Slowly Changing DimensionConclusionIn this chapter we have seen on the different transformation and the subcategories ofeach section to get familiarize with the different transformations available and thebest can be selected for the business requirement.w w w .f5 d e b u g .n e t© Karthikeyan Anbarasan, www.f5Debug.net 316

SQL Server Integration Services (SSIS) – Step by Step Tutorial Chapter 55 CONNECTION MANAGERSIntroductionIn this chapter we are going to see on the different connection managers available inSSIS packaging and the purpose of each transformation. We have 17 differentconnection managers across the SSIS packaging in order to use it for differentpurposes. wList of Connection Managersw wS No .f5 d e b u g .n e t Name Short Description Used to connect to ActiveX Data Objects (ADO) objects, like1 ADO a recordset.2 ADO.NET Used to access data sources by using a .NET provider and Microsoft SQL Server and XML3 Cache Used to read data from the cached server or from a cache file(.caw) so that the data is stored in the memory.4 Excel Used to connect to an existing Microsoft Excel workbook file for both Source and Destination processing of package5 File Used to connect to an existing file or to create a new file and use as a source or a destination.6 FlatFile Used to connect to a Flat file which acts like a source or a destination for the package to access and process the data© Karthikeyan Anbarasan, www.f5Debug.net 317

SQL Server Integration Services (SSIS) – Step by Step Tutorial across the platform.7 FTP Used to connect to a File Transfer Protocol Server to fetch the data or to update the data to the server.8 HTTP Used to access the webserver using the Hyper text transfer protocol to send and receive files across the servers9 MSMQ Used to connect to the Microsoft Message queuing server to access the messages as a source or to update the message as a destinationw w10 MultiFileUsed to reference to the existing file or folders or to create w a new file and use it as a reference at runtime. .f5 d e b u g .n e t11 MultiFlatFile Used to access the file using the flat file as multiple data source like using inside a loop container to loop through the file and access the data12 OLEDB Used to connect to the different data source using the OLEDB provider specifically used to connect to Microsoft SQL Server.13 ODBC Used to connect to different relational database system using the open connectivity provider14 SMOServer Used to connect to a SQL Management server objects to access for as a source or to update as a destination15 SMTP Used to connect to a Simple Mail transfer Protocol server to access and send mail or to receive mails16 SQL Compact Used to connect to SQL Server Compact database for light weight accessing of the server.17 WMI Used to connect to the Windows Management Instrumentation (WMI) in order to connect to the enterprise server for management.© Karthikeyan Anbarasan, www.f5Debug.net 318

SQL Server Integration Services (SSIS) – Step by Step TutorialConclusionIn this chapter we have seen on the different connection managers used in the SSISPackaging using SQL Server Business Intelligence Studiow w w .f5 d e b u g .n e t© Karthikeyan Anbarasan, www.f5Debug.net 319

SQL Server Integration Services (SSIS) – Step by Step Tutorial Chapter 56 DATA VIEWERSIntroductionIn this chapter we are going to see on how to use the Data viewers in SSIS packaging.Data viewers are used as one of the debugging option for the developers to check thedata between the processes of a packaging.Data viewers are good at places where we have a minimum data to analyze and thenexecuting the package at the development stage to see the changed took place in theprior task and proceed to the next task.Let’s jump start to see this sample on how to set the properties of the control.StepsFollow steps 1 to 3 of the first chapter to open the BIDS project and select the rightproject to work on integration services project. Once the project is created, we will seeon how to use the data viewers to see the data flow.Now once the project is opened drag and drop a source and a destination task alongwith a dataflow to do some manipulation as shown in the screen below.w w w .f5 d e b u g .n e t© Karthikeyan Anbarasan, www.f5Debug.net 320

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow we will do a small task on copying the data from the source table to a destinationfile using the OLEDB source and Flatfile destination as shown above.Now let’s configure both the tasks to make a flow as shown below.© Karthikeyan Anbarasan, www.f5Debug.net 321

SQL Server Integration Services (SSIS) – Step by Step TutorialSource Configuration:w w w .f5 d e b u g .n e t© Karthikeyan Anbarasan, www.f5Debug.net 322

SQL Server Integration Services (SSIS) – Step by Step TutorialDestination Configuration:w w w .f5 d e b u g .n e tOnce we are done with the configuration setting we can see our screen look as shownin the screen below.© Karthikeyan Anbarasan, www.f5Debug.net 323

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow when we execute the package it will do the execution but we are not sure atwhat point what happens. So in order to see the transformation between the sourceand the destination we can use a data viewer browser.Data viewer provides different options to view the data, the types are: Grid,Histogram, Scatter Plot, and Chart Format. To start the data viewer Right click on thegreen arrow which connects the source and destination and select the data viewer. Itwill open the window as shown below.© Karthikeyan Anbarasan, www.f5Debug.net 324

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow click on Add button to do the configuration of our required data viewer. It willopen the window as shown in the screen below.© Karthikeyan Anbarasan, www.f5Debug.net 325

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow click on ok will take the Grid view. Now we are done with the configuration andready to execute the package. We can see a viewer icon next to the arrow as shown inthe screen below which indicates that the viewer is active to view.© Karthikeyan Anbarasan, www.f5Debug.net 326

SQL Server Integration Services (SSIS) – Step by Step TutorialNow we will execute the package and see the data viewer browser. Press F5 toexecute the package and we can see the data viewer browser as shown in the screenbelow.w w w .f5 d e b u g .n e t© Karthikeyan Anbarasan, www.f5Debug.net 327

SQL Server Integration Services (SSIS) – Step by Step TutorialWe have an arrow button in the browser, once we are done with our analysis we canclick on the button to proceed. Once we click that button the execution start andproceed further and the final screen will appear as shown in screen below.w w w .f5 d e b u g .n e tConclusion:In this chapter we have seen how to use the data viewer to analyze the data and toproceed further which acts like a debugging portion for SSIS packaging.© Karthikeyan Anbarasan, www.f5Debug.net 328

SQL Server Integration Services (SSIS) – Step by Step Tutorial Chapter 57 DATA VIEWERS (HISTOGRAM)IntroductionIn this chapter we are going to see how to use the Data viewers (Histogram) in SSISpackaging. Data viewers are used as one of the debugging option for the developers tocheck the data between the processes of a packaging.Data viewers are good at places where we have a minimum data to analyze and thenexecuting the package at the development stage to see the changed took place in theprior task and proceed to the next task.Let’s jump start to see this sample how to set the properties of the control.StepsFollow steps 1 to 3 of the first chapter to open the BIDS project and select the rightproject to work on integration services project. Once the project is created, we will seehow to use the data viewers to see the data flow.Now once the project is opened drag and drop a source and a destination task alongwith a dataflow to do some manipulation as shown in the screen below.w w w .f5 d e b u g .n e t© Karthikeyan Anbarasan, www.f5Debug.net 329

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow we will do a small task on copying the data from the source table to a destinationfile using the OLEDB source and Flatfile destination as shown in the screen above.Now let’s configure both the tasks to make a flow as shown in the screen below.© Karthikeyan Anbarasan, www.f5Debug.net 330

SQL Server Integration Services (SSIS) – Step by Step TutorialSource Configuration:w w w .f5 d e b u g .n e t© Karthikeyan Anbarasan, www.f5Debug.net 331

SQL Server Integration Services (SSIS) – Step by Step TutorialDestination Configuration:w w w .f5 d e b u g .n e tOnce we are done with the configuration setting we can see our screen look as shownin the screen below.© Karthikeyan Anbarasan, www.f5Debug.net 332

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow when we execute the package it will do the execution but we are not sure atwhat point what happens. So in order to see the transformation between the sourceand the destination we can use a data viewer browser.Data viewer provides different options to view the data, the types are: Grid,Histogram, Scatter Plot, and Chart Format. In this sample we will see how to use theHistogram option to view. To start the data viewer Right click on the green arrowwhich connects the source and destination and select the data viewer. It will open thewindow as shown below.© Karthikeyan Anbarasan, www.f5Debug.net 333

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow click on Add button to do the configuration of our required data viewer. It willopen the window as shown below.© Karthikeyan Anbarasan, www.f5Debug.net 334

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow we will select the Histogram since we are going to see how to use the Histogram.We have a tab Histogram just navigate to that tab and select the column as shownbelow.© Karthikeyan Anbarasan, www.f5Debug.net 335

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow we are done with the configuration and ready to execute the package. We cansee a viewer icon next to the arrow as shown below which indicates that the viewer isactive to view.© Karthikeyan Anbarasan, www.f5Debug.net 336

SQL Server Integration Services (SSIS) – Step by Step TutorialNow we will execute the package and see the data viewer browser. Press F5 toexecute the package and we can see the data viewer browser as shown in the screenbelow.w w w .f5 d e b u g .n e t© Karthikeyan Anbarasan, www.f5Debug.net 337

SQL Server Integration Services (SSIS) – Step by Step TutorialWe have an arrow button in the browser, once we are done with our analysis we canclick on the button to proceed. Once we click that button the execution start andproceed further and the final screen will appear as below.w w w .f5 d e b u g .n e tConclusionIn this chapter we have seen how to use the data viewer (Histogram) to analyze thedata and to proceed further which acts like a debugging portion for SSIS packaging.© Karthikeyan Anbarasan, www.f5Debug.net 338

SQL Server Integration Services (SSIS) – Step by Step Tutorial Chapter 58 DATA VIEWERS (SCATTER PLOT)w wIntroduction w .f5 d e b u g .n e tIn this chapter we are going to see on how to use the Data viewers (Scatter Plot) inSSIS packaging. Data viewers are used as one of the debugging option for thedevelopers to check the data between the processes of a packaging.Data viewers are good at places where we have a minimum data to analyze and thenexecuting the package at the development stage to see the changed took place in theprior task and proceed to the next task.Let’s jump start to see this sample on how to set the properties of the control.StepsFollow steps 1 to 3 of the first chapter to open the BIDS project and select the rightproject to work on integration services project. Once the project is created, we will seeon how to use the data viewers to see the data flow.Now once the project is opened drag and drop a source and a destination task alongwith a dataflow to do some manipulation as shown in the screen below.© Karthikeyan Anbarasan, www.f5Debug.net 339

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow we will do a small task on copying the data from the source table to a destinationfile using the OLEDB source and FlatFile destination as shown above.Now let’s configure both the tasks to make a flow as shown below.© Karthikeyan Anbarasan, www.f5Debug.net 340

SQL Server Integration Services (SSIS) – Step by Step TutorialSource Configuration:w w w .f5 d e b u g .n e t© Karthikeyan Anbarasan, www.f5Debug.net 341

SQL Server Integration Services (SSIS) – Step by Step TutorialDestination Configuration:w w w .f5 d e b u g .n e tOnce we are done with the configuration setting we can see our screen look as shownin the screen below.© Karthikeyan Anbarasan, www.f5Debug.net 342

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow when we execute the package it will do the execution but we are not sure atwhat point what happens. So in order to see the transformation between the sourceand the destination we can use a data viewer browser.Data viewer provides different options to view the data, the types are: Grid,Histogram, Scatter Plot, and Chart Format. In this sample we will see on how to usethe Scatter Plot option to view. To start the data viewer Right click on the green arrowwhich connects the source and destination and select the data viewer. It will open thewindow as shown below.© Karthikeyan Anbarasan, www.f5Debug.net 343

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow click on Add button to do the configuration of our required data viewer. It willopen the window as shown in the screen below.© Karthikeyan Anbarasan, www.f5Debug.net 344

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow we will select the Scatter plot(x,y) since we are going to see on how to use theScatter plot. We have a tab Scatter Plot just navigate to that tab and select the columnas shown below.© Karthikeyan Anbarasan, www.f5Debug.net 345

SQL Server Integration Services (SSIS) – Step by Step Tutorialw w w .f5 d e b u g .n e tNow we are done with the configuration and ready to execute the package. We cansee a viewer icon next to the arrow as shown below which indicates that the viewer isactive to view.© Karthikeyan Anbarasan, www.f5Debug.net 346

SQL Server Integration Services (SSIS) – Step by Step TutorialNow we will execute the package and see the data viewer browser. Press F5 toexecute the package and we can see the data viewer browser as shown in the screenbelow.w w w .f5 d e b u g .n e t© Karthikeyan Anbarasan, www.f5Debug.net 347

SQL Server Integration Services (SSIS) – Step by Step TutorialWe have an arrow button in the browser, once we are done with our analysis we canclick on the button to proceed. Once we click that button the execution start andproceed further and the final screen will appear as shown in the screen below.w w w .f5 d e b u g .n e tConclusionSo in this chapter we have seen on how to use the data viewer (Scatter Plot) to analyzethe data and to proceed further which acts like a debugging portion for SSIS packaging.© Karthikeyan Anbarasan, www.f5Debug.net 348

SQL Server Integration Services (SSIS) – Step by Step Tutorial Chapter 59 DATA VIEWERS (COLUMN CHART)IntroductionIn this chapter we are going to see on how to use the Data viewers (Column Chart) inSSIS packaging. Data viewers are used as one of the debugging option for thedevelopers to check the data between the processes of a packaging.Data viewers are good at places where we have a minimum data to analyze and thenexecuting the package at the development stage to see the changed took place in theprior task and proceed to the next task.Let’s jump start to see this sample on how to set the properties of the control.StepsFollow steps 1 to 3 on my first chapter to open the BIDS project and select the rightproject to work on integration services project. Once the project is created, we will seeon how to use the data viewers to see the data flow.Now once the project is opened drag and drop a source and a destination task alongwith a dataflow to do some manipulation as shown below.w w w .f5 d e b u g .n e t© Karthikeyan Anbarasan, www.f5Debug.net 349