SQL Server Integration Services
The primary component for extracting, transforming, and loading data. First, drag an OLE DB source unto the designer. Modify the data source by creating a new connection manager and the correct database and table. For this example, I will only leave three columns selected for retrieval. Now drag/drop a Derived Column transformation and connect the…
Using a file-system task to create a folder. First, drag a File System task object unto the designer and rename it according to its purpose. Configure the task to create a directory, set the path for it, and UseDirectoryIfExists=True. Now, execute just that task within the package and your new directory will be created.
Integrating conditional logic into the data pipeline. Many projects requires the use of conditional logic to enforce logic according to business rules. In this example, I’ll illustrate such logic by diverting records between one of two Excel spreadsheets depending on whether each Employee is salaried. First, I’ll drag and configure an OLE Db Source object…
Adding additional data to the output related to package execution. I’ll begin with two separate text files, each representing sales from two states: California and Oregon. First, I’ll add a DataFlow task and add add to it two Flat File sources each configured to pull in data from the text files. When configuring the sources,…
Enabling data-sharing between tasks within a package. Often when performing multiple complex tasks within a package, data created from one tasks must be accessed either for testing or further manipulation by other tasks. Variables provide the best method for providing this kind of cross-collaboration. Variables may provide system data such as those commonly used within…
Transporting data from a source to its destination. The primary purpose of the data flow task is to move data from a source to a destination. Also, in the data flow is where transformations are performed. Moving data and transforming it constitutes the bulk of ETL (Extract-Transform-Load) work done in a BI solution. A package’s…
A brief introduction of Control Flow. Control Flows represent one of the most important aspects of an SSIS package because their logic controls its execution. Control Flow are made up of: Tasks: These are objects that contain steps to perform (Data Flow, FileI/O, scripting). The main categories of tasks are: Workflow Tasks such as Execute…
Exporting SQL Server to a DataReader. In this article, I will demonstrate using SSIS to connect to SQL Server, export data from a table into a DataReader. When we’ve completed this tutorial, you should see the above screen. First, drag a DataFlow task unto the Control Flow designer. Next, double-click the DataFlow task which will…
Commenting (Annotating) your SSIS Package. In this article, I will discuss what might be one of the most overlooked aspects of all development project – commenting. The example above is taken from a mildly-complex complex where I connect to SQL Server and export data from a table into a DataReader while providing two DataViews for debugging (read SQL…
What is SQL Server Integration Services (SSIS) SSIS is Microsoft newest ETL platform. SSIS is a successor not an upgrade of DTS. The primary purpose for SSIS is to migrate and manipulate data in support of the Business Intelligence (BI) lifecycle (SQL Server, SSIS, SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS). SSIS…
TechMbaBi 