Again, frustration.

After almost breaking my arm patting myself on the back getting past my last SAP BW issue I found that SAP BW Connector and SQL Server 2012 were punishing me again. I was building a second package against the same SAP instance on the same SQL Server 2012 instance when I hit something more than flip this field from 3 to 1 kind of thing.

NO SOUP FOR YOU!

“Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: Index”

Oh mister SSIS this means war! Looking at the complete output log I saw that SAP BW had actually delivered the data but the SAP BW source component had thrown a hard error. It was a general error and searching Bing yielded next to nothing. I did find posts like this one that pointed to some bugs inside and outside the connector. This, of course, builds a ton of confidence that its going to work in the future.

Desperation Leads To A Fix.

After searching dozens of KB articles around the SAP BW 1.1 and 1.0 connector I finally just pulled the trigger and installed service pack 1 for SQL Server 2012 and the 2012 SP1 feature pack SAP BW 1.1 connector. I hate when the standard tech support advice works…. The scary thing is there are still outstanding bugs that are fixed in CU3 and CU4 that aren’t included in SP1. The worst thing is I never could find an exact KB that matched my error codes. I don’t know why it was broken and I don’t know if there was a specific patch or CU that addressed my bug. This isn’t the first time something like this has happened ether. Sometimes bug fixes don’t get listed when a CU or service pack is made available.

Frustration.

If you have ever had to work with Integration Services and data sources other than the Microsoft variety you know how frustrating it can be. I recently started a project using SQL Server 2012 and SAP BW 7. The customer had already decided that the other commercial options weren’t viable and settled on the Microsoft Connector 1.1 for SAP BW. A test package was built and worked.

I had already read through Using SQL Server 2008 Integration Services with SAP BI 7.0 and felt comfortable with the instructions. Even though we were using SSIS 2012 I knew there was very little differences between the 1.0 connector and the 1.1 connector. I built out our new package and all seemed well. The SAP BW connection in the connection manager hooked up and tested out. The SAP BW source also tested out pulling the metadata and even delivered preview data just fine. Next, I attempted to run the package. Just as I expected it ran and I got the data I expected. The next three attempts all resulted in SAP BW connector timeouts. I kept poking and prodding it. The SAP admin didn’t see any issues ether.

So, I did what I always do. I stopped tussling with the package and walked through all the steps again. I went back and read the doc again. I made a few notes and shot an email off to the SAP administrator. I settled on one thing pointed out in the doc:

“We want to keep the number of parallel processes to a reasonable value for the overall DTP process type DTP_LOAD, but this parallelism can lead to a timeout error during the Open Hub DTP extraction through Microsoft Connector for SAP BI”

Sure enough, it was set to something other than 1.

VICTORY!

Now, we get all our records and don’t have the timeout issues. The downside seems to be a bit slower performance. If the choice is a faster data pump that only runs once every five or so attempts or one that runs slower but runs every time I’ll slow up a bit. The fact that everything verified and I even got preview data on every attempt was hugely frustrating and time consuming. Knowing when to stop and reevaluate the problem and having a good relationship with the SAP administrator probably saved me hours if not days of cursing and shaking my fist at both Microsoft and SAP.