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