SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred

Code: 0xC0202009
Source: Connection manager ""
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Cannot generate SSPI context".

I got this error recently, and could find almost no useful posts about what caused it and how to solve it, so here's what I found. It's simple!
When the "domain guy" in our company setup the domain account that SQL Server Agent runs under up, he didn't check "password never expires", so the account's password had expired, causing all the Integration Services packages that connect to Windows Authentication connections to fail. I modified the account in Active Directory Users and Computers to never expire, reran the packages, and they succeeded.
So, to summarize, make sure the account you're using for SQL Server Agent is in a good state as your first level of troubleshooting!

If this helps you out, leave a comment to help encourage me to post more often!

Cheers,
Rick

ibm system i access error 1327

Where I work we (like most companies, I would imagine) have our My Documents folder automatically mapped to a drive on a network. I'm not sure if it's expressly this, or the fact that the drive is on Network Attached Storage, but either way we get:
Error 1327.Invalid Drive: :\
from the IBM System i Access for Windows Installer InstallShield Wizard when it's trying to computer space requirements.
Here's the fix:

1. Save any open documents!
2. Open regedit
3. (insert warning about the risks of modifying your registry here!)
4. Browse to HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders\Personal
5. You should find the offending drive letter here
6. Change it to a local drive (double click "Personal" and type in "C:\" without the quotes, for example, but if you don't know how to do that, call your IT professional)
   
7. Install iSeries Access
8. Repeat steps 4-6 above, but change it back to the offending drive
9. Enjoy convenient access to your iSeries!

Hope that helps someone out!

Rick

Dealing with attributes that appear in more than one parent group

Sometimes you have an attribute in your dimension where the values appear in more than one grouping or category above it. Like the following:
Product     Product Category
Apple          Fruit
Apple          Snack

When you build this attribute in Analysis Services, you need to make the key for the Product attribute either a compound key, or build it off of something else that is unique, such as the business key. I don't happen to have the BK for Product, so I setup a composite key of Product Category and Product. If I didn't Analysis Services would choose one of the Product Categories and put all the measures in that Category when I was at that level of aggregation.
So, seems simple enough. The problem is that if I just setup the Product to have a composite key, I now get 2 Apple rows when I use Product as an attribute on a report outside of the Product Category->Product hierarchy. Note: if Product were 3 levels deep in a hierarchy, you would need to include all 3 levels. I also would get sorting based on Product Category and Product. I can switch the sorting pretty easily by having the sort based on Name instead of Key, but my users certainly don't want Apple to appear twice on the report.
The solution
Create an attribute with the composite key (don't forget the all important Attribute Hierarchies!), or base it on something unique like the business key, and give it a name such as Product for Hierarchy. Then create a second attribute (easily accomplished by dragging the field from the Data Source View pane of the Dimension Structure page to the Attributes pane) with the default settings (Product as KeyColumn, (none) as Name Column). Use the Product For Hierarchy for the Hierarchy, and rename it in the Hierarchy itself to Product. Then set the AttributeHierarchyVisible property to False, so it can't be seen outside of the hierarchy.
I routinely do this for many attributes so that I'm not forcing an analyst to use an attribute only via the hierarchy.

Hope this helps someone,
Rick

Restoring master DB from a backup in sql server 2005

We recently had some issues with the RAID getting corrupted on one of our production SQL Servers after some repeated power outages caused by our giant UPS failing. As a result we needed to rebuild the RAID from scratch and restore from a full drive backup from Net Backup. Unfortunately Net Backup didn't get all the files on the data drive, most importantly (at least for the purpose of this article!), master.mdf was missing. Fortunately we had a backup of master, but SQL Server won't start without master.mdf, and master.bak doesn't really give you that.
Yes, I realize there are other articles out there about this, but I didn't find any of them to be exactly what I needed, so someone else might find use in mine.
1. So, first step is to rebuild the master DB to a default version, so we can get SQL Server started and restore the master.bak file we made:
a. Run the following command: "M:\ENGLISH\SQL2005\DEVELOPER\SQL Server x64\Servers\setup.exe" /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=
i. Other articles online will tell you to use "start /wait" at the beginning of that command, but I had 0 success with that
ii. M:\ENGLISH\SQL2005\DEVELOPER\SQL Server x64\Servers\ is the path for installing a 64bit Developer edition from the MSDN CD. Yours may differ, but it's CRUCIAL that it be the same as the original path. To find the original path:
1. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\
2. Then look through all of the InstallProperties\DisplayName until you find Microsoft SQL Server 2005 (I started at the above location in the registry then searched till I found the right one)
a. Make sure it's the actual server instance, not one of the other various components
3. Then use the value in the InstallSource key
b. Once you've gotten the above command to run successfully, you'll be getting a fresh copy of your system DBs in the original data location. If you want to monitor setup.exe while it performs this activity use your Task Manager.
c. Once this has completed, confirm that SQL Server works by starting it from the SQL Server Configuration Manager
2. Now we need to restore Master from our backup:
a. Start the SQL Server in single-user mode
i. From a DOS prompt: \sqlservr.exe -m
ii. -m is the switch for Single User Mode.
iii. On the server I'm restoring = E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
iv. It won't appear like the process is complete in the normal sense when the SQL Server is running, if you're like me and are used to seeing the command prompt again after you run something from the command line, but once the messages stop scrolling in the DOS window the SQL Server instance should be started.
v. Confirm the above by looking for sqlserver.exe in Task Manager
b. Restore the backup
i. Personally I scripted this all via Management Studio after I rebuilt the "vanilla" master.mdf, then restarted the SQL Server instance in single-user mode. That way I also had the query window open so I could just run the statement twice (first time it reestablishes connection to the SQL Server) and run it
ii. Here's the command for our install:
RESTORE DATABASE [master] FROM DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\master\master_backup_200907082100.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
iii. Once this has executed, it will stop the SQL Server instance, giving the following messages:
11 percent processed.
20 percent processed.
31 percent processed.
40 percent processed.
51 percent processed.
60 percent processed.
70 percent processed.
81 percent processed.
90 percent processed.
100 percent processed.
Processed 432 pages for database 'master', file 'master' on file 1.
Processed 2 pages for database 'master', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.
c. Start the SQL Server service normally via the SQL Server Configuration Manager
i. If you see an error like this:
1. Server local connection provider failed to listen on [ \\.\pipe\SQLLocal\MSSQLSERVER ]. Error: 0x5
2. Try rebooting the SQL Server as it may not have gracefully released control of the ports SQL Server needs the last time it stopped.
d. Restore any other system databases first
i. Most likely you'll need msdb, possibly model
e. Restore any other DBs you may need
f. Ensure that you don't need to reinstall any service packs and patches

Troubleshooting Researching or Tracking Down Failed SQL Server Logins

I found such a dearth of usable information on this when I just searched for it on Google, that I thought I would write a post detailing the process I figured out.
For starters, I'm getting "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: xxx.xxx.xxx.xxx]" repeatedly on one of my SQL Servers. It's logged in a variety of places, namely the Application Log in Event Viewer, with the following info:
Event Type: Failure Audit
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 18456
Date: 6/12/2009
Time: 10:17:21 AM
User: NT AUTHORITY\ANONYMOUS LOGON
Computer:
Description:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: XXX.XXX.XXX.XXX]

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:


And in the SQL Server Log, with this info:
Date 6/12/2009 9:45:21 AM
Log SQL Server (Current - 6/12/2009 9:45:00 AM)

Source Logon

Message
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: XXX.XXX.XXX.XXX]

