Hi, my data flow sends mutiple excel spread sheets using foreach loop container to oledb destination...if i find any error records in a column , i want to modify the column value during run time so that i want to make sure im not stopping at any where at any cost...is that posible to insert a value instead of that error record of a column in run time...? im new to this SSIS...So could you explain me step by step? Thank you

You can validate the contents of a column using a derived column task in the data flow. First drag Derived Column component into your data flow Validate the data in the column using an expression the basic syntax you will need is this <column value>==<test value>? <TRUE Value>:<FALSE Value>. There are lots of other validations such as ISNUMERIC and LEN available and they all have prompts so shouldn't be too hard to set up. Lastly drop down the Derived Column box in the Editor window and select your input column as a "Replace" that way you will just get out the clean version of the column that went in. Tim

if we run the package locally we can do what ever the idea gives out clean solution...bit What i need is if i am about to execute the package in the customer side....it should not be stopped at any cost....i want to dynamically pass a value if i find any error in any column... For exapmle COLUMN NAME :Premise (integer) if it is having some character value in that in the source file...then during run time we must be able to modify that value by some default value automatically...assume that we are going to run the package in customer side.... Thanks

Prakash, The derived transformation / infact any transformation check the values in run time and perform respective functionality assigned. You can check the values during run time using a data viewer after the transformations.Happy to help! Thanks. Regards and good Wishes, Deepak. http://deepaksqlmsbusinessintelligence.blogspot.com/

Not sure what you want exactly, but it seems that using the SSIS Event handlers can help: http://www.simple-talk.com/sql/ssis/ssis-event-handlers-basics/Arthur My Blog

Hi Deepak, If we are about give the package to customer, we will not know that what error would come and in which column it would reside...so by any chance we must run the package without dropping a row..so we must have already handled those issues inside the package before giving the package to customer... so we need to modify the error value with appropriate default value programmatically or through some other way.....is that possible to do so? Thanks

Hi ArthurZ, My question is if we are about to give the package to the customer, they should not get any error issues...in customer side,we would not know what error would occur and in which column it would reside on when they run the package..so before we give the package, we must have already handled this issue inside the package so that if any column gives error it must be replaced by some default appropriate vaule through programmatically or by some other way....does my question make sense...? is that possible to do so? Thanks

I think you want to deal with the unexpeceted data in the excel and want to avoid failing of the package.. i suggestion to you will be to load the complete excel in a staging table and then do the validation on that table. While extracting assume all the data to be string. Once in the staing apply all the rules you want, one good thing with this is we will only skip the rows which have error and also can generate a report to show the actual value in excel vs expected values.

Hi Harshvai, Can u explain me with some examples...? Im not aware of what u said...please explaim me step by step process...Could you? Thanks

DOUBLE POST? http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/2db9bd92-40b2-45ca-9eea-10bacd87c2cf Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com