SSIS handling errors in control flow

To ensure that errors don't cause the package to stop, but all errors are reported back to parent containers, set the following:

FailParentOnFailure = True
MaximumErrorCount = 99999 (an "infinite" value)

SQL Server Agent Jobs and GUI Automation

Scenario

An application vendor has provided an application database maintenance utility that only has a GUI interface. You want to automate this maintenance activity as a SQL Server Agent job.

The best solution would probably be to tell the vendor to add a command line interface to the utility to facilitate unattended use. Unfortunately the vendor may be unable or unwilling to achieve this.

A Solution

1. Use AutoIt to script and compile an executable that will perform unattended execution of the ute
2. Call the AutoIt-generated exe from a SQL job using CmdExec

Step 1 is challenging, particularly if the vendor's utility is complex or poorly documented. Step 2 can be difficult to implement due to environment constraints.

With AutoIt you have the choice of

• installing the AutoIt environment and running the .au3 script file
• compiling a .exe from the script

In my experience you'll have far less trouble getting the exe approved by change control than requesting deployment of the entire AutoIt installation.

1. AutoIt

AutoIt v3 is the latest available version. The download contains a useful help file and example scripts. The web also has plenty of documentation and forum discussions on most topics you might be interested in. I'll just cover off a few points I thought were important:

• The language is a variant of Pascal
• Line continuation is performed with _
• Carriage return & line feed is represented by @CRLF
• Variable $CmdLine[0] contains the number of command line parameters provided
• ConsoleWrite() can be used to provide feedback and debug info
• The included Au3Info.exe is a handy GUI spy for identifying those much-needed CLASS and INSTANCE values
• Some functions are not suited to unattended use (see Q21 excerpt from AutoIt FAQ below)
• If your script is too fast, add some Sleep(n) statements to let the GUI catch up
• With the WinExists() function, AutoIt is able to handle GUI events that are too fast for a human to intercept or even perceive. Therefore writing a script based on manual user steps may be insufficient to properly control the GUI

Q21. Why my script doesn't work on locked station
A21. On locked station any window will never be active (active is only dialog with text "Press Ctrl+Alt+Del")In Windows locked state applications runs hidden (behind that visible dialog) and haven't focus and active status.
So generally don't use Send() MouseClick() WinActivate() WinWaitActive() WinActive() etc.Instead use ControlSend() ControlSetText() ControlClick() WinWait() WinExists() WinMenuSelectItem() etc.
This way you may have your script resistive against another active windows.and it's possibe to run such script from scheduler on locked Windows station.

2. SQL job

The SQL Server Agent is a service. By default, services do not interact with the desktop. This means your AutoIt exe can launch the utility but will be unable to detect or use any of its GUI elements.

To allow SQL Server Agent to interact with the desktop:

1. Start:Administrative Tools:Services
2. Right-click SQL Server Agent and select Properties
3. Select the Log On tab
4. Select Local System Account, check "Allow service to interact with desktop" and click OK
5. Restart SQL Server Agent (right-click and Restart)

Note the checkbox "Allow service to interact with desktop" is only available for the Local System Account. To use other accounts, they would need to be enabled to "Act as part of the operating system".

Potential problems are around whether Local System is suitable for running the agent service. Local System may not be suitable for other jobs on the same server. Some jobs may need permissions to other servers and it may not be appropriate to grant them to Local System.

A workaround would be to create a separate SQL Server Agent instance on the same server, just for jobs that need to interact with the desktop. However the additional instance will consume server resources.

A minor annoyance: a desktop-interaction-enabled SQL Server Agent will insist on keeping a Command Prompt window open while it's running.

SSIS - annoying animated data viewer creation

Why is there an animation when creating a data viewer in Visual Studio? Was Thwarted Game Dev Guy given the job of implementing this?

Mild irritation becomes serious annoyance when a live issue is being tested on a box under heavy load and the lil data viewers are taking a minute each to parachute into the data flow.

Copying SQL Server 2005 push replication using Generate Scripts

On the server where the existing replication is:

1. Right-click the publication (in SSMS, Replication: Local Publications) and select Generate Scripts...
2. Select To Create or Enable the Components
3. Choose file location and Save
4. OK and Close

On the server you want to copy it to:

