Converting a boolean value (True/False) into a numeric value (1 OR 0)

[VijayHP]

Hello all,

We have a requirement wherein we need to transfer contents of few tables (SQLServer) into an CSV file and then from the CSV file we need to insert into Oracle using sqlldr utility provided by oracle.

We used SSIS to transfer the contents of SQLTable(s) to CSV file. Now when we run the sqlldr utility to copy the contents of the CSV, we are facing few issues

1)There are 2 columns in the SQLServer tables having a value of true or false (Data type is bit). We need to convert this into numeric 1 (for True) and numeric 0 (For False).

2)We have a date column in the sql table. Most of the records have a format YYYY-MM-DD HH:MI24:SS. But there are few records which have a format YYYY-MM-DD HH:MI24:SS.SSSSSSSSS (milliseconds value is the difference).

Now I would like to know how we can do the data transformation using SSIS for the above mentioned scenarios 1) and 2). The transformation has to be done before the csv file is created.

Could you please assist us on this as we need the solution for this ASAP. Your assistance at the earliest would be greatly appreciated.

Thanks and Regards,
Vijay

 

[MDXer]

There are a few options as to where you can address this issue.

1) Source Query. Within your SQL source query you can convert the bit columnts to a type int and convert your dattime to a format the does not contain the milliseconds.

2) within the SSIS pipeline you can use a derived column task to convert your boolean columns to an int and output to a new column. You would have make sure this new column is mapped within your data destination. you would use something like MyColumn == TRUE ? 0 : 1. As for the date I still feel addressing this in the source query would be the easiest.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[VijayHP]

Hi Paul,

Thanks for the quick response.

However, I am not sure how I can do a conversion at the source level. I have used the OLE-DB source to connect to the SQL DB and selected a table. I dont see any provision here to do a conversion.

Also since I am very new to SSIS, I still couldn't understand how I can use the derived column to first change the column type and then change the value.

Basically I need the following

1) I have a column Plat_CTPDisplay which is of datatype Bit in the source SQLServer Database table. This has a value of True. This column has to be converted into numeric 1 (if the value is True and if it is False then a numeric 0).

Could you please explain the necessary things that I need to carry out in steps?

Thanks a lot in advance and apologies for the inconvenience caused.

Regards,
Vijay

 

[MDXer]

To Change things in your source query you would change the OLE DB Source "Data Accces Mode" to be "SQL Command" This option requires that you input your select statement.

In the derived column task you would not change the data type of your column as you can't do this, but rather add a new column with the required data type.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[VijayHP]

ok.

So if I have a column "Plat_CTPDisplay" (of data-type bit) having value as true. Then to convert this column to another data-type (numeric with value 1 for True) I add another column (rather than replacing this column) and then I do the following

Create a column and name it as Plat_CTPDisplay_New" of type NUMERIC and then in expression,give the following

Plat_CTPDisplay == True?1:0

Is the above the right expression OR do I need to modify this?

--Vijay

 

[MDXer]

looks right but SSIS will tell you if there is an issue.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[VijayHP]

Hi Paul,

Thanks for your responses. I have got this working using Derived column expressions.

Thanks again.,

Cheers,
Vijay