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.

Young Moms in Technology

I hosted a webinar with my Dad called Young Moms in Technology. We touched on the types of discrimination women (and especially moms) face in the field of technology (really, it’s applicable to any field, but my experience is in technology). We also talked a bit about my experience as a young person in technology.

Unfortunately, I’ve experienced discrimination for being a woman and a mom first hand. I’ve had co-workers not believe me when I tell them I’ve solved a problem. I’ve had my work scrutinized closely. I’ve been passed over for promotions for which I was the more qualified candidate. I’ve been paid less than male counterparts who had less experience and education than I did at the time.

These situations were not ok. And I’ll admit that I didn’t stand up for myself and demand justice (really, though, I was raised as a polite, southern girl – I rarely demand anything. I would have asked politely instead). I do think that you should ask for justice. I believe that the more awareness we bring to the discrimination, the more opportunities we create to fix the problem.

Another point we touched on in the webinar is about what it’s like to be young in a field where experience is everything. From my experience, it’s not easy. Employers, clients, and companies want people who know what they are doing, and young people (millennials) have a stigma for being inexperienced. This is not to say that every young person you meet/interview will be qualified, but don’t be so quick to judge on appearance of youth alone.

I do appear much younger than I actually am, and it can be frustrating to get people to take me seriously sometimes. Even if I work hard, I have had to continuously “prove myself” to a client just to be seen for the talent and knowledge that I have.

I guess my moral of the story is: give everyone a fair chance. Being a mom, a woman, a millennial is just a part of my life; labels that I own. I also possess the labels Consultant, SSIS developer, hard worker, and dependable.

Do you need help with a project? Hire me!

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.