Hi guys! I'm new here! Is there a way to create an ETL package that gets data from a flat file, puts it in a fact table and then it creates a cube based on the data without user intervention? So the package will generate automatically a cube that can be used in SSAS? I am sorry if I did not make any sense. Thanks!

So you want to instigate SSAS design-time procedures from the SSIS-runtime? In theory its possible because the SSAS Designer is simple a wrapper to an API - so there's no reason why you can't call that API from elsewhere. Essentially all you need to do is build an XMLA script which is the definition of your cube which is basically what SSAS Designer does, except with a GUI. If you really really want to do this (and I don't recommend it) then you should try the SSAS forum. -Jamie

Why is building an XMLA script in SSIS runtime is not recommended? Also, what control flow item do you use in SSIS to run/create an XMLA script? Thanks again!

lloydsantos wrote:Why is building an XMLA script in SSIS runtime is not recommended? Also, what control flow item do you use in SSIS to run/create an XMLA script?Thanks again! I didn't say there was a problem with building XMLA scripts. I said there was a problem with buildinig a cube in SSIS - better to use the real designers I would have thought. Still, you know your requirements better than I do!!! There is no out-of-the-box task that allows you to do it. You'd have to use a script task. I've actually got a blog post appearing sometime next week that will give an example of building an XMLA script in SSIS that may interest you (I just have to write it that's all ). -Jamie

Alright. What I've been doing is just figuring out if that is possible. I'm doing a technical investigation; it's not yet part of requirements yet. Ok, I'll be checking up your blog for that post. Thank you very much!

Hello, We are in a similar situation at my client... we wish to issue the CREATE and DELETE XMLA scripts for a specific cube via SSIS, however are unsure as to the control object to use... More info regarding our processes: OUR SSIS flow is designed to: 1.) backup an existing cube 2.) create a new cube leaving the original in place for users to use while the new one is building 3.) once the newly built cube has been validated, drop the orignal cube, and rename the newly builtcube back to the original cube's name We used DDL code files (DDL Task Objects within SSIS)which contain the XMLA query code for each CREATE, DELETE, ALTER statementsused for each specific task: The flow: ------------------------------------------------------------------------------ 1.) BACKUP Original_Cube 2.) CREATE New_Cube 3.) PROCESS New Cube 4.) DELETE Original Cube 5.) ALTER New_Cube to Original Cube ------------------------------------------------------------------------------ In order to execute each DDL Taskcode set for a specific task, it is necessary to specify a connection to a given catalogue... however, in ourcase, and in the general case of usingCREATE/DELETE/ALTER ddls, a given database & catalogue may or may not always be available... it would be ideal for there to be a way to issue an XMLA script to CREATE a DUMMY CUBE (outline and model only) for all other DDLs to use as a basis (at the beginning of our SSIS Flow), and then issue another XMLA script to DELETE the DUMMY cube when it is no longer needed (at the end of our SSIS Flow) Currently, the flow does work fine, however, the DUMMY CUBE must exist on the Server in order to provide a connection for each DDL Task... at this point, the creation of this DUMMY CUBE has to be a manual process, we are looking to automate this.... the "Script Task" object bumps you into a VB window asking you to provide a VB script... is there a way to have VB issue an XMLA command...? Or, is there an easier way around this...? THANK YOU! Michael

Not sure if this will work, but you might try setting the DelayValidation property to TRUE on the connection managers and Execute DDL tasks that you are using, and see if that helps.

please follow this link as your guide http://msdn.microsoft.com/en-us/library/ms169917.aspx regards mona