Creating a new Excel spreadsheet at runtime and writing data into it. 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…
Executing SSIS Packages with a stand-alone application. This article uses the same package created in the Table To Text File article. Using Start/Run, I will execute DTExecUI.exe which allows for graphic execution of an SSIS package. Next, I configure the utility to execution a file-system package and enter its path. Then, I configure the connection…
Transforming data into new fields. This example will demonstrate the three primary components of a data flow: data source, data pipeline, and data destination. First, drag an OLE DB source unto the designer. Configure the data source by configuring a new connection manager and the correct database and table. For this example, I will only…
Using DataViews to debug data entering the pipeline. I’ll begin this article using the same project to SQLServer to DataReader, export data from a table into a DataReader. When this tutorial is complete your Package should look identical to the screen shot above, with two DataView icons next to each connecting green lines between the…
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…
TechMbaBi 