x:\desktop refers to a location that is unavailable

If you get this error (I got it in Internet Explorer) then you probably had My Documents pointing to x: drive and moved the files to somewhere else (by right-clicking My Documents, selecting Properties and clicking Move).

One of the registry settings wasn't updated by the move.

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders

This probably still has a value of x:\desktop. Change it to %USERPROFILE%\Desktop

I'm using Windows XP Pro SP2, I have no idea which other OS's & versions are affected.

SSIS Breakpoint and other debug problems

Symptoms:

• When you add a or remove breakpoint from a breakpoint, the breakpoints of other tasks in other packages also change
• SSIS ignores breakpoints or breaks unexpectedly during debug
• Tasks execute unexpectedly during debug

Cause:

Duplicate Task Ids

When a package is created by copying another package, all the object IDs will be identical to the original package's IDs. This confuses visual studio with symptoms as described.

Resolution:

Install BIDS Helper if you haven't already, and use Reset GUIDs.

SSIS Parent Package variables - reading and writing in both directions

This is an old old topic which I'm revisiting after a long time away, and I'm finding the available info quite bitsy, so here's my own writeup about it:

Discounted Option

• The Set Variable Custom Task. It errors when referring to non local variables.

What's Left

• Script Task in the Child Package (bleagh)

"The Parent Variable Read"

Public Sub Main()
Dim vars As Variables
'If package executed directly then no parent variables exist!
Try
Dts.VariableDispenser.LockForRead("ParentVar")
Dts.VariableDispenser.LockForWrite("ChildVar")
Dts.VariableDispenser.GetVariables(vars)
vars("ChildVar").Value = vars("ParentVar").Value
vars.Unlock()
Catch ex As Exception
'Do Nothing
Finally
Dts.TaskResult = Dts.Results.Success
End Try
End Sub

"The Parent Variable Write"

Public Sub Main()
Dim vars As Variables
'If package executed directly then no parent variables exist!
Try
Dts.VariableDispenser.LockForWrite("ParentVar")
Dts.VariableDispenser.LockForRead("ChildVar")
Dts.VariableDispenser.GetVariables(vars)
vars("ParentVar").Value = vars("ChildVar").Value
vars.Unlock()
Catch ex As Exception
'Do Nothing
Finally
Dts.TaskResult = Dts.Results.Success
End Try
End Sub

Notes

• No need for Parent Package Configurations if the child and parent variables are differently named
• There's no need to do anything in the Parent Package.
• Don't put anything in ReadVariables or ReadWriteVariables! This will cause additional LockForRead, LockForWrite and Unlock method calls.

Wishlist - AFAIK these don't exist. I don't have time to give it a crack just now. It may be harder than it appears because nothing has been created in the past 3 years.

• Set Parent Variable Custom Task
• Set Multiple Variables Custom Task

Hard to Template

If you want to go large with a deep package invocation hierarchy then it will be somewhat tiresome to code. This is because local variables override parent variables if they have the same name, so you can't use the same package template for all hierarchy levels.

e.g. if you have a package template with variables var1 and localvar1, create two packages from it and call one package from the other, the child package will use the local var1 and ignore the parent var1.

This is unfortunate because it's becoming steadily easier to work with templates in SSIS using tools such as Pacman.

MDX Query Designer Design Mode - smart in one direction, dumb in the other

(Visual Studio 2005 Report Designer using Reporting Services 2005 with Analysis Services 2005 data source)

Let's say you've created a great query with the MDX Query Designer and you want to reuse it in another report. So you select the dataset and click the Design Mode button to view the underlying MDX, copy it and switch to your new report to paste the MDX. Now you want to use the designer to tweak the query...but you can't. When you click the Design Mode button again, you get the following message:

Changing to design view will result in the current query being lost.

Now that's pretty annoying - unlike Microsoft Access or most other GUI query tools you could choose to name, the MDX Query Designer doesn't parse the MDX to reconstitute the GUI.

SSIS as sledgehammer, transcript as nut

The Register plays host to yet another pointless attempt to sell the universal applicability of BI tools - this time by Microsoft. The compleat Palinalysis could be performed with an advanced text editor.

SQL Server 2005 Report Builder - displaying prompted parameter values

This has already been covered off long ago by
http://blogs.msdn.com/bobmeyers/archive/2006/03/26/561147.aspx

=String.Format("Sales Year: {0}", Parameters!OrderYear.Value)

But if you get System.Object[] as the returned value, this is because the parameter is multivalued.

The solution is on this page:
http://msdn.microsoft.com/en-us/library/ms157328(SQL.90).aspx

=String.Format("Sales Year: {0}", Join(Parameters!OrderYear.Value))

The Join function concatenates all the parameter values into a single string.

Using Microsoft Metadata Samples Remotely

Everything documented in this post was a complete waste of time as you can launch DependencyViewer from the command line using the /depDb: switch to specify the remote repository. Example below

DependencyViewer.exe /depDb:"Server=myservername;database=SSIS_META;Integrated Security=SSPI;"

This switch is documented for DependencyAnalyzer, but there are no such instructions about command line usage of DependencyViewer except when you launch it from the command line with the standard /? switch. Must learn to mentally join up very widely spaced dots...

Microsoft provides sample code showing how to interrogate the metadata of the SQL Server 2005 tool suite. The process is as follows:

• Run scripts to create the SSIS_META database
• Run DependencyAnalyzer on your warehouse, which populates SSIS_META with lots of juicy metadata
• Use DependencyViewer to view the metadata in a graphical layout

I spotted an SSIS_META database on a remote server and naturally wanted to peruse its contents with DependencyViewer. However DependencyViewer assumes a local repository, so the person who created this database either had local login rights to the server or had a modified build that could connect to remote repositories. I had neither, so I set about making the necessary changes to the solution.

Form1.cs has a hard coded connection string:

private string connectionString = "server=localhost;database=SSIS_META;Integrated Security=SSPI;";

which I changed to:

private string connectionString = "server=myservername;database=SSIS_META;Integrated Security=SSPI;";

The rebuilt app threw an error on clicking the Load button as follows:

************** Exception Text **************System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=gfjhs98s90s67a6dssd' failed. at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.PermissionSet.Demand() at System.Data.Common.DbConnectionOptions.DemandPermission() at System.Data.SqlClient.SqlConnection.PermissionDemand() at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.Samples.DependencyViewer.Form1.buttonTest_Click(Object sender, EventArgs e) at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
The action that failed was:Demand
The type of the first permission that failed was:System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:Intranet

I was trying to run it from a network share, never a good idea - so I copied the exe to a local location. Next the load button gave me an hourglass for a while before throwing the following:

************** Exception Text **************System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.Samples.DependencyViewer.Form1.buttonTest_Click(Object sender, EventArgs e) at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

I put the connection string in a message box in at the start of ButtonTest_Click and found that the connection string was still pointing to localhost. More searching turned up another reference in Program.cs. The following change in this file got it connecting.

From:

[Argument(ArgumentType.AtMostOnce, HelpText = "ADO.Net SqlConnection compatible connection string to dependency database location.", DefaultValue = "Server=localhost;database=SSIS_Meta;Integrated Security=SSPI;")]
public string depDb;

To:

[Argument(ArgumentType.AtMostOnce, HelpText = "ADO.Net SqlConnection compatible connection string to dependency database location.", DefaultValue = "Server=myservername;database=SSIS_Meta;Integrated Security=SSPI;")]public string depDb;