April 21, 2008 at 8:41 am
I'm trying to create a package using SSIS in Business Development Studio that automatically updates some cubes created in the old SS analysis services manager.
I haven't found time to recreate the cubes in 2005 analysis services yet.
I've managed to create a OLE DB connection to using OLAP 8.0 in the connection manager. However, when I add an analysis services processing task the connection is not appearing in the list of data sources for that task.
If i use the new driver (OLAP 9.0) then it says it cannot make a connection at all.
My question is - am I using the correct Control Flow Task here? Or is it only relevant to the new type cubes?
Do i have to create a DTS package in Enterprise Manager for SQL 2000 and then execute a DTS2000 task on SSIS for instance?
I have only really just started looking at this and am very new to SSIS and indeed DTS to be honest but I don't have lots of time to play around with things unfortunately.
Any help would be much appreciated.
April 21, 2008 at 9:54 am
I had to do this and ran into two issues.
The first is that the SSIS components will not update an AS 2000 cube. You will have to use a DTS package and call the DTS package through the execute DTS package task.
My second issue was that I had not installed the AS 2000 tools on my SSIS server. So, I needed to execute a DTS package that had an AS 2000 processing step in it. The bummer here was I could not install the AS 2000 tools because SQL 2005 was already installed. To fix it, I only found two options - the first was to uninstall everything on my SSIS server and start by installing AS 2000 there, getting it to the correct SP to match my AS 2000 server, and then re-install all of the SQL 2005 stuff. My other option was to create a stored procedure on my AS 2000 server (because it happened to be running SQL as well) that would execute a DTS package via xp_cmdshell. I was then able to call to the procedure from my SSIS package and the actual processing of the AS database happened entirely locally on the AS2000 box. It was a hack, but it ended up working out.
May 1, 2008 at 3:26 am
Nice one thanks.
That was my suspicion too.
Now I just need to uninstall 2005 to get Enterprise Manager working again lol...Will let you know how it goes.
I'm sure SSIS is great if everything is as designed...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy