CODEWITHSUNDEEP
sqlssissql-server-2008-r2data-cube
udaya726
udaya726

Reputation: 1018

insert data to relational table from data cube

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

Upvotes: 1

Views: 890

Answers (1)

James Rosser
James Rosser

Reputation: 424

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.

Upvotes: 0

Related Questions