Getting Started with SSIS

I am relatively new to the world of SQL Server Integration Services (SSIS). I knew sort of what it was and that it existed, but I didn’t start working with it and learning how to use it until about 3 years ago.

I started by following through the Stairway to Integration Services. This is a great way to jump start if you are just starting out. This program assumes you know absolutely nothing about SSIS and walks you through from beginner to a solid intermediate.

It was shortly after completing the Stairway that I began my first consulting job and was heavily working with SSIS. At this job, I was designing and maintaining packages that would grab FTP files, put the information from the files into a table, and push that information to production. Sometimes, they would also grab data from the database, place it in a flat file, and send that file via FTP or SFTP to a client.

This job was also a great way to start with SSIS. I wasn’t doing anything too complicated, and if I ever hit a snag, a quick google search would most likely bring me to a resolution.

It was also in this job, that I met my first “monolith” package. This was a package that was running for over 4 hours at a time and then timing out. The job was to move data from a MSSQL data source into a MySQL data source housed in AWS. My Dad and I worked together on this package and we were able to break it down into smaller chunks – 1 controller package, and a package for each step that needed to run. When we were finished, the whole project ran successfully in less than 18 minutes!

I recently spent some time talking to my Dad about my experience in getting started with SSIS. If you’d like to listen to it, you can find it here.

Encrypting Files in SSIS

Recently I was working with a client who wanted to pull some information from their database, insert it into an excel file, encrypt that file, and send it to the client via FTP. They wanted an automated process that could run on a schedule, so I wrote an SQL Server Integration Services (SSIS) project to do this.

I created this package and it was running perfectly from BIDS and from the SSIS catalog. I scheduled the job and let it run. A few months later, the client came back and told me that the job had stopped running successfully. I logged in and reran the job from BIDS – that worked. When I tried to run the job from the catalog I ran into this error:

Encrypt the file: Error: In executing “C:\gpg2\gpg2.exe” “–trust -model always –output *Destination File* -r *Encryption Key Name* –encrypt *Source File*” at “C:\gpg2”, The process exit code was “2” while the expected was “0”.

*actual file names/key name removed

I tested the package again within BIDS, and again it ran without a problem. I tried redeploying it and testing again. Again it failed. So, I googled it. I tried a few suggestions: running from a command line instead of the catalog; I changed from using variables for file names to hard-coding them; and I thought about changing the success value to 2. I didn’t do this last part because it wasn’t actually doing what I wanted it to do – encrypting the file. And also because that’s just a bad idea in general.

And then, I found a reply to this post that mentioned something about permissions. I asked the client if anything had changed recently with permissions and found they had recently removed some old accounts to firm up security – that’s a great thing! However, it means that I needed to check the permissions of the SQLServerAgent on the machine.

After checking, (and working with Dad) I found that SQLServerAgent did not have any permissions to the gpg2 folder in the file system. I added read/write permissions to the directory and ran the job again. It failed again. I then updated the permissions on the folder to “Full”. This allowed the job to complete successfully!

Moral of the story: locking down permissions is great and should be done – however, keep in mind the ripple affects. If you’re getting a similar error from a job in SSIS, check the permissions for SQLServerAgent.

Hello world!

The very first thing you learn when you are learning a new computer language is how to write “Hello world!” to the screen/output desired. True to that standard, here is my “Hello world!” blog post.

I’ve been working with computers all of my life. I built my first server at the age of 13 with nothing but a box of parts and a book. I taught myself Visual Basic in the same manner. I continued my studies at Liberty University’s computer science program where I was first introduced to relational databases. I happen to love databases and data for that matter. Today, I work as a consultant and do work with Microsoft SQL Server Integration Services, Microsoft SQL Server, MySQL, and anything else that happens to come up. I love learning about new technologies. I currently work with my dad, Andy Leonard, at Enterprise Data and Analytics.

As for my personal life, I am a wife to Antony Trupe. We’ve been married since July 26, 2006. We have three awesome children: Alex, Gabe, and Ellie. We homeschool all three of them for now. I am active in our homeschool co-op as a teacher and the current webmaster for the group. In addition to our kids, we also have two cats (Jack and Ginger), three goats (Rosabell, and her two little boys), a collection of chickens, and thousands of bees. I enjoy baking and making novelty cakes. I also dabble in sewing a little bit. I am an avid coffee drinker as well – my favorite right now is either Joe Bean’s (a local drive thru coffee shop) or Death Wish Coffee.

I’m hoping to use this blog to help spread any knowledge that I have acquired throughout the years, whether it be about computing, baking, animal husbandry, homeschooling, or homesteading. Thank you for visiting!