Trying to load Flat File to SQL Server. Number of columns in Flat file ProductID - two-byte signed integer [DT_I2] Name -Unicode string [DT_WSTR](50) ProductNumber-Unicode string [DT_WSTR](25) MakeFlag-Boolean [DT_BOOL] Getting issue with last column 'MakeFlag'. I have attached sample Flat file if some body wants to test it Error: 0xC02020A1 at Data Flow Task, Flat File Source [1]: Data conversion failed. The data conversion for column "MakeFlag" returned status value 2 and status text "The value could not be converted because of a potential loss of data.". Error: 0xC0209029 at Data Flow Task, Flat File Source [1]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "MakeFlag" (22)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "MakeFlag" (22)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. Error: 0xC0202092 at Data Flow Task, Flat File Source [1]: An error occurred while processing file "D:\Users\Administrator\Desktop\New Folder\IndiaIndia.txt" on data row 211. Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. Need to convert the boolean value to a 0 or 1 before importing it Used derived column to convert the boolean value to a 0 or 1 Expressions what i used in derived column for two columns MakeFlag and FinishedGoodsFlag MakeFlag ? (DT_BOOL)1 : (DT_BOOL)0 FinishedGoodsFlag ? (DT_BOOL)1 : (DT_BOOL)0 I get error messages as below.Any thing wrong with the expression i am using here.Please let me know.Please send me the correct expression Error: 0xC02020A1 at Data Flow Task, Flat File Source [1]: Data conversion failed. The data conversion for column "MakeFlag" returned status value 2 and status text "The value could not be converted because of a potential loss of data.". Error: 0xC0209029 at Data Flow Task, Flat File Source [1]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "MakeFlag" (22)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "MakeFlag" (22)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Hi, You cannot chage(convert) the datatype while reading source. if you wish to change the column datatype as bool means, it will always expects a value of "0" or "1".... I will suggest to read the column as is and later use a derived column to update the value as 0 or 1 and , after that use the dataconversion component to change datatype of the coulmn to bool and map it to destination... check the below screen :-)

Hi, You cannot chage(convert) the datatype while reading source. if you wish to change the column datatype as bool means, it will always expects a value of "0" or "1".... I will suggest to read the column as is and later use a derived column to update the value as 0 or 1 and , after that use the dataconversion component to change datatype of the coulmn to bool and map it to destination... check the below screen :-)

or, as alternative, do it in one step: Don't replace the column in the Derived Column Transformation, but create a new one [bool] == "a" ? true : false Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

or do it in one step: Don't replace the column in the Derived Column Transformation, but create a new one [bool] == "a" ? true : falsePlease mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

yeah i agree , if you sure original column not required any more means , then replace or create new column.. like SSISJoost suggested..

[MakeFlag] Columns contains both 0 and 1 when i use expression as (MakeFlag == "a" ? "0" : "1"') it inserts only 1s(Ones) for the entire MakeFlag column when i use expression as (MakeFlag=="a" ? true : false) it inserts only 0s(Zeros) for the entire MakeFlag column Please find the test data that have used import rows from flat file to SQL Server ProductID,Name,ProductNumber,MakeFlag 1,Adjustable Race,AR-5381,False 2,Bearing Ball,BA-8327,False 3,BB Ball Bearing,BE-2349,True 4,Headset Ball Bearings,BE-2908,False 316,Blade,BL-2036,True 317,LL Crankarm,CA-5965,False 318,ML Crankarm,CA-6738,False 319,HL Crankarm,CA-7457,False 320,Chainring Bolts,CB-2903,False 321,Chainring Nut,CN-6137,False 322,Chainring,CR-7833,False 323,Crown Race,CR-9981,False 324,Chain Stays,CS-2812,True 325,Decal 1,DC-8732,False 326,Decal 2,DC-9824,False 327,Down Tube,DT-2377,True 328,Mountain End Caps,EC-M092,True Smash126

[MakeFlag] Columns contains both 0 and 1 when i use expression as (MakeFlag == "a" ? "0" : "1"') it inserts only 1s(Ones) for the entire MakeFlag column when i use expression as (MakeFlag=="a" ? true : false) it inserts only 0s(Zeros) for the entire MakeFlag column Please find the test data that have used import rows from flat file to SQL Server ProductID,Name,ProductNumber,MakeFlag 1,Adjustable Race,AR-5381,False 2,Bearing Ball,BA-8327,False 3,BB Ball Bearing,BE-2349,True 4,Headset Ball Bearings,BE-2908,False 316,Blade,BL-2036,True 317,LL Crankarm,CA-5965,False 318,ML Crankarm,CA-6738,False 319,HL Crankarm,CA-7457,False 320,Chainring Bolts,CB-2903,False 321,Chainring Nut,CN-6137,False 322,Chainring,CR-7833,False 323,Crown Race,CR-9981,False 324,Chain Stays,CS-2812,True 325,Decal 1,DC-8732,False 326,Decal 2,DC-9824,False 327,Down Tube,DT-2377,True 328,Mountain End Caps,EC-M092,True Smash126 What's the value of MakeFlag in the source? If it's 0 and 1, change it to something like MakeFlag=="1" ? true : falsePlease mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Try Some thing like this,, MakeFlag=="True"? "0":"1"

The values for the source column are True and False The values for the destination column are 1 and 0 I have used Expression given by you in Derived Column MakeFlag=="1" ? true : false It has inserted only zeros in the destination column

Thanks guys for the help you have given to me Kingxxx1 i have tried with the solution by you MakeFlag=="True"? "0":"1" Package execution is sucessful but in place of 0 it has inserted 1 and in place of 1 it has inserted 0 If you change a little bit in the expression it will work.If i change True to False it will work MakeFlag=="False"? "0":"1" Again thanks to Kingxxx1, SSISJoostSmash126

whether it worked, if yes please vote as helpful and make it as answer..

Yes it worked. Helpful:) Smash126