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.

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.