hey guys, I am trying to insert data from an input csv file to a sql database. my csv is an identic match with my database in amount columns and the naming of them, but i added 2 extra columns in my database with isvalid (default value = true) and a column errordescription ( default value = "" ) I am trying to input everything from my csv towards my database table, all my fields are nvarchar(64) in the flatfile i changed the data type to the unicode string so everything should work. Now my problem is I am trying to add validation to my dataflow task to check if the length is not > 64. What i want to do is when a field has a bigger value then 64 I want to replace this field with an empty string and update my last 2 columns Isvalid = false and errordescription = column x is bigger then 64. Is this possible in SSIS ? Also how can i check the length of my input CSV fields so my script would know this field > 64, transform it to an empty string. Thanks in advance, Kimmiez

Hello Kimmiez, We check for length of data using the Derived Column Transformation component available in a Data Flow Task (DCT). In the DCT you can set the column to be replaced and use a SSIS expression as LEN([ColumnX]) > 64 ? "" : [ColumnX] This is a conditional check for length, if exceeds 64 chars then emits an empty string, as requested. The next DCT after the above would be probing the value of [ColumnX] for the empty string in the similar fashion and so "update my last 2 columns Isvalid = false and errordescription = column x". How you do the above is probably this: set to replace the content of the column errordescription in another SSIS expression to the value of column X and assign ... here is the trick, there are no boolean in SQL Server, so if it is a bit 0 then put the 0 into Isvalid column.Arthur My Blog

Hi Arthur, I understand the first part with the LEN > 64 check, but I think I'm still doing something wrong here. So I connected my flat file source to a DCT and the DCT to a OLE DB Destination. Now the problem is in my flat file connection manager I put the width length on 64 in the advanced tab, So when I run my package it fails on first step and will not go to the DCT. For now i changed the width length on all fields to 4000 since the DCT will check if it is 64 anyway, is this the right way of doing this ? about the 2nd part for filling in my 2 extra Columns i only have in my table and not in my csv (IsValid, ErrorDescription), I am not 100 % sure what you mean ? So are you saying I should add a 2nd DCT after the first DCT (the one that checks the length) ? If so, I would just check every Column that has "" as value and then update last 2 columns (Isvalid = 0 , ErrorDescription = "Column x length was to long") ? Not 100 % sure on how to implement this, since I am still a newbie to SSIS! I appreciate your help already Kind regards, Kimmiez