Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Feb 2009
    Posts
    1

    Question Unanswered: Share your ETL approach

    I have some experience with DTS and have walked through some SSIS tutorials in my current data warehousing class. I have taken on the DBA role and want to revamp our whole ETL process so that the same genearal approach is used for all uploads of data into production. Right now, each upload has its own process usually with some analyst uploading data to a holding table on production SQL Server using Access! Then a variety of scripts are run in Management Studio. I'd love to hear from the trenches before I tackle this project. Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Very simple process. We BCP into unconstrained staging tables. Clean with T-SQL. Transfer to normalised, constrained database.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Bingo.
    Use your ETL tool for E, and for L, but not for T.
    Drop the data into a staging table with absolutely no constraints, so that it is nearly impossible for an error to occur at that point. Now, your debugging is limited to a single stored procedure where you verify the staging data, cleanse it, and distribute it to production tables.
    Toss an "ImportDate" column on the end of your staging table that defaults to the current date and time, and an "ImportSource" column for storing the name of the file from which the data was imported, and finally an "ImportError" column where your stored procedure can note any issues that prevented the data from being distributed to production tables. Then you have a robust process that can accept data from multiple sources, and maintenance and troubleshooting is pretty straight-forward.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    Quote Originally Posted by pootle flump
    Very simple process. We BCP into unconstrained staging tables. Clean with T-SQL. Transfer to normalised, constrained database.

    This is my approach as well. Various clients have different requirements with respect to data so it makes sense to upload, cleanse and push into production.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    what does ETL stand for?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    what does ETL stand for?
    ETL means Extract Transform Load. ETL tools allow you to pull data from some source, change its structure as needed, then load the resulting data into a destination.

    -PatP

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I was kinda hoping for a funnay answer for this "technology"
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    We are not here to entertain you, sir.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    Quote Originally Posted by blindman
    We are not here to entertain you, sir.
    And here I thought that was our mission.....Useful - not necessarily, humorous - always!

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    We aren't here to entertain others, only ourselves.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...in the privacy of your own home, I hope.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    Quote Originally Posted by blindman
    ...in the privacy of your own home, I hope.
    Now where's the fun in that??

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Exactly - prude!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by pootle flump
    Very simple process. We BCP into unconstrained staging tables. Clean with T-SQL. Transfer to normalised, constrained database.

    that would be Stage then Transform, my preferred method, it's old school but safe and easier to understand and troubleshoot.

  15. #15
    Join Date
    Oct 2009
    Posts
    1

    Vigilance Xpress web-based ETL ?

    Can anybody who has used GSS Group's web-based ETL software Vigilance Xpress post some feedback on their experience with it, as i'm doing trying to narrow down best ETL choice for my employer. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •