none
Conversion of Blank value to Big INT in SSIS

    Question

  • Hi,

    I have a question related to SSIS migration of data from Notepad to SQL database.

    Scenario  : Dumping Blank data from Notepad to SQL Column (DataType Bigint).

    Description

    • I have data in Notepad, which contains two column say Column A and Column B.
    •  I have a SQL table having Column A and Column B, column B is having  datatype Bigint .

    In Notepad, Column B having string of length 18 and can have BLANK value as well.

    So in order to perform the same, i have created the SSIS package to dump the data from Notepad to SQL table.

     I have used "Ragged Right " format  in SSIS package as a input for notepad (Flat File) and manually set the datatype of Column B to "Eight Byte Signed Integer [DT_I8]" and length to 18 for inputs .

    Things are going good until Column B (Notepad) having data in it, but my SSIS package got failed once Notepad  Column B having Blank value in it and i am getting below mention error

    Error: Data conversion failed. The data conversion for column "column B" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

    Also i do not want to include any add on with in the package (Data Flow)  to deal the blank value in  like Derived Column .

    Hoping for quick answer.


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/
    Friday, November 25, 2011 11:06 AM

Answers

  • Well, you could use sth like this

    INSERT TargetTable (TargetColumn)

    SELECT CONVERT(BIGINT, CASE WHEN LEN(SourceColumnn) = 0 THEN NULL ELSE RTRIM(LTRIM(SourceColumn)) END) AS TargetColumn

    FROM StagingTable

    • Marked as answer by Eileen Zhao Friday, December 02, 2011 8:28 AM
    Thursday, December 01, 2011 1:48 PM

All replies

  • As flat file source doesn't allow writing query for querying the data (similar to excel source does), I am not sure you would be able to handle empty strings while reading the data from the file itself.

    You might need to somehow use derived transformation for handling this case and you may want use some ternary operator expression.

    Friday, November 25, 2011 11:44 AM
  • Hi,

    can BLANK value like space be represent as a BigInt ?? is this a valid Bigint value ??

    No

    thats why you are getting error because you have defined datatype of column  as Bigint it does not contain valid BigInt value ..

    so if you want to insert blank values into database change datatype to String into flat file connection manager

    place a expression in derived column to check column value if its blank then convert to BigINT NULL and insert into DB.

     


     


    Shailesh, Please mark the post as answered if it answers your question.
    Friday, November 25, 2011 11:45 AM
  • Hi Deepak and Shailash,

    I know BLANK value can not be converted into BIGINT by itself, but i am not in favour of using any derived component because inclusion of Derived component will increase execution time of package as i have 30 such column and 50 tables, hence it will degrade performance.

    I HAVE ONE STREAGY IN MY MIND :

    Is it good to use column having datatype varchar in a staging table and then i dumped the data from this table to main table after converting the same to bigint.

    Please suggest on the same.

     

     


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/
    Tuesday, November 29, 2011 9:41 AM
  • No need to use stage table.

    Flat file source having one option “Retain null value from the source as null value in data flow”

    Enable that property shown bellow then data conversion as bigint will work.

    Tuesday, November 29, 2011 9:53 AM
  • Hi Deepak and Shailash,

    I know BLANK value can not be converted into BIGINT by itself, but i am not in favour of using any derived component because inclusion of Derived component will increase execution time of package as i have 30 such column and 50 tables, hence it will degrade performance.

    I HAVE ONE STREAGY IN MY MIND :

    Is it good to use column having datatype varchar in a staging table and then i dumped the data from this table to main table after converting the same to bigint.

    Please suggest on the same.

     

     


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    Hi Amit,

    Yes you can use a staging Table and defined data type string in for same field.

    i would recommended to use bulk insert task to insert into staging table if data volume is High.

    and you need to define an expression to convert blank values to some predefined values of type BigInt while inserting  from

    staging to Main Table.

     

     


    Shailesh, Please mark the post as answered if it answers your question.
    Tuesday, November 29, 2011 10:20 AM
  • Hi Kiran,

    Thanks for your time, but my concern is not relative to the NULL values, it is something relative to the BLANK value, also i already use the way to proposed but it will not give any life to the progress of Package life.


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/
    Tuesday, November 29, 2011 10:22 AM
  • Hi Amit,

    there are several pros and cons for using staging area/staging tables to populate the DWH. If conversion of blanks is the only reason for using a staging table...  well, personally i would prefer to do the conversion in the data flow. If you skip this conversion in the data flow, then you'll have to do it in the next ETL step when loading data from staging into DWH. At the end there will be no performance increase. Btw the Derived Column is an in-memory row transformation and should perform very fast.

    Tuesday, November 29, 2011 11:22 AM
  • We can achieve with

    1. Stage table (or)
    2. Using two data flow tasks.

    Using two data flow tasks:

    In first data flow task use conditional split to avoid blank records, and load remaining records.

    In second data flow task use conditional split to extract only blank records and append to target. While the time of target mapping “ignore” the field which having blanks.

    Tuesday, November 29, 2011 12:00 PM
  • Hi Stanhe,

    Sorry to say, as per client instruction i would not use Derived Column in SSIS package, hence i must prefer Stagging Table and convert the datatype with the help of script into the main database.

    Please suggest.


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/
    Thursday, December 01, 2011 10:00 AM
  • Well, you could use sth like this

    INSERT TargetTable (TargetColumn)

    SELECT CONVERT(BIGINT, CASE WHEN LEN(SourceColumnn) = 0 THEN NULL ELSE RTRIM(LTRIM(SourceColumn)) END) AS TargetColumn

    FROM StagingTable

    • Marked as answer by Eileen Zhao Friday, December 02, 2011 8:28 AM
    Thursday, December 01, 2011 1:48 PM