Below are answers to questions asked during the recent webinar. If you have additional questions, please contact Andy Leonard at andy.leonard@linchpinpeople.com or Thomas LaRock at thomaslarock@confio.com
Q: What versions of visual studio are compatible with SQL Server 2012 (ex. VS 2008, 2010, 2012)?
A: SQL Server 2012 shipped with VS 2010 for BI development. Microsoft released VS 2012 templates in March 2013. Matt Masson has a good write-up on installing the VS2012 templates for SSIS 2012 development: http://www.mattmasson.com/2013/04/installing-ssis-for-visual-studio-2012/
Q: So the baseline is just the select process?
A: The baseline is really a metadata collection and storage exercise. I usually automate the process and leave reports onsite along with the "real" data integration process.
Q: Are there any fast-load options to define data is pre-sorted to avoid a CX resort?
A: There are really a couple things to consider: Pre-sorting the load and communicating the data's pre-sorted condition to the relation engine. Pre-sorting the load isn't difficult. Communicating the condition to the relational may be difficult. I've seen narrow use cases where re-applying a dropped CX to a table benefits from having the data "in order" when it is loaded.
Q: Does any of this apply to SSIS 2008?
A: Yes! This presentation was based in SSIS 2012. But you can build a custom SSIS framework (or purchase one from third-party vendors) to perform the same functionality in 2008 R2.
Q: Are the features you are showing right now new to 2012 or are they in 2008R2? I haven't seen those menus in BIDS.
A: This presentation was based in SSIS 2012. But you can build a custom SSIS framework (or purchase one from third-party vendors) to perform the same functionality in 2008 R2.
Q: Is the Integration Service Catalog different from Integration Service? We are still on 2008, and I don't see a Catalog anywhere.
A: The Integration Services service in 2005, 2008, and 2008 R2 is mostly a "librarian." It knows where packages are stored in the file system and where they are stored in the msdb database. It manages both in an entity it calls the SSIS Package Store.
Q: Is it possible to split a SSIS package and run it on multiple systems? Will it optimize the package?
A: Yes, but… Beware asynchronous locking and blocking. There is an execution pattern called Work Pile that runs packages asynchronously across an "SSIS Application Server Farm." You have to account for large (or otherwise long-running) jobs - such as end-of-month, -quarter, -year - that may cause otherwise well-behaved package executions to "collide."
Q: Could you provide the location where you can download these reports?
A: http://ssisreportingpack.codeplex.com
Q: Do you find that the first knob you twiddle is the DefaultBufferMaxRows/DefaultBufferSize?
A: In general; I start with the database, move to query tuning, and then to the SSIS Data Flow. Probably 90% of performance issues are resolved before I get to the SSIS package, in my experience.
Q: So if 'smaller' buffer size could improve performance, didn't your results slide indicate the reverse, that the Fast Load was more efficient?
A: I used to have a checklist where I "knew" what to do to make the database and SSIS peform better. As a consultant visiting many different clients, I discovered some "fixes" that worked at one client took another client's server to its knees. I threw away my checklist. I now have a methodology that relies heavily on focused testing to ascertain what works best in the current infrastructure / configuration / server and network load.
Q: Can you run the new tools in SSIS in SQL Server 2012?
A: You can use SSIS 2012 to build data integration solutions for SQL Server 2008 R2 sources and destinations. I caution folks against installing the 2012 workstation tools onto a workstation with 2005, 2008, or 2008 R2 workstation tools if the goal is to develop SSIS 200n packages and SSIS 2012 packages. Some critical DLLs are overwritten by the 2012 workstation installation (the Scripting DLLs, for example) and you lose the ability to develop fully-functional SSIS 200n packages.
Q: In memory database claim real time processing because everything is in memory as a result you don't need indexes, partition, execution plan etc. so the way we do performance tunning may be not anymore the need on performance tunning . Your comment please.
A: Innovation will change performance dynamics. My experience with Microsoft technology is that there is a message from Marketing about performance enhancements and then there's the truth. I recommend a test-driven methodology. There's no substitute for good engineering.
Q: On the topic of naming convention--just curious, does anyone know why Microsoft has it where the name displays on the tasks/transformations, instead of the description. to me, the description should display--that way you can name your objects as you prefer (without worrying about how that name is displayed)
A: I am not sure.
Q: Is there a way to measure performances of SSIS packages or tasks without deploying them? From Visual Studio?
A: You can measure relative performance executing inside SSDT / BIDS. For very light loads, performance inside VS will be close to deployed performance. Loads of scale will often perform much better deployed.
Q: Are all these options and reports available with stand alone dtsx files or only with catalog packages?
A: This is a great question! If you want to use the reports that ship with the SSIS 2012 Catalog, you must deploy the SSIS Project to the Catalog and execute the package within the Catalog. However, you can collect remarkably similar package performance metrics using a custom SSIS Framework. There are third-party solutions available, free solutions (at codeplex.com and other locations online), and Linchpin People offers an SSIS Framework. With a framework, you are not limited by SSIS version. You can collect metrics similar to those collected by the Catalog in 2005, 2008, 2008 R2, and 2012.
Q: Is it possible to give access rights to people to only run the SSIS Catalog reports, but not allow them to do anything else in Intergration Services Catalog?
A: Another great question! Unfortunately, I do not know. I am working on this very issue with a client at present. SSISDB security is… different. I believe the answer is "yes" but I can't tell you how right now because I don't yet know. ![:\](https://thwack.solarwinds.com/resources/emoji/confused.png ":\")
Q: Can you pass a variable from one project to another?
A: Yes. I can accomplish this using Project Parameters and utilizing either the SSIS execution stored procedures, SSIS Catalog Environments, or custom code.
Q: Which SSIS performance Counters would you suggest to monitor?
A: See http://msdn.microsoft.com/en-us/library/ms137622.aspx. Buffers Spooled is #1. Buffer Memory is a close second. You want Buffers Spooled at 0 at all times. If the Data Flows are swapping to disk, that's "bad." (http://www.youtube.com/watch?v=jyaLZHiJJnE)
Q: When I edit SSIS 2012 jobs it opens the 2010 IDE... how is he having it open in the 2012 IDE??
A: Matt Masson has a good write-up on installing the VS2012 templates for SSIS 2012 development:http://www.mattmasson.com/2013/04/installing-ssis-for-visual-studio-2012/
Q: What is the relationship between max commit size and buffer size? Will the change of the value affect performance and sql db logs?
A: Michael Coles did some good testing on this in earlier versions of SSIS and, if memory serves, found that the Max Commit Size property setting doesn't impact the actual number of rows committed by a destination adapter. I wish I could find his work - I believe it was in the comments to a post at SQLServerCentral.com - but I cannot locate it. Michael found the buffer size was the real commit size. I haven't yet tested 2012 to see if that's changed.
Q: Do you know if they fixed the ssis config file runtime issue has been fixed in 2012?
A: I am familiar with one SSIS config file runtime issue in SSIS 2005. The /Config switch on the DtExec command line would load a config file but it would not apply the setting(s) contained within that file, rendering the /Config switch useless. The fix in 2008 was to reload all configurations - those specified on the command line and those specified at design time - after command-line overrides (implemented by the /Set switch) had been evaluated. If this is the Config file runtime issue of which you speak, it has not yet been fixed: SSIS 2012 maintains this behavior. My recommendation to Microsoft was to allow this behavior to continue in the package validation / pre-execute phase of loading a package, but to provide an option to apply /Config-specified configurations after design time configurations had been applied but before command-line overrides (/Set's) were applied. Despite support for this suggestion inside Microsoft, the request was denied. In my opinion, the current behavior violates 40 years of computing convention: The command-line should always win. In SSIS, configurations win - even design time configurations will overwrite command line-specified (/Set) run time values.
Q: I've got a simple 2005 SSIS package that pulls a data set into a CSV file. Production shows it starts the query and ERROR 2-Abort about 5 times before the actual query is run. Is there a way to turn off those extra run-abort "preamble" actions?
A: Maybe. I would start by analyzing the query in the Production environment. To be clear, I am advocating testing in Production. This isn't a good practice. If you have a QA or Test system built on the same hardware as your Production server with the same amount of storage attached so you can suplicate Production conditions, test it there. It's rare to have such a system.
Q: My ssis pkg slowest comes from the validation portion during the execution. Minus all that out, I see that all the tasks execute under 2ms.
A: You can turn off some parts of package validation, but not all. The DelayVaidation property is set to False by default. If you set it to True it will (mostly*) attempt to execute the package or executable (package, container, or task) or connection manager without validating it first. *I write "mostly" because the DelayValidation property does not affect all types of validation. Also, setting the Package DelayValidation property will not turn off validation for executables contained in the package. The executables most impacted by validation are Connection Managers and Data Flows. A good starting place to reduce validation time is to set DelayValidation to True for these and for the Package itself.
Q: In buffer management do you go for query hint option "fast" to attempt to load the first buffer for the dataflow pipeline to process?
A: I have seen the FAST hint do wonders for data flow performance. The trick is identifying that the data flow is waiting on its first buffer of data from the source adapter. That can be tricky, because the data flow could be doing other stuff in pre-execute (populating a lookup cache, for instance). The easiest way to test it is to write a query using the FAST option and drop it into the source adapter.
Q: Is SSIS 2012 any better in cascading data type changes, or do you still have to change it everywhere in the tree?
A: There is a new mapping tool available in the data flow to assist with data type and column changes. It's still painful, just a little less so with the new tool.
Q: Is it true that if we have many packages running in SSIS 2008, we can upgrade our framework to 2012 and not have to worry about upgrading the packages and potetially corrupting them?
A: That's mostly true. SSIS 2012 development defaults to a new Catalog-friendly development and deployment mode called Project Deployment Model. But SSIS 2012 also fully supports 2008 packages via the Package Deployment Model. When you import a 2008 project into 2012, it will walk you through a wizard to import the 2008 project as a Package Deployment Model project. You can then right-click the Project and convert it to a Project Deployment Model project. There's an awful lot of moving parts under the hood of this operation, but I've found the wizards for converting between Package and Project deployment models sound. I think Microsoft deserves applause for supporting the previous editions.
Q: Where do you see SSIS being installed in most high throughput environments? On the Source/destination server or on a stand alone server?
A: This is another question only answered by testing. That said, I prefer to begin with SSIS on the Destination server. Writes are more intense than reads, and writing locally is easier than writing across the pipe. My last choice is the source server because I prefer non-invasive processes - especially if I am invading the system of record (or the system responsible for generating the revenue from which I receive pay). It's a balancing act; one in which the scales tip differently as data scales (see what I did there?).
Q: Have you seen in SSIS 2012, a successfully implementation of using the capability to dynamically create SSIS packages where the package definition is stored in a db (metadata)?
A: Yes. The complexity of maintaining such a solution must be factored into the decision to build it. There are tools that can help. Mist (http://varigence.com/Products/Mist/Capabilities) leaps to mind. Mist provides automation options that mitigate the complexity of such a solution.
