Need help for SSIS design

• scziege

  SSC Veteran

  Points: 247

  More actions

  March 17, 2011 at 12:24 am

  #234314

  Hi to All,

  I'm going to build a datawarehouse solution and I need some good hints for the

  design.

  This is the scenario.

  1. Someone can upload a file with a special format. The upload is done over

  a web page.

  2. The uploaded data is inserted into a staging table with all columns as a type

  nvarchar(255)

  For example a sample table:

  COLUMN_NAME

  FileId

  LineNumber

  DistributorCode

  CustomerCode

  CustomerSalesTaxNumber

  CustomerName

  CustomerAddress1

  CustomerPostCode

  CustomerCity

  CustomerRegion

  CustomerLocationCode

  CustomerCountry

  CommercialDenomination

  StartValidityDate

  3. After that all the fields in the staging table should be validated against some rules.

  - Is this the right datatype

  - Is the length of the field correct

  - Some fields need padding or truncation.

  - Some fields need a lookup

  And all of this should be customizable.

  4. Add the end of the day the data should be validatet against all rules and the correct

  data should be included in a table with the right datatypes, field length, foreign keys

  etc.

  I want to take SSIS for this design. And now I want to know how it is possible to

  customize the validation rules and to use the customized validation rules in the

  ssis packages.

  Hope the description is clear

  Regards

  Thorsten

• Elliott Whitlow

  SSC Guru

  Points: 102402

  More actions

  March 17, 2011 at 1:01 am

  #1299809

  Please don't cross post..

  Please continue the discussion at:

  http://www.sqlservercentral.com/Forums/Topic1079481-148-1.aspx

  CEWII