And a paired row stating:
Date 6/12/2009 9:45:21 AM
Log SQL Server (Current - 6/12/2009 9:45:00 AM)

Source Logon

Message
Error: 18456, Severity: 14, State: 11.


While there are a couple of different things you can glean from these error messages, such as why it's failing (do a lookup based on the State. In this particular case State 11 means that the login is good, but the connection failed). You can see the state descriptions below:

State	Error description
1	Account is locked out
2	User id is not valid
5	User id is not valid
7	The login being used is disabled
8	Incorrect password
9	Invalid password
11-12	Login valid but server access failed
16	Login valid, but not permissioned to use the target database
18	Password expired
27	Initial database could not be found
38	Login valid but database unavailable (or login not permissioned)

But that's not really what I wanted. I wanted the login attempts to stop. So I started profiler with a connection to the server with the failed logins. I started with a blank template, and added a single event:

Use Audit Login Failed under Security Audit, with all the columns.
Then look for the entry for the failed login:

It's kind of hard to see in the screenshot, but basically the important info you're looking for is in the ApplicationName, ClientProcessID, HostName, LoginName and/or SessionLoginName, and TextData.
In my particular case there wasn't any data in the ApplicationName, which could go a long way toward helping you figure it out. And I've seen suggestions online to use profiler to find this. Hopefully you'll be able to solve your problem using this column. The next column is what was really key for me:
ClientProcessID
Use this to go to the Task Manager on the client machine (found in the TextData column). If you only have an IP, use nbtstat -a or whois from the command line to find the machine name. Add the PID to the columns displayed in the Task Manager by choosing View->Select Columns from the menu:

Then check PID from the list that appears:

Then sort the Task Manager list by PID by clicking on it, and scroll down to the PID that matches the ClientProcessID column from Profiler:

In my case it was RpScheduler. This will lead you right to the offending service or client app. In my case I just disabled the service on the client machine because I don't need it. In other instances you may need to change the login info for the service, or the client application. This is really about finding the app, not so much about troubleshooting what you find.

Hopefully this helps someone, and feel free to comment with suggestions!

More SSIS Error Codes: -1071607689 1071607689

I just encountered errorcode -1071607689, which translates to DTS_E_OLEDBDESTINATIONADAPTERSTATIC_CANTCONVERTVALUE.  Not the most helpful error definition.  In my case: the data I was trying to insert was larger than the field it was being inserted to.  I was trying to insert into a varchar(255), and the data was larger than that.

HTH,

Rick

Can't build cube because of broken aggregations

I just removed some dimensions from a cube I had built, to clean things up a bit. When I tried to build the cube it failed because of references to the now-missing-dimensions in the aggregations. So I went to Design Aggregations to rebuild them. This failed because of the now-missing-dimensions.
Here's my workaround:
I re-added the now-missing-dimensions, then rebuilt the cube to test it. It succeeded, so I went back to the Design Aggregations tool, and started it. I then stopped it as soon as it had built 1 aggregation. I then removed the dimensions I wanted to remove and rebuilt the aggregations.