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

Post a Comment

Popular posts from this blog

"Bigness" data - some thoughts about the big data journey

The " Bigness " data. Ideas of Big Data If you are reading this blog, you are likely to be a nerd. Everywhere - Yes, data is everywhere, and the volumes are exponentially increasing by day. There exists a saying: "Where there is a will there is relatives". Are your measures trustworthy? Ensure their integrity, but at the same time give attention to the training needed to its users. Data Detectives - The cool part for me of data science, is the detecting fraud part. Fraudsters love lots and lots of data. The more the merrier. Its also easier to hide abnormalities when lots of data are present. Therefore, if you find your organization at the stage where you are IYO consuming and processing big data, then its time you consider employing a data detective. Machine Learning - This will only be useful if you actually start using what your system is learning about trends, etc OODA Loop - What came first? The egg or the chicken. This scenario gives ri
Read more

Code Reviews

Code Review Checklist Code reviews are a very important part of the development process, in any environment. It is another safety net in ensuring a software deliverable of high quality. These are some points to keep in mind about code reviews. Take note of what needs to happen before, during and after a code review: Make sure that your code is self-explanatory and has adequate amount of code documentation and comments. These comments needs to describe the problem it is addressing in a clear and concise manner. Automated tests are good to test chunks of functionality. Include automated tests with your changes to make it easier to validate that your code works. In a SQL heavy environment, calling the different SP with specific input parameter values and inspecting the return result, or db stored result is a good midway point in testing the code. Your code should be sitting in source control. Before a code review commences: Set a date and time for the review to commence, and sen
Read more

Moving Data [Apache Spark]

So I decided I'm going to use a real world example and do some transformations against such. I decided on http://dumps.wikimedia.org , so that I have some nice sized files which I'm able to really see the advantages Apache Spark brings to the table. My system is loaded with Apache Spark 1.6.0 and Scala 2.10.5. Lets do this : First open the spark shell: spark-shell Next load an SQLContext: val sqlContext = new org.apache.spark.sql.SQLContext(sc) //sc an existing Spark context  Next import the following packages into the shell session: import sqlContext.implicits._ import org.apache.spark.sql._ Now, you can start by loading the data from the " pagecounts-2011222 " file into a Resilient Distributed Dataset (RDD). RDDs have transformations and actions; the first() action returns the first element in the RDD  Now load the data from the file into a new RDD: val wikiHits = sc.textFile("/home/osboxes/Downloads/pagecounts-20151222") Do some
Read more