Datamart is not refreshing because of changed datatype in Dataflow

[Tafkat]

Hi All,

I created a datamart with 1 source (a dataflow) to see how it works 🙂 . After changing the datatype in the dataflow my datamart is not refreshing anymore. Its ginving the following error:

Local Sql execution failed. Message=ALTER TABLE SWITCH statement failed because column ... has data type bigint in source table which is different from its type bit in target table. 

When I delete the table from the datamart and add it back again tha datamart is refreshing correctly.

But I dont want to do this everytime sominthing is changing in the dataflow. 

Thanks for the help! 

[otravers]

It worked the first time.

[vanessafvg]

have you refreshed the metadata of the dataflow when you make this change ie go into the dataflow and do a refresh of the data in the preview window.  if the metadata changes you need to update it.

[Tafkat]

Hi @vanessafvg,

Its still not working 😞 I did the following steps:

1 Changed the datatype in the dataflow.

2 Did a full refresh of the dataflow

3 Went towards the datamart, when I open the table its still showing the old datatype

4 In the datamart I clicked on the edit button of the changed table when it opens its showing the new datatype.

5. I refreshed the data in the edit window and saved the power Query

6. Then I get the following message:

[otravers]

I tried to replicate this:

- Like you at stage 3, I saw that the datamart table had not reflected the dataflow datatype change from int to logical.

- However when I edited then saved the table in the datamart, the refresh completed successfully and the data type is now true/false in the datamart too.

I've run into many transient issues with datamarts so far, do you run into the same error even after repeatedly trying the edit/save dance in the datamart?

[Tafkat]

Hi @otravers,

As you said you run into many transient issues with datamarts. Do I need to report this as a bug or something? Im quite new in this community 🙂

Thanks for your help!

[otravers]

Since datamarts are in preview, and the purpose of the preview phase is to catch such bugs, I assume Microsoft is tracking these error conditions in the service already.

[Tafkat]

Hi @otravers ,

After clicking Edit table (after stage 3) the true/false data type is showing up. I didnt changed anything in the edit mode I only clicked on the save button. and then im getting the following message:

Did you do the edit and save dance a couple of times or did it succeed already the first time?

Thanks for your time! 

[vanessafvg]

can you post the fulll errror message?

[Tafkat]

InternalError

ErrorMessage

Local Sql execution failed. Message=ALTER TABLE SWITCH statement failed because column … has data type bigint in source table …. which is different from its type bit in target table …

HttpStatusCode 500

[vanessafvg]

thats definitely a mismatch, what are the datatypes of those columns?

[Tafkat]

Hi @vanessafvg,

Data Flow

The datatype in the dataflow changed from 123 Whole number towards Boolean data type.

Datamart

The datatype in the datamart is still 123 Whole number

Edit screen Datamart

datatype in the Edit table datamart is changed towards Boolean data type.

After saving the edit table in datamart its giving the below error:

When I delete the table from the datamart and add the table back in again then the problem is solved. But this is not working for the future (with 1 test table its not a problem 🙂 )