SSIS Error: Cannot convert unicode and non-unicode string data types

At the time of data migration or at any situation you may need to add new column and assign fix value to the column. After you have added the column and put the static/fixed value, then you may receiving the error: "Cannot convert unicode and non-unicode string data types"

 Let's make an example; business delivered excel file with all data from legacy system which is already closed; and  you need to add the old data to your data warehouse. Loading data from excel file to a staging table;you built the package as like below figure 1, where you have added data flow component 'Excel file source' and you have 'derived column' to add new column and assign a hard coded value.

After you design it found the red error like below that you may hate.

fig 1: SSIS package migrated data

Well, let's look it closer and you can see the error saying  as like below fig 2: "Cannot convert unicode and non-unicode string data types". But why we see this error?

Fig 2: Unicode and non Unicode miss matched

We made a new column by using derived column component called 'Status'  and we set 'Completed' for the column Status which is extracting from the excel since all extracted data are old and transactions are completed so we set status as completed. However, the static string 'Completed' set as data type Unicode String [DT_WSTR] in SSIS which has issue with the target table column 'Status' data type. The  staging table has staus column as varchar which cause the issue here.

Fig 3: Data Type issue

If you look at the above figure 3, where string "Completed" is set under the Expression so DataType has been picked as [DT_WSTR] which is Nvarchar means Unicode. However, the staging table we have created in the database, the particular column is defined as Varchar which is DT_STR for SSIS means non Unicode. So it's clearly difference between [DT_WSTR] and [DT_STR] in SSIS.

How to solve it?

To solve the miss match of Unicode and Non Unicode issue, we need to convert unicode data type to Non Unicode. So here goes the conversion expression :(DT_STR,17,1252) "Completed". "Completed" string is considered as Non Unicode/Varchar data type. 

Fig 4: Solve issue with data conversion

If we explain the expression (from Fig 4),  where (DT_STR,17,1252) -> (DataType, length of the data, Code page), Code page 1252 means it's windows Latin 1 (ANSI).

After you add the above expression the error will disappear and you can run the package to load the data.

SSIS: Loading Flat file and issue with locale settings

Working with Flat file source in SSIS is common task and we all have experience with that. However, sometime it get tricky when it comes to localization. Some countries uses comma (,) and some countries uses dot (.) as decimal point for numerical field in the table.

If your data include decimal places  with dot (.) and you use the locale as any of the Nordic country such as Denmark/Norway/Sweden, then you are screwed. Since these countries use their decimal places as comma (,). I am going to explain a scenario where faced similar issue.

Got a file from business where a column in the file was numeric, and e.g. data look like below:

Budget	Amount
Food	229.1200
Cloth	8.4100
Travel	12.1500
Apartment Rent	271.2800

 Created the SSIS package:

Fig 1: SSIS package for loading data from Flat file

When I made the package did not look carefully the numerical field, so put the locale as Danish. If you open the connection manager you will find window like below:

Fig 2: Setting the locale

As you can see from the above Figure (Fig 2) I put Danish(Denmark) as locale, so after loading the data from the flat file to the staging table I only can see wrong data is inserted. After spending some time found the locale I have set it will make mess with dot (.) decimal place.

It means either I have to change the file replacing dot (.) with comma (,) or I can change the locale setting with English (United States). Offcourse, you will find more easy to change the local setting.

Hurrah!! Now you got correct result at your staging table.

               

How to handle NULL in SSAS Tabular by using DAX

As new in DAX area, I found a few useful DAX function that can ease your BI life while you are working with SSAS Tabular model. To handle null values and undefined values were challenging at the begining, however: found two useful function called ISBLANK and IF that you can use to handle those challenges.

Let's discuss with example: a table named Dim_Account which has data like below (In real life you may not have this type of data, however, you get problem when your data is corrupted)

Fig 1: Account data (corrupted)

Now I would like to count number of rows from the above dataset where AccNo is not NULL,and AccTypeName='Savings'

So the DAX look like: COUNTAX(FILTER(Dim_Account;[AccTypeName]="Savings");[AccNo]))

Here, COUNTAX calculate only non empty rows so I got number of row return =1; however; I would like see how many rows also have NULL, and would like to replace the NULL value with 0.

