Clean data by using the SSIS DQS task

-
SQL Server Integration Services (SSIS) includes a DQS Transformation to correct data from a connected data source. The DQS Transformation Output column is populated with one of five status as listed below. You can use a Conditional Split Transformation to separate out the output records based on its status. You can apply different business logic to each stream or insert them to a table of file destination for manual processing.

To clean data using the SSIS DQS task follow these steps:
  • Open a new SSIS package in Visual Studio
  • Create a new Data Flow Task
  • Add a Source Component to extract the data to be cleansed
  • Add a DQS Cleansing transformation and connect it to the Source Component Output
  • Edit the DQS Cleansing transformation and set the Data Quality connection manager and Data Quality Knowledge Base to use for the cleansing activity
  • Click on the Mapping tab and map the Input Columns to the corresponding Knowledge Base Domain, then click OK
  • Add a Conditional Split transformation and connect it to the DQs Cleansing Output
  • Edit the Conditional Split transformation and add the following five outputs as shown








Add a destination component to each of the outputs from the Conditional Split transformation.

Comments

Post a Comment

Popular Posts

Enable logging for package execution in SSIS

-
Image
Logging can be enabled and used at design time and runtime. To begin logging, in the Visual  Studio menu select SSIS > Logging to open the Configure SSIS Logs window. In  the Containers section, select the Control Flow objects for logging—the overall package itself  and/or each of the individual tasks within the package. Then in the Providers and Logs tab,  under the Add A New Log > Provider Type, select where you want the logging to be saved to,  and click the Add button: After adding each of the Provider Types selected you want to log to, go to the Select the  Logs to Use for the Container section to set up the Connection in the Configuration column.  In the case of a Text file, select a file to write to. For SQL Server DB as the target,  simply select the target database. You are not limited to selecting only one target location for  the logging, multiple destinations can be added and they will all be wri...
Read more

Create an SSIS catalog

-
Image
SQL Server 2012 introduced a new paradigm in storing and deploying SSIS packages. Before  that release, all packages were individually managed and deployed. The SSIS DB catalog and SSISDB database serves as a centralized place where deployed SSIS  packages can be stored along with everything related to it, including: Project and package parameters Configuring runtime values for packages Executing and troubleshooting packages Managing Integration Services server operations You find the SSISDB catalog in the folder labeled Integration Services Catalogs. This is where  you find the Projects folder containing each project with all its packages. And the Environments  folder contains the package configuration and environment values, which can be set  before executing the packages. Each SQL Server instance gets only one SSISDB. You also find another SSISDB in the Databases folder in SQL Server (Figure 2-113)....
Read more

Implement slowly changing dimension with SSIS

-
Image
To implement Slowly Chaning Dimensions using the SSIS  SCD components, followe these steps. 1. In your Control Tab, add a Data Flow task 2. Edit the Data Flow task and add an OLE DB Source 3. Double-click the OLE DB Source and connect to your source table, either in the OLTP     environment, or a staging table within the DW 4. Add a Slowly Changing Transform and connect it to the precedence constraint from the     OLDE DB Source 5. Next, Double-click the Slowly Changing Dimension transform to start the Slowly Changing  Dimension Wizard, and click Next 6. On the Select A Dimension Table And Keys window, select Connection Manager to              connect  to the destination DW 7. Select Table Or View for the destination table 8. Map the Input Columns to the Dimension Columns 9. Make sure that only one column is marked as “Business Key”, which is the key from the ...
Read more