SSIS Talk

Should you come across this error message, “cannot show visual studio 2008 tools for applications”, you may have to clean up your VSTA environment.

You may also have the following additional error message:
The System Cannot Find The File Specified

Here’s how to fix it, thanks to Silviu Guea, Microsoft.

Create a batch file with the following code. Execute it. Ignore any errors. Try again. If it doesn’t work, you may have to contact Microsoft support or try many uninstall/reinstall combinations. For me, this worked the first time I tried it. Finally, the code:

@rem start batch file
@rem delete the VSTA registry cache from the current user
REG DELETE HKCU\Software\Microsoft\VSTA /f
REG DELETE HKCU\Software\Microsoft\VSTAHost\SSIS_ScriptTask /f
REG DELETE HKCU\Software\Microsoft\VSTAHost\SSIS_ScriptComponent /f

@rem delete the VSTA registration cache for SSIS script task and data flow script component
REG DELETE HKLM\Software\Microsoft\VSTAHost\SSIS_ScriptTask /f
REG DELETE HKLM\Software\Microsoft\VSTAHost\SSIS_ScriptComponent /f

@rem delete the cached files from the HDD
rd /s /q "%AppData%\Microsoft\VSTA"
rd /s /q "%AppData%\Microsoft\VSTAHost\SSIS_ScriptTask"
rd /s /q "%AppData%\Microsoft\VSTAHost\SSIS_ScriptComponent"

rd /s /q "%USERPROFILE%\Local Settings\Application Data\VSTA"
rd /s /q "%USERPROFILE%\Local Settings\Application Data\VSTAHost\SSIS_ScriptTask"
rd /s /q "%USERPROFILE%\Local Settings\Application Data\VSTAHost\SSIS_ScriptComponent"

@rem re-generate the VSTA files for SSIS script task and component
"%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\vsta.exe" /hostid SSIS_ScriptTask /setup
"%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\vsta.exe" /hostid SSIS_ScriptComponent /setup

@rem end batch file

Download file here: vsta_cleanup.txt

For those of you that work extensively with sys.dm_exec_query_stats and associated plan cache DMVs, be aware that altering the server’s ‘max degree of parallelism’ setting will flush the query plan cache.

You may see this message in your server’s SQL Log file:
“SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘SQL Plans’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.”

In the case of MAXDOP changing, if you look before the above event happened, you should see that MAXDOP was changed and hence caused the flush.

This makes sense. If you are changing the MAXDOP setting, all current query plans are worthless as they’re out of date with respect to this parameter. Some plans may now be able to benefit from parallelism, whereas before they could not. Or if constraining MAXDOP, some parallel plans may not be able to work anymore. (Or some combination thereof.) The easiest way to rectify this issue is to simply flush the plan cache and let all plans recompile at their next execution.

A common situation for changing MAXDOP could be for maintenance operations, where during normal operations MAXDOP is constrained to 1, yet while doing weekly work during a maintenance window the DBAs may elect to set MAXDOP to 0 (unrestricted).

So, when you’re scratching your head wondering where your plan cache went, it could be due to administrative tasks.

There is a bug where even if you have applied a patch or service pack to SSIS 2008, the version listed next to the “Integration Services” service in Management Studio continues to show 10.0.1600, which is the version number for RTM.

As always, if you are unsure, it is best to determine the actual version by looking at the files themselves, ie. dtexec.exe, or by confirming the version number in BIDS via Help->About.

While this isn’t a big deal, it is an annoyance. I’ve got the question out to the product team for their response, so stay tuned for more information as it becomes available.

EDIT – Please see Matt Masson’s (SSIS Product Team) comments below.

When using a Merge or Merge Join component in a data flow, your incoming data is required to be sorted. While it may be easy to drop a Sort component into your data flow, it may make more sense to perform the sort in your source query (if you are using a source such as an OLE DB Source component).

If you decide to use an ORDER BY in your source and want to tell the SSIS Data Flow that your data is sorted, follow the below steps:

(more…)

For those of you that wish to execute maintenance plans on a server and do not wish to have SSIS installed on said server, you will run into issues if you are on an RTM version of 2008 (version 10.0.1600).

The specific error message is “The SQL Server Execute Package Utility requires Integration Services to be installed by one of these editions of SQL Server 2008: Standard, Enterprise, Developer, or Evaluation. To install Integration Services, run SQL Server Setup and select Integration Services. The package execution failed. The step failed.”

This constraint has been lifted in Service Pack 1 and Cumulative Update 3, each. See: http://support.microsoft.com/kb/961126/

Starting with SQL Server 2005 RTM and through the latest SQL Server 2008 build at the time of this writing, there is a pretty nasty bug with respect to the COUNT(expression) function and its rule that when using an expression, it filters out NULLs in its count results.
(more…)

Fellow MVP Aaron Bertrand has blogged about the MRU (Most Recently Used) server list in SSMS under SQL Server 2008 and how hard (and unsupported) it is to clean it up versus how it was under SQL Server 2005. Please give that a read and head over to Connect to vote for his suggestion as you see fit.

(it’s easy [but still undocumented] under SQL Server 2005 – just delete the mru.dat file…)

Connect item: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=424800

Just a quick tutorial on using a script component as a source… Follow the link for screenshots.

(more…)