How do you do? There is a function in DAX which is ISNULL. So lets handle the NULL value in DAX:

IF(ISBLANK(COUNTAX(FILTER(Dim_Account;[AccTypeName]="Savings");[AccNo]));0;COUNTAX(FILTER(Dim_Account;[AccTypeName]="Savings");[AccNo])))

If you are coming from SQL background then surely you say NULL and BLANK are not same, they are completely different.. However; here is the reference from Microsoft DAX:

"A blank is a data type in DAX that represents and replaces SQL nulls. You can create a blank by using the BLANK function, and test for blanks by using the logical function, ISBLANK."


However, Lets look into the details regarding function IF(ISBLANK(A);0;A), it means if A is NULL then return 0 and all other cases get the value of A.

At the same way, If we explain DAX for accountype NULL handles:

Fig 2. DAX Null handles

From the above fig 2: first part of the DAX query is traverse through each row of the table and find if any NULL value and then replace that with 0 and the last part is execute if first part is FALSE, it means get all the value than NULL.

Missing Report Data Pane in SSRS

One of the reporting tool is used in BI called SSRS.  And when you create a SSRS project by using SQL Server Data Tools, the most important pane you use to create report is ReportData. Literally, you can't make SSRS report without using Report Data Pane, Now think about suddenly you can't find Report Data pane which was just there before. How you gonna find that?

This post will cover how to find report data when you just miss it.

When you create SSRS project, you will find report data pane under view as like below screenshot.

Fig 1: Report Data in Visual studio

And the report data pane look like below:

Fig 2: Report Data Pane

Now you start making report as like below figure:

Fig 3: Design the report

After working a little bit then you may suddenly don't find the Report Data Pane under 'View', And list under 'view' menu will look like below:

Fig 4: missing Report Data

Now you  are puzzled, how come the pane is not there??  How to get it back:

The reason for missing the 'Report Data' is you must click some where else than the design layout; you may click at the project file (as like below diagram)

Fig: Click at the right place

As long as you click at the design layout then you will find the 'Report Data' pane.

Exporting data as flat file by SSIS: Step by step guideline

Exporting data from database and convert that into comma seperated file(.csv) is pretty straight forward and easy task by using SSIS,

As an example, I need to export ResellerInfo from AdventureWorksDW2012 and generate .csv file by using SSIS.

In SSIS Toolbox you have transformations called a) Data flow Task b) OLE DB source and c) Flat File Destination , to complete the excercise we need those two transformations and one Task.

Fig 1: SSIS Toolbox (Data flow Task)

Fig 2: SSIS Toolbox

In OLE DB Source you should write your SQL query to populate the result, in this case, I have below SQL:   SELECT

       [ResellerName]

      ,[NumberEmployees]

      ,[OrderFrequency]

      ,[OrderMonth]

      ,[FirstOrderYear]

      ,[LastOrderYear]

      ,[ProductLine]

      ,[AddressLine1]

      ,[BankName]

      ,[MinPaymentType]

      ,[YearOpened]

  FROM [AdventureWorksDW2012].[dbo].[DimReseller]

 

At your package you need drag a data flow task and then double click the data flow task, now under the data flow drag and drop the two transformations OLE DB Data source and Flat File Destination.

Fig 3: Under Data Flow task

In the above design, 'Populate Data' is a dataflow task and 'destination CSV' is Flat file destination transformation.

While we connected from 'Populate data' to the 'Destination CSV', we had to configure the 'Destination CSV' which is Destination Flat file Transformation. Destination Flat File configuration must connect with Flat file connection manager as like below:

Fig 4: Flat file destination is conneted with Flat file connection manager

Flat File connection manager should include, connection manager name, file destination and name at least. You can have options to choose from different delimeter like comma, semicolon etc.

Fig 5: Configure flat file connection

Now, from your package you can run the data flow task "Generate CSV for Reseller" by right click.

Fig 6: Execute Data flow task

and then you should have the .csv under the location that you have already put in the flat file connection manager.

Fig 7: CSV result set at the specified location

SSIS Tricks: Control executing the next SSIS tasks

