The Sixth Mission
In this installment of SQLCoOp, we are writing about SSIS. For me, this happens to tie closely with my the June SQLCoOp post I wrote, called Data Models, SQL Server, SQLite, and PowerShell. In that post I walked through how to create a data model in ER/Studio Data Architect and have it end up in a SQLite database. In this post I’ll show how to use SSIS to get master data from a SQL Server database and insert it into a newly created SQLite database that can then be deployed with client applications.
The Project
I needed to create a consistent way of pre-populating an empty SQLite database to be used in new builds of our application, so I turned my development SSIS package that moved test data from SQL Server into the SQLite database into a production SSIS package that pre-populated master data into a SQLite database.
Before you get started trying this example out, make sure that you have an ODBC Driver for the flavor of SQLite that you are using installed on your computer. Here is a great resource for ODBC Drivers. After the installation is finished, setup an ODBC connection to the SQLite database that you’ll be populating.
Step One
The first step I did, was run a DELETE statement for each of the tables that I’ll be populating. This allowed me to not worry about the destination database being populated from last time I ran the package. I used an Execute SQL task for each table. I set the Connection property to my ODBC connection I created and I set the SQLStatement property to the corresponding DELETE Statement.
Step Two
For the next step I created a Data Flow task for each table. Inside each Data Flow task I created an ADO.Net Source and ADO.Net Destination tasks.
For each of the tables I wanted to populate, I created a unique stored procedure in a utility database called SSISMigration. This allowed me to customize the needs of each of the stored procedures without worrying about the needs of the applications using similar stored procedures in the source database.
I set the ADO.Net Source connection to the SSISMigration database. I set the Data Access Mode to “SQL Command” and the SQL Command text to an execute statement such as “EXEC MSSL. GetAddressType”.
| Side Note: If you want to pass a parameter to the stored procedure in a generic form, then check out the Expressions property of the parent Data Flow task. |
For the ADO.Net Destination task, set the Connection Manager property and select the table that you would like to have populated. Then click on the Mappings menu item to verify that the columns are mapped correctly between the Source and Destination tables.
Step Three
Now I can connect each of my Execute SQL Tasks with their corresponding Data Flow tasks. This will ensure that the delete process occurs before the insert process.
Step Four
The first time I built this process, I had all the statements connected in one big line. This made the processing an asynchronous process. I then learned that I could have multiple processes happening at the same time. But what if I have one last process that needs to occur after all other processes are finished? This is where the awesome Sequence Container comes into play. By placing all my Delete/Insert processes into the Sequence container, I can have them run synchronously, and still have a final process occur after they have all completed.
Step Five
Finally, I need to clean up after myself. Since I did some deleting and inserting, I want to make sure that my brand new SQLite database is as small as possible. SQLite has a command called VACUUM. This command rebuilds the database to remove any fragmentation caused by CUD operations. To run the VACUUM command, I use another Execute SQL Task. I set it up like I did in step one, with the SQL Statement set to “VACUUM;”.
Now I have three processes that occur synchronously and one process that happens after the first three are completed.
Step 6
Run the package. If I’m only making this “new” master copy of the database for development, then I might just run the package from Visual Studio on an as needed basis. If I want this to be an automated process, then I can set the package up on SQL Server to run on a schedule.
But Wait, There’s More
My example here was for populating a new SQLite database with master data from SQL Server. I could use this same process for creating a subset of data for testing needs or for creating new SQL Server databases that need to be deployed in new environments.
Don’t Stop Yet
Don’t forget to check out these blog posts by the rest of the SQL CoOp team on the subject of SSIS:
- Chris Yates: ISPACs – Got To Love Em
- Julie Koesmarno: On sabbatical through the fall. Please her website at mssqlgirl.com.
- Jeffrey Verheul: SSIS: Zipping files with WinRAR
To follow our quest for SQL knowledge through this collaborative project, follow the #SQLCoOp tag on Twitter.
See you next time!!
On a SQL Collaboration Quest
Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.
Original Post: On a SQL Collaboration Quest
