Microsoft SQL Server and SSIS cheat sheet
Microsoft SQL Server and SSIS cheat sheetThe 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:
- 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"
- 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
- 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.
- 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:
/** Close and deallocate cursor if it still exists **/
IF (SELECT CURSOR_STATUS('global','db_cursor')) >= -1
IF (SELECT CURSOR_STATUS('global','db_cursor')) > -1
SET @StatementDesc = ISNULL(@ProcedureContext + ' ', '') + ISNULL('Error occured at: ' + @StatementDesc, '')
@ErrorProcID = @@PROCID,
@AdditionalErrorMessage = @StatementDesc
Happy coding peeps.