Have you ever had an integration or migration project that required a certain pre-retrieval or post-loading step on your data source or target? Maybe you need to perform a few operations within the same database session or just before writing records to a database? Such a scenario would be extremely challenging with just the out-of-box SSIS components.
Nevertheless, there is an easier way to perform these actions thanks to KingswaySoft’s Premium ADO.NET components found in the SSIS Productivity Pack. The SSIS Productivity Pack is a collection of advanced SSIS components to enable greater developer productivity. In this blog post, we demonstrate how to use the Pre & Post Commands feature in the Premium ADO.NET components to run SQL statements or set session variables before or after executing our components. With the latest 20.2 release of our SSIS Productivity Pack, this feature is available in both the Premium ADO.NET Source and Destination components.
Challenges of Using Out-of-the-Box Components
To confirm the benefits of the Pre & Post command features, let’s first take a look at how out-of-the-box components can be used for this purpose. One approach would be to use an Execute SQL Task to run any SQL statements on the database before running the actual data flow task which loads your data to the target system. With this approach, you can prepare your environment before the actual data movement. The same method can also be used to execute any SQL statements after the data load by using an Execute SQL Task after your data flow task. Using the Execute SQL approach, the Control Flow design will look something like this:
The drawbacks of such an approach are many. First, it can complicate our control flow design especially if we have multiple data flow tasks that we need to perform pre and post preparation tasks. Additionally, we require two extra Execute SQL Tasks for each data flow task in our SSIS package. Another drawback is that each Execute SQL Task will run the defined SQL statements in its own database session which can be an issue if we have to work with session variables that need to be maintained across all of our tasks.
Similarly, if we aim to execute SQL statements just after the Source component has finished retrieving data or before writing records to a database table within a data flow task, the difficulties will be immense. Choosing to use the out-of-the-box OLE DB Command component for instance, will execute the SQL statements for each input record which could case major performance issues. This is where the Pre & Post Commands feature in our Premium ADO.NET components come in handy.
Using Pre & Post Commands
Let us start with the Premium ADO.NET Source component. With our 20.2 release, the component now supports the Pre & Post Commands page where you can directly define the SQL statements you wish to execute before or after the component retrieves your data. In the following example, we are executing a stored procedure on the database side to prepare for the staging environment before reading the data from our table. Next, we define another SQL statement to run a stored procedure after the data has been completely retrieved by our component. This stored procedure can perform clean-up tasks in our staging environment after the data has been loaded.
Similarly, our Premium ADO.NET Destination component supports the Pre & Post Commands page to execute SQL statements before or after your data has been written to your destination table. Here is an example of loading data to the Account table, for instance, in our database. We will use the Upsert action of our Premium ADO.NET Destination component (if the specified record exists in the Destination Table based on the defined matching criteria, it will be updated, otherwise it will be inserted). We have also enabled Bulk Writing for improved loading performance.
We can use the Pre commands option to run any SQL statements before the data is loaded, and the Post Commands option is used to run any SQL Statements after the data load is complete. These statements will be executed within the same database session.
In this example, we have used the “Pre” Command option to log the current timestamp of the database table when the data load begins. We can also set any session specific variables that might be needed before executing our stored procedure.
For the Post command option, we are logging the current time stamp to a database table to know when the data load has been completed successfully. We can also execute a stored procedure to perform post-integration tasks.
Based on the previous examples, by using the Pre & Post Command feature in Premium ADO.NET Components, we do not need to use an Execute SQL Task, thus simplifying our package design. We can also set session specific parameters by working with this feature which would not be possible otherwise.
Closing Notes
In conclusion, the Pre & Post Commands feature in KingswaySoft Premium ADO.NET components can simplify your package design to execute any SQL statements before or after retrieving or loading your data. It is an essential feature to set session variables within the same database session before reading or writing your data. There are other possible uses that can also benefit from this feature such as executing SQL statements only once after retrieving or loading your data, without which would be challenging to implement otherwise within the same data flow task with just the out-of-the-box components.