SSIS Talk

When creating environments in the new SSIS catalog, you are able to define environment variables. When creating string variables, you are limited to a string length of 4,000 characters. If you exceed 4,000 characters, you will receive an error. Details below.

Environment variables can be created by right-clicking on the environment in question and selecting “Properties.”

In the properties window, select “Variables” so that you can add new variables.

As long as you stay within the 4,000 character limit, you’ll be fine. If you exceed that limit, expect an error message as below:

Clicking on the error message in the highlighted progress window, you can see the full details:

Yep, internally, the SSIS team uses the sql_variant data type, which cannot store an NVARCHAR(MAX) column. Since our string is longer than 4,000 characters (the max limit for NVARCHAR), the data type of the data we are sending must be converted to an NVARCHAR(MAX) which is an incompatible type for sql_variant.

The SSIS team is aware of this and hopefully they’ll be putting in a limit to prevent users from entering anything more than 4,000 characters to prevent the error from happening in the first place, or at least raising awareness of the limitation.

Also of a somewhat unrelated note – if you choose to set a variable to “sensitive” once you save it to the database by clicking the OK button, you will not be able to retrieve its value from within the GUI. Just beware. Treat the sensitive property as if you were working with passwords (which may be a common use of this property anyhow).

So, in response to Jamie Thomson’s article on extracting all SSIS packages from msdb, I also wanted to show how this can be done from within SSIS itself. It’s very straightforward, and uses a data flow component you may not be familiar with: Export Column.

(more…)

Have you ever designed an SSIS package and wished that you had easier layout tools? Ever wanted the snap-to guides that allow you to position tasks/components in such a way that they align with existing tasks/components on the design surface?

Here’s what I’m referring to (note the blue dashed line on the left):

Well, now’s your chance – please vote for Jamie Thomson’s suggestion to add this feature to SSIS. While there, please be sure to leave feedback as to why you would like to see this included.

Vote here: https://connect.microsoft.com/SQLServer/feedback/details/644668/ssis-snap-to

The first public CTP of the next major version of SQL Server is now available. Go and get it, kick the tires, and let the team know about any bugs you may find.

Some of the immediate changes to highlight in CTP1 are: SSMS is now based on Visual Studio, sequence generators, several new DMV/DMFs (including one to investigate the resultset of a query/sproc without actually executing it), centralized SSIS management, SSIS changes, and others.

http://www.microsoft.com/Presspass/press/2010/nov10/11-09PASS10PR.mspx?rss_fdn=Press%20Releases

Stay tuned here for some highlights in Denali CTP1, especially around SSIS.

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