Move, Enhance, or Import Company Data Using SSIS
While working at SalesPad, I’ve witnessed multiple businesses encounter a situation that made it necessary to transfer and/or modify company data.
The reasons for such a situation may vary greatly, but one of the optimal tools to solve this issue is an SSIS (SQL Server Integration Services) package.
A few example situations where an SSIS package has proven exceptional are as follows:
- A business currently uses software that they are unsatisfied with and wishes to migrate to a new software solution without losing any of their existing data.
- A business wants to import sets of new data into their system. The data they would like to import is currently in a source supported by SSIS (i.e. Excel, XML, ODBC). For a complete list of data sources that are supported by SSIS, please click here.
- A business is splitting into two separate divisions and would like to maintain each division’s data on individual databases.
For the first example, a large combination of different SSIS packages can break the process down to incremental steps.
Each type of data entity in the current system (customers, for example) may need to be completely transformed to the specifications of the new system. The ability to execute completely custom T-SQL (Transact-SQL) queries alongside boxed-tools in SSIS makes any necessary transformation possible.
Because an SSIS package can process multiple SSIS packages simultaneously, a tiered, well-structured, and efficient process can be created to transform and move an entire company database in a matter of minutes. This allows the process to continue, even if one step fails. Below is an example of a result-driven flow control in an SSIS package. Green arrows indicate success of the above step.
An SSIS package can be used to accomplish the second example with a few additional features as well.
Using the provided tools in SSIS, the company can:
- Run the import on an automatic schedule.
- Notify any interested parties of the success or failure of the process.
- Perform a lookup on existing records to determine if any imported records are duplicates and handle them appropriately. If a customer record already exists, perhaps that record should be updated with the new information.
- Execute stored procedures to properly import the new records into the target system.
- Perform validation on the new data using custom criteria to exclude any invalid entries, along with the option to correct them.
Again, for large data operations, SSIS can be highly desirable. By using Data Flow, Bulk Insert, Expression and other available boxed tools, records specific to each company can be identified and moved to the target location without extraneous development or time. In most situations, the large amount of provided tools and operations make SSIS a more money and time efficient solution than performing the same steps through just scripts.
SSIS packages can be easily created with programs such as Microsoft Visual Studio, Microsoft Business Intelligence Development Studio (BIDS), or programmatically (click here to to learn more about the required tools for SSIS creation). This allows for simple drag and drop package creation, but with quick access to much deeper customization for the experienced software developer.
It is important to note that if developing SSIS packages using Visual Studio, it is recommended that the version of Visual Studio match the version of the Microsoft SQL Serverthat the package will be executed on.
While it is easy to create a variety of basic but useful SSIS packages, an experienced mind can leverage its functionalities to fulfill complex needs on large sources of data. This balance between depth and ease makes SSIS useful in quite a range of settings. Consider it as a tool to accomplish your company’s next data-oriented need.