Microsoft SQL Server and SSIS cheat sheet

Microsoft SQL Server and SSIS cheat sheet

The below some short guidelines when regularly working with SSIS and MS SQL Server. Some of these can also be used for some knowledge reviews. Are you using:

  1. BULK INSERT
    • Remember to ensure your text files are delimited with LF (line feed) only, and not CR (carriage return) characters. Open the file in Notepad++ to verify this. Alternately, do a search using Extended mode for "\r\n", and replace these with "\n"
  2. MERGE
    • Very handy compared to separate INSERT / UPDATE statements. Using MERGE also means a combination of INSERT / UPDATE are atomic - a big plus. The gains far outweigh the cons regarding performance, which in my experience has been negligible
  3.  XACT_ABORT
    •  A session variable that controls how processing should continue after an exception in your Stored Procedure is thrown. With this setting on, your entire batch is aborted, and processing jumps to the catch block from your try catch logic. With this setting OFF, MS SQL Server will attempt to continue processing after the line where the exception was thrown.
  4. DECLARE CURSOR
    • Take note of the below catch block of code. This piece of code is invaluable when your stored procedure fails midway to completion, and your cursor has been declared already. If you happen to run the stored procedure again following the failure, the DBMS might complain about the cursor already being declared, hence rendering you unable to execute the procedure until the previous cursor has been deallocated and killed. In the below snippet, the CURSOR was created as a GLOBAL CURSOR. See the first part of the catch clause to handle this typical problem:

      BEGIN CATCH

          /** Close and deallocate cursor if it still exists **/
          IF (SELECT CURSOR_STATUS('global','db_cursor')) >= -1
          BEGIN
              IF (SELECT CURSOR_STATUS('global','db_cursor')) > -1
                  CLOSE db_cursor
              DEALLOCATE db_cursor
          END


          SET @StatementDesc = ISNULL(@ProcedureContext + '  ', '') + ISNULL('Error occured at: ' + @StatementDesc, '')
          EXEC dbo.Generate_TSQL_RaiseError
              @ErrorProcID = @@PROCID,
              @AdditionalErrorMessage = @StatementDesc

      END CATCH

Happy coding peeps.
www.silvafox.co.za
 

Comments

Popular posts from this blog

Moving Data [Spark Streaming - simple data files]

Notepad++ Regular expressions

RSS feeds to your webpage [part 1]