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

I’m reposting a list of task/component prefix naming conventions that Jamie Thomson posted some years back for SSIS. Using a standard set of prefixes allows for easy log reading, better multi-developer support, and ease of readability.

Get the Excel document here: SSIS_Prefix_Naming.xlsx (11 KB)

Note that I have not updated this document for 2008 task/component changes, and some of the abbreviations may have been changed from Jamie’s original list of suggestions to suit the needs of my organization at the time.

(Hmm, what is a retweet in blog terms? This is a re-blog.) Jamie Thomson has provided a post providing a link to download Microsoft Visio stencils for SSIS diagramming. Get it here (Josh Robinson – original source) or here (Jamie Thomson).

A quick tip for those that may want some better organization over the default provided by BIDS when working with SSIS packages.

You can add your own groupings to the Toolbox window by right-clicking anywhere in the toolbox, and selecting “Add Tab”.

For example, in the data flow window, I can add a new tab (or grouping) for Analysis Services Destination components.

1) Right click on the toolbox background and select “Add Tab”

Add Tab

2) Give it a name.

Name Tab

3) Drag components to the new tab.

Dragging Components

4) Repeat as needed.

Repeat

The final results might look something like this:

Final Results

If you have something that you’d like to see blogged here regarding SSIS and/or SQL Server, please let me know and I’ll see what I can do.

Also, if you find a post here that you’re using to work on an issue in SSIS 2008, let me know if it isn’t working for you. It could be that I don’t have updated content for use in the SSIS 2008 environment. It’s easy enough to update, I just won’t be able to catch all of the items listed here.

One last thing – the annual MVP Summit is coming up in February. If you have something that you’d like for me to bring to the SSIS dev team in terms of feedback, feature requests, bugs, etc…, let me know and I’ll do my best to present them to the team.

Today I had a situation come up that prompted a SQL Server Connect feature request submission for SSIS: add the ability to mark a connection manager object as read-only. In doing so, the connection manager would not show in dataflow destinations, it would throw a warning if added to an OLE DB Command component, would error in package validation if used in a destination (which could happen post-development via config files), etc…

What do you think? Is this something you could see value in having implemented?

Connect submission: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=525805

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.

Way too often, we hear many misconceptions about what the Integration Services Service actually does. Some think it is required to execute packages. Others think it is used for checkpoint restarts. Others think it speeds up execution. So, which group is correct?

The answer is none of them. The SSIS Service, quite simply, is responsible for managing the Integration Services interface in SQL Server Management Studio. It enables the ability to import/export packages, view running packages, and view stored packages. It really doesn’t do anything more than that.

Disabling the service will not affect:

• package development (you can develop packages without the service)
• the ability to execute packages (DTEXEC and other executables are responsible for this)
• checkpoint restarts of failed SSIS packages
• the ability for users to query the msdb database for stored packages via SQL
• package execution speed
• SQL Server Agent’s ability to execute packages

The SSIS Service is not cluster-aware, and is in fact not recommended. http://msdn.microsoft.com/en-us/library/ms345193.aspx

EDIT – See Michael Entin’s comment below. The SSIS Service will also cache component/task metadata so that the SSIS runtime engine can poll the cache to see what is installed, which may help speed up package load times however small those gains may be.

“Why does my data flow take so long to execute when I use an OLE DB Command component?
“I need to update values in another table for every row in my data flow.”

These are common questions I see when dealing with “slow” performance of an SSIS package. Sometimes though, the developer of the package does not even understand which component is taking so long to process say, thousands of records in their data flow, so it is understandable why this component may proliferate in many shops. (Sometimes it is needed, of course, but you need to always ask the question – do I need the updates to happen RIGHT NOW?)

If you have a need to update data in another table, while still pushing data from a source to a destination, the OLE DB Command is often used because of the thought that it is part of the data flow, so why not use it.

Read on for more….
(more…)

A collection of 53 Microsoft MVPs (myself included) have wrapped up authoring one of the greatest SQL Server books yet to hit the shelves – SQL Server MVP Deep Dives.

This book brings together the experience from all corners of SQL Server: Design & Architecture, Development, Administration, Performance Tuning & Optimization, and Business Intelligence. Each section contains unique topics ranging from avoiding common query mistakes, running SQL Server on Hyper-V, to SSIS performance tuning.

The book is available to pre-order here: http://www.sqlservermvpdeepdives.com

(EDIT: Save 50% by purchasing the book from the above site by the end of September when you use the code, pop0928)

All author royalties – and this is what really makes this book stand out – are being donated to War Child International, a charity involved in bringing food, health, and happiness to kids across the world involved in war.

Many thanks to the editors for pushing this book through to completion: Paul Nielsen, Kalen Delaney, Greg Low, Adam Machanic, Paul S. Randal, and Kimberly L. Tripp

For more information on the book, continue reading:
(more…)