SSIS Using Variables

• karthika9

  Valued Member

  Points: 55

  More actions

  October 22, 2009 at 8:20 am

  #216555

  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 TotalErrorRows

  InputRows Variable contains Total No of Input Rows

  OutputRows 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

• shanu.hoosen

  SSCrazy

  Points: 2227

  More actions

  October 23, 2009 at 3:33 am

  #1069875

  Hi,

  Not too clear on your question, but heres a possible solution.

  1.Import text file to SQL

  2.Create 3 variables, TotalRows, ValidatRows, InvalidRows

  declare @ValidRows float

  declare @InvalidRows float

  Declare @TotalRows float

  3. Assign values to variables to calculate error percentage

  set @ValidRows = (select count(*) from table where rows are valid)

  set @TotalRows = (select Count(*) from table)

  set @InvalidRows = (@TotalRows-@ValidRows)/@TotalRows

  4.Insert data only if error is less than 50%

  if @InvalidRows <= 0.5

  print 'error at '+ cast(@InvalidRows*100 as char(5)) +'% '+'Statement to insert data here'

  else

  print 'error at '+ cast(@InvalidRows*100 as char(5)) +'% '+'No data inserted'

  replace print with your insert statements.

  hope this helps