Getting Started with SSIS Productivity Pack: Premium ADO.NET Components

Thank you for watching this video from KingswaySoft. Today, I will be introducing the two ADO.NET Data Flow components offered within the SSIS Productivity Pack. The SSIS Productivity Pack is a collection of premium SSIS components which enable greater developer productivity and increases the power of SSIS. As of this recording, there are two components that work with ADO.NET connections: The Premium ADO.NET Source component and the Premium ADO.NET Destination component. When compared to the out-of-box ADO.NET components, we offer much greater usability, along with some advanced functionalities, such as the support of all kinds of database operations, including Update, Upsert, and Delete actions which are not found in the out-of-box component. We will begin with the Premium ADO.NET source component. Let’s quickly create a package-level ADO.NET Connection Manager. The Premium ADO.NET Source component can be used to read from databases over an ADO.NET Connection. Let’s drag the Premium ADO.NET Source component from the SSIS Toolbox to the design surface. Double click to open its Editor form. Select an ADO.NET Connection Manager. Next, the Data Source drop down will display a list of available tables and views from the database. Once a data source is selected, the Command property will automatically populate, generating a basic select statement for reading from the database, which you can make changes accordingly based on your business requirements. The Import option allows you to load SQL from a file into the Command property. The Export option allows you to save the SQL in the command property to a file. There is also an option for a Preview dialog that shows the result of executing the text in the Command property. This Preview shows up to the first 200 rows. Navigating to the Columns page, you will find a list of the available columns based on the query command provided. By default, all fields are selected. As a best practice, you should only select the ADO.NET fields that are needed for the downstream pipeline components. Let’s go back to the General page to modify our query command. Note that unselecting a field does not mean that the field is not read from the database, but just the values from the field are not sent to downstream pipeline component. Click OK to finish configuring this Premium ADO.NET Source Component. We’ll quickly add in a dummy DataReader as our destination component to receive the input rows from the source component. We can now execute the task successfully. We will now demonstrate the Premium ADO.NET Destination component. As mentioned before, the Premium ADO.NET Destination component facilitates writing to databases. Let’s quickly configure a source component first. I will use the Data Spawner component to quickly generate data for First Name and Last Name. We can now drag the Premium ADO.Net Destination component from the SSIS Toolbox to the design surface and connect the two. Double click it to open its Editor form. Let’s select an ADO.NET Connection Manager. Next, the Destination Table dropdown will display a list of available tables for the database specified in the Connection Manager. The Premium ADO.NET Destination component can write records to a database by using Insert, Update, Upsert or Delete. When selecting the “Insert” action, you have the option to enable the Bulk Insert option to help improve performance. When selecting Update, Upsert or Delete, a new option will become available. If the option is enabled, it will update or delete the first matched records. Otherwise it would update all matching records. Using this option has the same effect as you use TOP 1 clause in your Update or Delete SQL statement. Note that if the database does not support the TOP keyword, the command will fail when the option is selected. When using Upsert, if the specified record exists in the Destination Table, the component will perform an update. If no such record exists, the component will insert a new record. For our demonstration, we will choose the Upsert action. Navigating to the Columns page, we will select the columns to map from upstream components to columns from the specified Destination Table. Since we are using the Upsert action, we will have to pick what columns are to be used as the Upsert keys. As you can notice, it is possible to select a compound key. Let’s head to the Error Handling page, where there are 3 error-handling mechanisms to choose from. The default option is ‘fail on error’, where the entire dataflow will fail as soon as an error occurs. There is also the redirect rows to error output, where the error output will contain the failed records with extra columns for ErrorCode, ErrorColumn and ErrorMessage. There is also a third option, in which you can ignore any errors that may have occurred. Click ‘OK’ to finish configuring this Premium ADO.NET Destination Component. We can now execute the task successfully. This concludes the demonstration of Premium ADO.NET Source and Destination components within our SSIS Productivity Pack. There are many other components in the Productivity Pack that enable developers to accomplish more in SSIS in a much more productive fashion. Thank you for watching this video. Please feel free to take a look at our other videos available for viewing on our website or YouTube channel. For any further assistance, please feel free to contact us.


Leave a Reply

Your email address will not be published. Required fields are marked *