Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
karthika9
Starting Member
8 Posts |
 Posted - 2009-10-22 : 10:25:42
|
| I have a scenario where I am struck.....I get Data in the form of Text Files and I have to dump them in SQL Sever using SSIS. Now In the SSIS Package I am doing various data validations according to teh business requirement. BUt the problem is during the data validation teh rows which do not satisfy the rules need to be redirected into another text file and Now I have to check How much percentage of the whole file has error row(the rows whch does not satisfy the data validation rules and if they exceed 50% of teh whole file then that file is rejected other wise it is accepted. To implement this I started with a variables, I took three variabes InputErrorRows, OutputErrorRows and TotalErrorRowsInputRows Variable contains Total No of Input RowsOutputRows Variable contains the Final No of Output Rows after the data vadation Amount TotalError Rows should be InputRows - OutputRows and Using this TotalErrorRows Variable I need status the Input file as Accepted or Rejected. But I am not abe to move further from decalring thsi variables I have no clue how to implement them to get the required output. Please help me out IF any one has any other way to do this it would be gr8. Thanks In Advance |
|
|
gaauspawcscwcj
Starting Member
29 Posts |
Posted - 2009-10-23 : 00:19:39
|
| u can use Script Task to do this.1. read input file2. loop and validate each data following u business3. if false --> set to an object or write to error file and increate error variable in 1 4. if OK --> set to an object or write to Okie file and increate OKie variable in 1 5. check percentage of the whole file base on error variable, Okie variable and total variable 6. if OK --> ...7. if Not good --> ...gaauspawcscwcj |
 |
|
|
smeeluv
Starting Member
20 Posts |
Posted - 2009-10-26 : 17:04:09
|
| I would keep it simple and dump the text files to staging tables. Run your business rules and any fallouts go to an error table. After all the records run through, then run your counts based on the staging and error table, if less than 50%, post to production. Then if you need to send a dump of the errors out, just write to another flat file. If you need more detail, just let me know!? |
|
|
|
|
|
|
SSIS USing Variables