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).

I’ve found very little reference online about how to copy a master image from one Aperture project to another. The solution is quite simple – hold down the Option key while dragging an image from one project to another. That’s it.

Saturday, August 27th, in Omaha, NE, we are hosting a SQL Saturday event that is promising to be a great time. This is a free event ($10 lunch fee if you want lunch on us) that helps promote our local SQL PASS chapter. Register here: http://www.sqlsaturday.com/91/eventhome.aspx

I will be presenting on two topics: SSIS changes in Denali, and Using Event Notifications for Monitoring SQL Instances.

The first session, SSIS Changes in Denali, is pretty self-explanatory. Expect that I will go through each of the major changes in demo form, with some discussions around each.

The second session around Event Notifications, will be quite a bit more advanced and will talk about using the Event Notifications infrastructure in SQL Server combined with Service Broker to implement a very robust monitoring system. This infrastructure allows for capturing all kinds of audit information such as creating a table, dropping an index, rebuilding statistics, blocking, deadlocks, etc… There’s quite a bit of information that can be captured. I will show how to set this up and how to consume the information for monitoring purposes.

Don’t miss your chance at seeing me speak (!) and most importantly don’t miss your chance at learning SQL Server topics for free. See you there!

An event flyer can be found here: http://www.ssistalk.com/Flyer_updated.pdf Please distribute as you see fit!

Omaha, Nebraska. A town based alongside the Great Flood of 2011 Part II (The Missouri River) will be hosting its first SQLSaturday event on August 27th, 2011. A SQL PASS sponsored event offering a full day of free SQL Server education, networking, food, and fun. The event will be held at the brand new, state of the art College of Business Administration building at the University of Nebraska at Omaha’s campus.

We have the call for speakers open right now. If you would like to speak and would like to know more information, please contact me (using the Contact link at the top of the page) or submit your details online and let us know you’d like some more information.

For more details, visit http://www.sqlsaturday.com/91/eventhome.aspx.

After much waiting, what is perhaps the most complete release to-date of the next version of SQL Server, code named “Denali” has been released. This is Community Technology Preview #3 (CTP 3) and will be well worth your time to experiment with. If you’ve worked with CTP 1, throw it away and move toward CTP 3. It should pretty much contain all of the features you’ve heard of – AlwaysOn, SSIS enhancements, etc…

Read the announcement here: http://blogs.technet.com/b/dataplatforminsider/archive/2011/07/11/sql-server-code-name-denali-ctp3-is-here.aspx

When trying to investigate the SQL error, “Error: 18456, Severity: 14, State: 38.” it can be frustrating when not a single log on the system will tell you what the actual problem is. In the case of state 38 (16 or 27 prior to SQL 2008) this error means the database specified in the client connection does not exist, or is offline. In a large shop with many various clients it can be hard to find out the source of the connection, so you’re option is to use the tools at your disposal.

The SQL Error Log does not help much at all. Here’s what’s in the SQL Error log:
2011-05-06 09:06:17.28 Logon Error: 18456, Severity: 14, State: 38.
2011-05-06 09:06:17.28 Logon Login failed for user ‘DOMAIN\ssrs.service’. Reason: Failed to open the explicitly specified database. [CLIENT: 192.168.0.147]

The Windows Application log is not much help either.
Login failed for user ‘DOMAIN\ssrs.service’. Reason: Failed to open the explicitly specified database. [CLIENT: 192.168.0.147]

In other words, useless for troubleshooting. The way to obtain the database name the client is trying to connect to is to use SQL Server Profiler.

To set up SQL Server Profiler, connect to the SQL instance where the error is occuring and then track the following events:
Errors and Warnings: User Error Message
Security Audit: Audit Login Failed

For these two events, make sure you capture at least the following columns: ApplicationName, HostName, LoginName, SPID (required), StartTime, TextData (where the message text will be), Severity, State, ClientProcessID, and Error.

Then run the trace. You’ll see the following data under the “User Error Message” event when the login failure occurs:
Cannot open database “DatabaseName” requested by the login. The login failed.
For the “Audit Login Failed” event you’ll see the following data:
Login failed for user ‘BLUENE\ssrs.service’. Reason: Failed to open the explicitly specified database. [CLIENT: 192.168.0.147]

Using this information (and the rest of the info in the other columns you selected), you should be able to go to the source and identify which process is trying to connect to the specified database and take appropriate action.

If you’re interested in the other states that error 18456 can generate, please visit fellow MVP Aaron Bertrand’s page on this topic for a very nice listing.

Attached is the SQL Profiler Trace Template for SQL 2008 instances if you want to import it instead of manually configuring SQL Profiler. To import the template, in SQL Server Profiler, go to File->Templates and select Import…

SQL_Login_Failures_Profiler_Template.tdf (860 bytes)

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.

For those of you that wished this site was accessible on mobile clients, this request is now complete. The site will detect that you are browing with a mobile client and present you automatically with a format suited for your device. Please let me know if you run into issues.

Also, when viewing the mobile version, at the bottom of the page is an option to switch back to the full site (see the switch titled “Mobile Theme”). Enjoy.