Okay okay, maybe DTS left a bad taste in my mouth or maybe it was because I’ve developed over 256 DTS packages and I still twitch at night. Or maybe I used the first edition of SSIS and while it was better than DTS I still wasn’t a fan; then one of my old colleagues touched base with me and told me that I needed to give the new SSIS version a shot.  He was curious about what I thought, I reluctantly held him at bay for a while but then an opportunity presented itself one day last week. A current colleague approached me with an issue where he had some .txt files that contained SQL code in them that he was running manually.

The first thing that popped in my head was giving SSIS another shot, I initially said no way not going there but figured ah what the heck – lets give it a try. Now the task at hand was quite simple really and by no means did I over complicate anything. I just wanted to take the files and load them to help my buddy out. Took me all of 8 minutes to whip a quick package up. Below is a representation of how I accomplished this; I will try to elaborate step by step.

I’m assuming that one already knows how to open SSIS in Studio…….

Step 1: Connection Managers – will need 3 of them

1. Connection Manager (new database connection) will house the server name and database on the server
2. SMTP Connection Manager – this pertains to the email functionality and the SMTP server
3. File Connection Manager – empty connection for existing file that will be used in a later step

Step 2: From the toolbox I selected my “For Each Loop Container” this will allow me to loop through “all” files that I’m looking for in my specified directory. Double click the Container and or go to the properties and you will be presented with the below window. For my testing purposes I created a test folder called Test1 on my local drive. The files that I need to loop through are .txt files; in my example I am going to load all .txt files within the directory.

Step 3: From the tool box drag the Execute SQL Task over into the For Each Loop Container.

Double click on the Execute task to go to the properties section. The connection will need to be the connection from the Connection Manager that was setup in Step 1. Next the FileConnection will need to be the SQL connection set up in the Connection Manager that was established also in Step 1.

Step 4: I always set up a SMTP Connection Manager so I can utilize the send mail task from the tool box. This allows for me to communicate on failure or success to the appropriate parties. Simply drag two send mail tasks from the tool box and in the properties section add your SMTP server and how you want to connect. Click on the for each loop container and drag the arrow to your send mail tasks; to change for failure right click the arrow and select on failure.

Pretty simple in a nut shell. I felt the tools were very easy to use and while SSIS does a lot more powerful operations this solution provided a way for me to loop through my files in the designated directory, load them in without issues, and a method for notification.

Not covered in here but I will touch on is the fact that I liked the ispacs that deployment utility created. I found it much easier than using the manifest etc. All in all I was quite pleased.