Best way to learn is to do it… (SSIS)

So I’ve been working on a bunch of projects that are allowing me to get my hands dirty with SSIS, and it’s been awesome.  I’ve learned so much in such a short period of time – well, re-learned some, learned better ways of things.

The most important thing I learned:

  1. If you have SQL2012 server and you’re developing on VS2013, STOP!  I had to recreate the process in SSDT2012 in order to deploy and run it on my production SQL box.  Has to do with versioning, etc.  Save yourself a bunch of worry – find out which version of SQL you’re using, and make sure you can develop in the right environment.

Other Important Stuff:

  1. Send Mail tasks won’t work unless you have things configured properly.  I was trying to find things on google and all I kept coming across was how to configure the task or how to Install SSIS and configure it to run, not how to configure the server to send it properly.  Thankfully John took pity on me and helped me realize that using an execute SQL task and sp_send_dbmail works more easily and cleanly – Sql Server Central
    • To that end, you need to make sure you have a profile to send from, so create one under Database Mail on  your sql box if you haven’t got one already.  I created one called SSISJobs.2016-06-07 15_10_34-Database Mail Configuration Wizard - 192.168.12.163
      I tried just creating entries into sysmail_account and sysmail_profile – no dice.  Use the wizard, it worked like a charm.
    • Run this after in your query browser to make sure it’ll work:
      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = ‘SSISJobs’,
      @recipients = ‘test@test.com,
      @body = ‘The stored procedure finished successfully.’,
      @subject = ‘SSIS Job Message’ ;
    • The [msdb].[dbo].[sysmail_log] table is a handy table to tell you if your message is having an issue
      • 1) Exception Information ===============
      • or Not
      • DatabaseMail process is started
        DatabaseMail process is shutting down
  2. Make sure the applications you’ll need are installed on the box you are running things from, or you have a way to access them.  In my case, I’m using Putty to access SFTP servers to download and move files.  I’ll put that whole process in another post.
  3. DTSX is really all just XML 🙂
  4. Once SSIS is installed (which it already was), all you have to do is right click on Integration Services Catalogs and create one.  Open that and create folder.  Then deploy from SSDT by right clicking the project and hitting deploy.  You have to configure it to hit the right SQL Db, but more details on that later too.
  5. I’ll add more to this list, or create a new list when I get a chance.

I know there are a lot of things I’m not taking advantage of such as deployment and configuration stuff in 2012, but I just want to get my first package up and running.