SSIS Tips and Tricks Q&A

Thank you to everyone who attended my webinar on SSIS Tips and Tricks on October 3, 2013. The link to the recording can be found here. You can also check out all the Pragmatic Works Free Training videos here. I had several people ask about PASS Summit. So here is a link to the Summit page (http://www.sqlpass.org/summit/2013/Home.aspx). It is being held in Charlotte, NC in just a couple of weeks, October 15th to October 18th. There were also quite a few requests for a link to the webinar I did a while back where I talked about setting up loops in SSIS. As it turns out I have two webinars that are ETL overviews. The unfortunate part of that is that something is wrong in our system and they both link to the same recording. That being said the looping example is apparently in the one that is missing. I will try to do a blog post here in the near future that covers the topic. If you don't see if email me or leave a comment and remind me. How do I set the directory where the checkpoint files are saved? In the checkpoint location setting just specify the full file path, for instance, C:CheckpointsMyCheckpointsCheckpointFile.txt Do checkpoints work in loops? Yes and no. A loop won’t prevent a checkpoint from being created but there is no way for the package to start back in the iteration of the loop that failed. Example time! Let’s say we have a for each loop that is looping over 10 files. During the loop it fails on the 4th file. When I restart my package it is going to start back at the beginning of the loop not with the 4th file. So the checkpoint will be created but it will likely not function like you expect. This is just one of the many reasons I don’t use them in production. Do checkpoints work with BI xPress? Checkpoints don’t cause any adverse effects on BI xPress. That being said they could cause problems with your even handlers that are used in our Auditing Framework. Here is why. When we set up checkpoints we set the option for Fail Package on Failure to true. This means that no event handlers will be fired as the package will not kick off any new tasks. Since BI xPress uses native SSIS for logging (event handlers) the errors will not be logged. If you follow my advice of not using checkpoints in production this will not become an issue. Short answer: yes they will work with BI xPress. Long answer: some functionality BI xPress adds may not work properly. The auditing is the only feature I know of that will be affected though. Is one connection manager better for using parameters in queries? The OLEDB connection is probably the most robust in general. You will run into some limitations with ODBC, so if possible I would choose OLEDB. How do you configure checkpoints in a parent-child package scenario? Using checkpoints in a parent-child package scenario is no different. Configure the checkpoints on each package and each task you would like to have the ability to restart from. Can multiple packages write to the same error log table? Absolutely! The fields I chose to put in that table were simply for the sake of showing how to do it. There are several other columns that you would likely want to add to the table also. I would be sure to add Package Name, Package ID (a unique identifier assigned to each package when it is created), Execution ID (this is a unique identifier generated for each run of the package), Package Start Time, Task ID (a unique identifier for the task just in case you have two tasks with the same name), and that’s probably about it. All of those have system variables you can choose in the parameter mapping much like the Source Name value we used. Are checkpoints a good fit for loops, even in development? Checkpoints don’t do well with loops. Personally I don’t enable them on tasks inside a loop in general for this reason. Can we log the package name into the error logging table? Yes, I would encourage you to do this actually. I was just trying to keep things simple in the webinar. There is a variable in the event handler for Package Name. See a couple questions above for some other useful fields to log. Can you use the BI Xpress Best Practice Analyzer to check for naming conventions? This is something that is already on the radar. I actually suggested that quite a while ago, great minds think alike! I hope to see it in the near future. Is Rachel going to sing Miley Cyrus too? A little known fact about Rachel, she is a huge fan of Miley Cyrus. Ask her about it at all future webinars. Context for those who didn't attend. We were talking about the Pragmatic Works Karaoke event that will be at Summit in a couple of weeks. Rachel said she was going to sing and people started making suggestions. Miley Cyrus came up. I’m actually a little embarrassed that “Miley Cyrus” is even on my blog now. My Google ranking just went down I’m sure. Does the Fail package on failure property need to be set on all tasks? Yes, but only on the tasks that you want to use checkpoints with. For instance, you may not want to restart the package from a loop or specific tasks inside the control flow. What are some examples of partially blocking transforms? Here is a list of the transform in SSIS broken out. Non-Blocking Transforms: Audit Character Map Conditional Split Copy Column Data Conversion Derived Column Lookup Multicast Percent Sampling Row Count Script Component Export Column Import Column Slowly Changing Dimension OLE DB Command Partially Blocking Transforms: Data Mining Query Merge Merge Join Pivot Unpivot Term Lookup Union All Fully Blocking Transforms: Aggregate Fuzzy Grouping Fuzzy Lookup Row Sampling Sort Term Extraction How do you handle naming conventions inside of loop? I don’t treat the tasks inside a loop any different. It does get a little more challenging to determine what is going on when looking at logs, but if you log the value of variables as well it helps decipher things. How do I send an email when an error occurs? The On Error event handler isn’t just for logging to a table as we looked at during the webinar. You can actually use any of your control flow tasks right inside the event handlers. Rather than logging the error using an execute SQL task just use a send mail task. Or better yet, do both! There was a package in the solution called Performace.dtsx, did you use that? Negative. I did not use that package in this webinar. How does the lookup transform handle multiple matches? If there are multiple matches only the first match will be returned. The rest of the matches will be ignored. If you look at the SSIS execution results panel there will be a warning that comes up saying multiple keys are cached. If you don’t see that warning then you have nothing to worry about. Where can I download the slide deck? Your wish is my command: Boom! (If that wasn't clear the word “boom” is the download link)

SSIS Tips and Tricks Q&A

Comments

More from this blog

Azure Data Studio is dead...now what?

Working with Fabric shortcuts in PySpark

Gathering useful notebook and environment details at runtime

Using sempy to get SQL query CU cost from the Fabric Capacity Metrics app

List Parameters for a Python Function

Command Palette

Comments

More from this blog