Last Thursday I presented a webinar on SSIS Best Practices and how BI xPress can analyze SSIS packages to ensure those practices are being followed. I got a lot of great questions, but couldn?t answer them due to time constraints. So, here are the questions and their answers. Thanks to everyone that attended!
Q: Why don't you put the "derived column" in the select statement and order on it there?
A: Yes, whatever the derived column is doing, it would be better to include that in a TSQL command in the source. Follow that up with an ORDER BY to get rid of the sort transform.
Q: Is Fast Parse available in SSIS 2008 and 2005?
A: Yes. You will find the fast parse option in both 2005 all the way up to 2012.
Q: Do you add anything to check source and destination row counts match?
A: You would want to at least have those row counts logged somewhere so that you can manually compare them, however, adding an Execute SQL task to the end of a package to do this for you is faster and probably a better idea depending on the situation. You could expand on this idea by creating an OnPostExecute event handler to compare those values and then send an email.
Q: Why we should use BI xPres?
A: I love that question! Mainly because it does hours worth of work for you in a matter of seconds. Get a list of the features on the product page.
Q: Does Table Lock affect load performance?
A: When inserting, no. If you are trying to update records while inserting new ones, then yes. The two different operations will interfere with each other.
Q: With Fast Parse having some columns with the option to true and some to not true change performance or will mixed options have the same performance as having it set to false for all columns?
A: For the best performance, you will want to set them all to true. Leaving some false and setting others to true will not yield the best performance.
Q: In the Analysis, fast Parse on applicable output columns - why don't it list those applicable columns? It could make dev life easy.
A: Excellent suggestion! I will pass that along and see if that can be incorporated.
Q: Can we get a list of best practices for SSIS package?
A: I will include that at the end of the post. Also, keep in mind, the best practices analyzer in BI xPress displays this list as well.
Q: So the fast parse error message does not tell you what column to look at?
A: It does. Easy to miss since its close to the middle of the message in the first paragraph, but its there. Check the screen shot below.
Q: Hi Shawn, Can we get the list of the best practices implemented in BixPress or the list of Best practices BixPress validates?
A: Yes. That list is displayed at the bottom of this post.
Q: Doesn't using ORDER BY on the SQL source slow down the select statement?
A: It will slow down the query, but it?s still much faster than using the sort transform.
Q: Missed the derived data in SSIS, but if the column could be derived in TSQL directly, would "nasty order by on calculated value" on server still optimize better than managing transform in SSIS ?
A: Absolutely! Try to avoid the sort transform whenever possible.
Q: Why is pushing the UNION and the SORT down into the database engine better than doing it in the package?
A: Speed is the main reason. The majority of (if not all) data flow components function slower than TSQL queries. Especially the sort transform. Sort is a fully blocking transform, so it will deal a huge performance hit to your SSIS package.
Q: Is the recording will be available online ?
A: Yes. You can view it on our webinar page.
And, as promised, the list of best practices that BI xPress checks for?
Also, keep in mind that in an upcoming release, the use will be able to add their own best practices to this list!