Question

Ben Hatton on Wed, 15 Mar 2017 07:56:36


Hi,

I am reading from Oracle table source, writing to Azure Blob sink with json format.

I would like to output a native json boolean value, e.g. 

{ "isTrue": true }

Oracle typically uses 'Y' 'N' characters in place of boolean.

Any ideas on how such a conversion might be done without custom activities?  I've tried specifying "type":"Boolean" on both source and sink datasets.  I don't think I understand fully how type conversion takes place in DF.

Cheers,

Ben.


Sponsored



Replies

Bud_Spencer on Thu, 16 Mar 2017 09:42:33


hello,

if you dont know how esactly convert the values to bool, try to load all data in a simple table (all varchar 500) and then make a SP in SQL to clean and perform the data and move to finally table.

A greetings.

Ben Hatton on Thu, 16 Mar 2017 23:43:30


Hi,

It looks like the Copy Activity does not refer to the data type in the sink dataset structure.

I have made a .net custom activity for pushing records to an Azure Servicebus Queue, and in this I do include code that looks at the sink dataset structure, and provides a conversion when necessary:

foreach ( DataElement de in outputDataset.Properties.Structure) {
    JValue btoken = data.SelectToken(de.Name) as JValue;
    if (btoken != null)
        switch (de.Type.ToLower()) {
            case "boolean":
                btoken.Replace(new JValue(Convert.ToBoolean(btoken.Value)));
                break;
            case "number":
                btoken.Replace(new JValue(Convert.ToDouble(btoken.Value)));
                break;
         }
}

In this case, the input needs to be in the format recognised by Convert.ToBoolean - i.e. "True" or "False" - which means my oracle query needs to covert "Y" into "True".

Hope this helps someone else, it would be great if this kind of conversion was built into the copy activity.

Regards,

Ben.