1. If the server has never had replication before, manually create and delete a test publication (right-click Local Publications & select New Publication, choose some random stuff, then right-click the test publication and Delete). This will ensure the distribution database is created along with other stuff needed for replication
2. Open the script, replace the servers and logins as appropriate
3. Run on the target server

Using sp_msforeachtable on remote databases

Yes you can! The only proviso is that your linked server must have RPC and RPC Out enabled.

1. Right-click the Linked Server and select properties
2. Select Server Options
3. Click RPC & RPC Out and OK

Then you can do wild stuff like

exec [remoteserver].[remotedb].[dbo].sp_MSforeachtable "ALTER TABLE ? DISABLE TRIGGER ALL"

SSAS and percentage measures

Scenario

Let's say you have the following base measures that are the raw data held in the source system:

• ListPriceDollars
• StandardDiscountPercentage

The cube users require these two measures and an additional StandardDiscountPriceDollars measure to be available for use.

In this situation, it's easy to get stuck at the point of ensuring StandardDiscountPercentage displays sensible numbers at all aggregation levels. There are plenty of options to play with - aggregation functions, measure expressions, calculated members, etc. For people with more of a SQL than MDX background there's a lot to understand here. Throw in some null values and a parent-child relationship and you'll be torturing yourself trying to find the best way to represent this in SSAS.

Solution Option

One possibility is to completely avoid trying to aggregate percentages.