Your SSIS package may requrie to have control that will make decision if the next SSIS tasks will execute or not. I have an example where 'Data Flow Task' for loading the fact table will start if 'Execute SQL  Task' return TRUE.

Here as an example; I have a sequence container that look like below:

Fig 1: Execute SQL task and  Data flow task

Let's look at the code in SQL Execute task named "Control Fact Load":

Fig 2: SQL query for SQL Execute Task

The SQL code in the above diagram:

SELECT case when max([TimeKey])<convert(varchar(8),getdate(),112)

                      Then 'Y' 

                    Else 'N'

         END  as GoToNextStep

                FROM [dbo].[Fact_XXX]

The SQL code in the above diagram intend to find if there is any Date exist in the fact table bigger than today. It means if the fact table's date "TimeKey" is less than today's date then we retutn 'Y' ; so, we did not load any data for today yet. And if today's date is already exist it means data is uploaded to the fact already then we return 'N' hence next SSIS task will not execute.

The return value of the SQL need to save in a variable. So user variable GoToNextStep is created and assigned the value after the Execute SQL task as like below:

Fig 4: User variable created

And then assigned the value after SQL execute task:

Fig 5: Binding value to the variable

The way we control between two tasks is precedence constraint.

Fig 6: Prcedence constraint editior

You need to open the prcedence constraint editior (right click the connector between two tasks) and then set the value as like below:
Evaluation operation: Expression and Constraint
Value=Success
Expression: @[User::GoToNextStep]=="Y"

Now, Data Flow Task (Load Data To Target table) will only execute if prior Exceute SQL task (Control Fact Load) return "Y". If 'N' is returned via Exceute SQL task (Control Fact Load) then next data flow task (Load Data To Target table) will not execute at all.

How to atuomate SFTP file transfer in Windows

When it comes to loading file to the FTP server then we use different free FTP/SFTP client like WinSCP, FileZilla etc. The process of uploading files is completely manual in this way. If your business needs to upload files repeatedly then the process should be automated.

My writing will cover how this can be done in Microsoft windows.
For example you have a file called UploadMe.txt which is located in C:\temp. Now we would like to upload this file to the SFTP Server. Before we start using  script, let's see how we do in WinSCP.

Fig 1: WinSCP  User Interface

As we can see, we need following information to connect the FTP/SFTP server, I have put the information as an example:

Host name: ftp.myftp.com

Port number: 22

User bane: myuser

Password: Password

And when you are connected to the SFTP server then you drag files from your local machine and drop the files to the server.

Fig 2: Moving file from local PC to SFTP Server

How do we do the same thing by using script?

Step 1: You need to install WinSCP, so go to the : https://winscp.net/eng/download.php and download it.

       

Fig 3: Download WinSCP 

Step 2: Script for establishment of connection to the SFTP server and move the file from your machine to the Server. So the script should cover at least below:

a) Opening a connection the SFTP server, below syntax will open the connection:

open sftp://myuser:Password@ftp.myftp.com:22


b) Syntax for copying data from local machine to FTP server:
put -nopermissions -nopreservetime "C:\temp\test_data.txt"  test_script.txt

Lets make full script in one place and save the file as 'uploadscript.txt'

option echo off
option batch on
option confirm off
open sftp://myuser:Password@ftp.myftp.com:22
#Change LOCAL directory
lcd "C:\temp\"
#copy an individual file
put -nopermissions -nopreservetime "C:\temp\test_data.txt"  test_data.txt
exit

Before we run the above script through .bat file, we would like to test if we can connect with FTP server and load the file by using command windows.

Fig 4: windows Command line

Change the path to WinSCP so that we can use WinSCP resources:

Fig 5: Accessing WinSCP file

Run the script which will make connection with the SFTP Server and copy the file to the server.

Fig 6: Make connection and move file

As you can see from the above screenshot (fig 6), SFTP server connection is made and started loading the file.

However, above process is done through windows command line to see if we can establish connection and transfer file. Since we would like to make it automated so we need to make a batch file and run the script ('uploadscript.txt') that we just run through windows Command line. And last step is to schedule the task via windows Task Schedular, hurrah, now your file will be automatically upload to the SFTP server as schedule them.