3

I have an SSIS package that queries an Analysis services database and writes the results to Azure Blob Storage as a csv file.

Package is very simple as below. My issue is that NULL values are represented as "\N" and I need them to be BLANK.

SSIS Data Flow

There does not appear to be a NULL handling property in the Azure Blob Destination data flow component. Am I missing something / is there a way to change how this component handles NULL values?

Here is the query:

EVALUATE
VAR Customers_csv =
    SELECTCOLUMNS (
        Customers,
        "CustomerID", Customers[CustomerID],
        "State", Customers[State]
    )
RETURN
    Customers_csv

And here is the csv output. The third record is NULL.

CustomerID,State
637099,Kentucky
316102,Kentucky
535357,\N
733735,Kentucky
Hadi
  • 36,233
  • 13
  • 65
  • 124
datalore
  • 31
  • 2
  • Analysis Services in OLAP mode do not support nulls, not sure about tabular. "\N" looks like a value in your dimension. You can add expression in data flow to replace "\N" with "blank". – Piotr Palka May 17 '19 at 21:52
  • Thanks for the reply Piotr. The \N is only in the SSIS csv file output, it is not present in the AAS cube. Additionally, when I execute the DAX in DAX Studio the "blank" values are indeed blank. The issue is only when SSIS writes the csv file to Azure Blob Storage. – datalore May 21 '19 at 01:50

1 Answers1

0

You can simply add a Derived Column Transformation with the following expression:

REPLACE(REPLACENULL([State],""),"\\N","")

This will replace Nulls and \N values with a blank string.

Update 1

After searching, it looks like \N notation is used to denote NULL value. Check the following links for some workaround:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    Thank you for your answer Hadi but this did not work. I added a Derived Column transformation in between the OLE DB Source and Azure Blob Destination with the following expression: REPLACE(REPLACENULL([State],""),"\\N",""). However, the file still created \N for the NULL/Blank records. – datalore May 20 '19 at 14:53
  • @datalore i updated my answer, check the links i provided – Hadi May 21 '19 at 00:51
  • thank you for providing those links. It appears they only apply to Azure Data Factory and not SSIS. – datalore May 21 '19 at 01:59
  • 1
    @datalore i dont think you can do anything in SSIS – Hadi May 21 '19 at 02:20