1. Change your source view or named query to calculate StandardDiscountPriceDollars (=Product.ListPriceDollars / (1 - (Product.StandardDiscountPercentage/100))
2. Don't use the raw StandardDiscountPercentage value in the cube - instead, create a simple replacement calculated member (= 1 - ([Measures].[Standard Discount Price Dollars] / [Measures].[List Price Dollars])

With a little bit of zero and null handling the job will be done.

Getting MDX from Excel pivot tables

1. Data: Connections
2. Properties
3. Select Definition Tab
4. Add Log File=c:\temp\pivotlog.txt;
5. OK
6. Close
7. Refresh Pivot

Note: the definition text provided above is case sensitive! Particularly Log File

Displaying data source information in Reporting Services reports using expressions

The following expression is about the best I have come up with:

=DataSources("myDataSource").DataSourceReference & " " & DataSources("myDataSource").Type

This will display:

• Data source name - probably also "myDataSource" in this example. The full path is provided when the report is run in Report Manager
• Data source type e.g. "OLEDB-MD" is an Analysis Services data source

Limitations:

• You can't use expressions referring to data sources in the header or footer. But who wants the data source details displayed in the report body??
• The data source reference has to be hard coded in every report. I'm not aware of any way to enumerate this to just fetch the first datasource. It would be fantastic if the following worked, but it doesn't:

=DataSources[0].DataSourceReference & " " & DataSources[0].Type

Pausing SQL Server transactional replication without losing queue contents

To Pause:

1. Open SSMS and connect to the distributor
2. Disable and Stop the "Distribution clean up: distribution" job
3. Expand SQL Server Agent, right-click on Jobs, select View Job Categories and choose REPL-Distribution
4. Disable and Stop all listed jobs

To Continue:

• Enable and Start all the jobs that were stopped

I have seen countless forum discussions for this topic but no succinct list of steps.

SSIS OLE DB Command error with NOT NULL constrained columns

Symptom:

OLE DB Command fails with the following error reported to the package log:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. The RPC name is invalid.".

Possible Cause:

The data flow task is experiencing a constraint violation.

For a table ThisTable defined as follows:

ThisTableKey int not null
ThisTableValue varchar(10)

and OLE DB Command data flow task containing the following statement:

update ThisTable set ThisTableValue = 'UpdateVal' where ThisTableKey = ?

where ? has been mapped to a data flow column.

If the data flow column contains a NULL value then the task will fail because of the NOT NULL constraint on the column.

Diagnosis:

This is hard to diagnose in SSMS because the SQL statement above will run fine (and do nothing) when ? is substituted with NULL.

Instead add data viewers to the outputs of the offending OLE DB Command. You should see the following columns at debug time:

• ErrorCode: -1071607702
• ErrorColumn: 12345 (this is an example value)
• ErrorDescription: The data value violated the schema constraint.

Open the package in a text editor and search for 12345. You will find a LineageId recorded against it. Search for the LineageId and you will find the column name (should be ThisTableKey).

Resolution:

Use a Conditional Split task to filter out all NULL values in the data flow column before the OLE DB Command.

SSAS Aggregation Design error

Symptom:

The following error appears when deploying the cube or designing aggregations

The reference to attribute is not valid

Cause:

The partitions file has gotten out of sync with the cube file. Perhaps the cube file got checked back in to source control but the partitions file changes were undone.

Solution Options:

You may be able to hack the partitions file to clean up invalid attribute references.

Then again it may be easier just to roll back and reapply your changes.

SSAS processing error could be due to proactive caching settings

Symptom:

"Errors in the high-level relational engine. The data source view does not contain a definition for the [object] table or view. The Source property may not have been set."

But the data source view doesn't contain this table.

Possible Cause:

A table has been specified for tracking in the notifications for proactive caching, but isn't available any more for some reason.

Solution Options:

• Update the table name and location as required
• Disable proactive caching for the partition

See http://www.mssqltips.com/tip.asp?tip=1563 for more details.

Stupid SSIS variable list bug

1. Bring up the variable list and start editing the name of the first variable
2. Click the Name column of the variable list to sort the list
3. Hit Escape to cancel the edit

The variable name you were editing has now replaced the name of the variable that was top of the list after your sort! Cancelling the edit didn't stop this from happening.

Workaround: remember and reenter the variable name that was overwritten. Bleagh.

Adding folders to SSMS project

Problem:

Can't create folders in a SQL Server 2005 Management Studio project.

It would be handy to be able to separate scripts into folders. Unfortunately only three default folders are available: Connections, Queries and Miscellaneous

Workaround:

1. Open the .ssmssqlproj file in a text editor
2. Copy one of the LogicalFolder tags e.g. Miscellaneous
3. Replace the Folder Name and Type. Make sure the Type value is different from all of the existing folders
4. Your new folder(s) should now be available in SSMS

Example insertion:

<LogicalFolder Name="Stored Procedures" Type="4" Sorted="true">
<Items />
</LogicalFolder>

Getting excel into a sharepoint list

Symptom:

When you try to import an Excel spreadsheet into a Sharepoint list using the "Import Spreadsheet" option, the following message appears.

The website declined to show this webpage HTTP 403

Most likely causes:

This website requires you to log in.

What you can try:

Go back to the previous page.

More information

This error (HTTP 403 Forbidden) means that Internet Explorer was able to connect to the website, but it does not have permission to view the webpage.

For more information about HTTP errors, see Help.

Cause and Resolution:

Some have suggested reinstalling Office components on the server or formatting your spreadsheet in table format. Dunno, haven't tried.

Workaround:

Push from Excel as follows:

1. Data: List: Create List
2. Choose your list range and click OK
3. Data: List: Publish List
4. Enter url and name, and click Finish. All done!

SSIS can't find environment variables

Scenario: Deploying SSIS packages that use environment variables and setting up a SQL Server scheduled task to kick them off.

Symptom: Can run packages manually, but the scheduled task fails.

Cause: Environment variables. You have probably logged off and on to the server a few times while attempting to run the packages manually, which means your login has the latest environment variables. However the SQL Server Agent may not have "logged in" recently and will be unaware of the new environment variables.

Resolution: Restart the SQL Server Agent (which is the equivalent of logging it back on to pick up the latest environment variables).

SSIS package location selection criteria

Kirk Haselden's SSIS blog posts circa 2005-2006 are no more, so I am saving what little I have managed to copy or scrounge.

Advantages of Saving to Files:

• Easier to do shared source control
• Ultra secure when using the Encrypt with User Key encryption option
• Not subject to network downtime problems (saved locally)
• May escrow deployment bundles including miscellaneous files
• Less steps to load into the designer
• Easier direct access for viewing
• May store packages hierarchically in file system
• Projects in Visual Studio are disk based and require the package to be in the file system
• Generally, a better experience during development
  

Advantages of Saving to SQL Server:

• Easier access by multiple individuals
• Benefits of database security, DTS roles and Agent interaction
• Packages get backed up with normal DB backup processes
• Able to filter packages via queries
• May store packages hierarchically via new package folders
• Generally, a better in-production experience

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.