Post reply

SSIS - PITA

  • October 31, 2011 at 1:06 pm

    #245427

    I have a simple SSIS package that reads a SQL DB input file, runs thru a script and does some parsing using regex, then updates a different SQL table in the same DB.

    It ran for over 72 hours, to process 4 Million records, and your cant debug the script!

    I created a SQL CLR C# program, which you CAN debug, and it processed the whole 4 Million records, with the same regex and DB i/o in under 60 seconds!

    Why is SSIS such a dog ?

  • October 31, 2011 at 1:13 pm

    #1401265

    isuckatsql (10/31/2011)

    Why is SSIS such a dog ?

    Erm, because we took the watermelon for a walk and found Batman playing Hopscotch?

    Honestly, without knowing more details about how you applied the script component and what regex you were doing, I really can't answer. Not that I'm sure I could WITH details, but definately not as it stands. 😛 :w00t:

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • October 31, 2011 at 1:29 pm

    #1401273

    I have to agree with Craig. Also, SSIS isn't always the best tool for the job, as you may have discovered when transforming data between tables in the same database.

  • October 31, 2011 at 2:35 pm

    #1401309

    You found a case where SSIS is the wrong tool. You are doing transformations within the same database. If SSIS is installed on that same instance you could be running into memory contention. If across the wire, it depends on how the package was setup as to whether the data was being copied across the wire and then back.

    Using TSQL you could do the regex in the same database and have done it faster too.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • October 31, 2011 at 3:23 pm

    #1401335

    isuckatsql (10/31/2011)

    I have a simple SSIS package that reads a SQL DB input file, runs thru a script and does some parsing using regex, then updates a different SQL table in the same DB.

    It ran for over 72 hours, to process 4 Million records, and your cant debug the script!

    I created a SQL CLR C# program, which you CAN debug, and it processed the whole 4 Million records, with the same regex and DB i/o in under 60 seconds!

    Why is SSIS such a dog ?

    Heh... Because SSIS stands for...

    [font="Arial Black"]S[/font]ucking

    [font="Arial Black"]S[/font]ound

    [font="Arial Black"]I[/font]s

    [font="Arial Black"]S[/font]tandard

    :-):-D:-P;-):hehe:

    Just kidding. Truth is, "It Depends". I've seen people make SSIS sing and I've seen people with problems like you had... just like any other piece of software. I can't personally say one way or another, though, because I don't actually use it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 31, 2011 at 3:28 pm

    #1401340

    Jeff Moden (10/31/2011)

    isuckatsql (10/31/2011)

    I have a simple SSIS package that reads a SQL DB input file, runs thru a script and does some parsing using regex, then updates a different SQL table in the same DB.

    It ran for over 72 hours, to process 4 Million records, and your cant debug the script!

    I created a SQL CLR C# program, which you CAN debug, and it processed the whole 4 Million records, with the same regex and DB i/o in under 60 seconds!

    Why is SSIS such a dog ?

    Heh... Because SSIS stands for...

    [font="Arial Black"]S[/font]ucking

    [font="Arial Black"]S[/font]ound

    [font="Arial Black"]I[/font]s

    [font="Arial Black"]S[/font]tandard

    :-):-D:-P;-):hehe:

    Just kidding. Truth is, "It Depends". I've seen people make SSIS sing and I've seen people with problems like you had... just like any other piece of software. I can't personally say one way or another, though, because I don't actually use it.

    What it really comes down to is this; test, test, and test some more. Every tool has its place, you just have to be sure you are using the right tool for the job at hand. You wouldn't use a butter knife to cut a 2' x 4' would you? :hehe:

  • October 31, 2011 at 3:32 pm

    #1401342

    Lynn Pettis (10/31/2011)

    Jeff Moden (10/31/2011)

    isuckatsql (10/31/2011)

    I have a simple SSIS package that reads a SQL DB input file, runs thru a script and does some parsing using regex, then updates a different SQL table in the same DB.

    It ran for over 72 hours, to process 4 Million records, and your cant debug the script!

    I created a SQL CLR C# program, which you CAN debug, and it processed the whole 4 Million records, with the same regex and DB i/o in under 60 seconds!

    Why is SSIS such a dog ?

    Heh... Because SSIS stands for...

    [font="Arial Black"]S[/font]ucking

    [font="Arial Black"]S[/font]ound

    [font="Arial Black"]I[/font]s

    [font="Arial Black"]S[/font]tandard

    :-):-D:-P;-):hehe:

    Just kidding. Truth is, "It Depends". I've seen people make SSIS sing and I've seen people with problems like you had... just like any other piece of software. I can't personally say one way or another, though, because I don't actually use it.

    What it really comes down to is this; test, test, and test some more. Every tool has its place, you just have to be sure you are using the right tool for the job at hand. You wouldn't use a butter knife to cut a 2' x 4' would you? :hehe:

    Well, what is the 2'x4' made of? Balsa wood - then why not use a butter knife;-):-D

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • October 31, 2011 at 5:23 pm

    #1401365

    Just venting

  • October 31, 2011 at 7:13 pm

    #1401381

    Lynn Pettis (10/31/2011)

    You wouldn't use a butter knife to cut a 2' x 4' would you? :hehe:

    In the hands of an artisan, anything is possible.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 1, 2011 at 8:18 am

    #1401643

    isuckatsql (10/31/2011)

    Just venting

    We understand. 😎

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply