ssis

• Export all msdb SSIS packages to files

  This is going to be a post with more pictures than text, but it is something that is useful.  :)  I did not set this package up, it was done before I started working here.  But, it has been asked about, so I am blogging about it.

  When I release changes to the production, I always want a rollback plan (I would assume most people do).  In order to do that for SSIS, I use a package to export all of the SSIS packages to files. Then, save those in a zip folder.  That way, I can get to them quickly as needed.  Here is the package info for those who can also use it. I will say that this only looks at 1 of the folders within the msdb structure - the main BI ETL folder (which has sub-folders and the SSIS packages in those sub-folders). The way this package works, I would have to have it in each of the SSIS solutions I use. I want to re-do this to make it more generic and re-useable for multiple solutions.

• Oh where, Oh where ... has my column gone?

  Phew - I ran into something I knew would be soooo simple, but couldnt figure it out!  Don't you hate that?  Luckily a co-worker helped plug away at it and wham-o we found it!

  I had a derived column with a little bit of logic, but not much, then added a data viewer after it to see the results.  Then, added another column within the derived column step.  When I went to look at the results in the data viewer - my column was no where to be found!  Ohh my!!

  Turns out, when you create a data viewer it takes the columns it gets, and if you add another upstream, it wont reflect that change.  To change this, you go to the Data Flow Path Editor and click on the "Configure..." button in the bottom right corner (SSIS 2008 R2).  When you bring up the configuration dialog window, you will see a list of columns.  More than likely your new column is in the left hand box - move it to the right and presto! - you can now view that data to your heart's content!  What my co-worker pointed out was that you can also remove the extraneous columns you dont care to look at when you are using the data viewer.

  Hope this helps someone find that missing column! :)

• SSIS - Stored Procedures with Return Values

  I am finally making a blog post about this. Everytime I go to use this technique, I always forget the syntax and have to scout around to find it.

  The scenario: I want to use an "Execute SQL Task" transformation within SSIS to execute a stored procedure, using an OLE DB connection. Then, I want to capture the return value. My problem is, what is the syntax within the Parmeter Mapping configuration screen? I seem to always forget what to type in as the parameter name. :)

  I start out providing the needed information about which stored procedure to execute, using which connection. I am adding in a screenshot below, but generally, the syntax for what I need is exec ? = usp_storedprocedurenamehere. (See screenshot 1). Then, when I go to the Parameter Mapping screen, I select the variable I am capturing the integer into, change the direction to be ReturnValue, and leave the default Data Type as LONG. Here is where I get stumped - The ParameterName needs to be 0 (that is a zero). (See screenshot 2) For Input/Ouput parameters, you can specify the numerical sequence of the parameter, or the parameter name. I will show that in another blog post.

  Well, this is a short and sweet post - mostly to give me somewhere I can look when I forget, yet again. :)

  Talk to you later!

• SSIS connecting to Oracle

  Ohh boy - this is a biggie for me right now (actually it has been for a couple of weeks, but I have successfully put it off until today). :)

  I have a situation where there are already SSIS packages, and sql statements that pull from an Oracle database into our SQL Server database.  The person who set them up left the company (a positive for him).  Therefore, I was scrambling when I started getting all these error messages that were basically laughing at me!

  These are the steps I have had to do:

• SSIS: ProTips - Performance Tuning

  SQL Saturday - Aug 4, 2012 - Baton Rouge

  Instructor: Tim Costello (Interwork - Dallas)

  SSIS: ProTips - Performance Tuning

  1. Benchmark before you begin
  • Performance WireTap script task component written by Todd McDermid
  • output window show start and end row - to show the execution time
  • lightweight (even if left in when running in production, it is lightweight enough not to hurt overall performance)
  2. Its All About the Buffers
  • properties
  • recommend keep defaults (start with low hanging fruit first)
  • dont mess with buffers first - check other stuff for possibilities
  • better to have small # of large buffers, than large # of small buffers
  • Synchronous tranformations - VERY fast (same buffer from beginning to the end)
  • Asynchronous transformations (creates new buffer)
  3. Optimize your souces
  • remove unneeded columns
  • if pulling from a database, use the SQL command (your own query) rather than the table name in the drop down - there is a BIG benefit to this
  4. Better tool for the job?
  • is SSIS the right tool for what you need?
  • if going from a database to a database - do it in the database, not SSIS

  Other things discussed: 3 types of data flow components:

  • Full-blocking transformations
  • Semi-blocking transformations
  • Non-blocking transformations
• Transformations Not Visible When You Open A Package

  This isnt a "problem", its just really annoying. :) When I open some of my SSIS packages (2008 R2), I have to scroll to the left and up just to see my transformations. I put up with it for a long time, but when I got to work today (on a Monday), I was already tired and a bit upset. Then, I said "enough is enough!" and promptly tried to fix this. What I noticed for each of the packages that were like this, was the text was smaller, almost like the zoom was different than on other packages.

  I looked and looked for a zoom in the menu options - but I didnt see anything. So, last but not least, I right clicked in the Control Flow open space (there are several things I access from there) and Voila! Here are the steps for fixing this:

  1. Right-click in the Control Flow open space
  2. Choose Zoom... 100 %
  3. Make a small change to a transformation (just move it a little)
  4. Save and close the package
  5. Open the package -a nd again... voila!

  Its crazy how easy that was and how long I put up with it. :) Hope this makes someone's day, like mine.

  For those of you who are much more of a code junkie and dont want to use the GUI, you can use the "view code" and change the "PersistedZoom" value to 100. You will also have to open the designer and move a package, then save and close in order for the change to be seen.

• Word Wrap in Annotations

  Well - one would think that word wrap would be something that isnt too difficult, but when I started typing in an annotation today (look at me - commenting!!), it didnt wrap my text.  I tried enter, to no avail.  If you are stuck - hit Ctrl-Enter and that will wrap your text for you.  Simple enough, but it took me a little bit to find it.  Hope it helps someone! :)