1

I have SQl data cube. I want to export data to a relational data table from the cube. I tried to use SSIS data flow task to extract data. But there are several data conversion issues and some others(I used data conversion action but it doesnt solve my problem fully.)

Then I tried script task . I connect to the data cube and get the data .After that insert to the table. It took lot of time.

AdomdConnection conn = new AdomdConnection("Data Source=bci-bifsrv-01;Catalog=BInventoryAge");
        SqlConnection sqlConn = new SqlConnection(@"Data Source=.;Initial Catalog=Couriers;Integrated Security=True");

        conn.Open();
        string commText = @"--query--";

        AdomdCommand cmd = new AdomdCommand(commText, conn);
        cmd.CommandTimeout = 5000;
        AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

What is the best way to extract data from data cube

udaya726
  • 1,010
  • 6
  • 21
  • 41

1 Answers1

0

When using OLE DB Provider for Analysis Services 10.0 if you go to the data link properties add "Format=Tabular" to the "Extended Properties" field. This has been know to resolve similar issues.

Another option is to connect using ADO.NET instead of OLE DB.

If neither of these work a final option which may help is to convert all columns to DT_WSTR as you receive them from the cube, and then convert them again into their correct formats.

Hope this helps.

James Rosser
  • 404
  • 2
  • 6