• @Lemptster,

    Outstanding! Thank you for the time you took on that. I especially appreciate the methods you talked about for the promotion of packages and projects.

    Shifting to the subject of "EL" v.s. "ETL" and to answer your questions...

    That single subject may be the reason why I've found no particular use for SSIS. Most of the companies that I've worked for haven't allowed direct communication between servers or server-to-server communications through applications (and, SSIS is an application). As a result, nearly all of the data that I've had to import or export has been in the form of one text file or another or via (ugh!) spreadsheets.

    There have been times for me (working in such a place now) where server-to-server communications have been allowed and neither DTS (in the early days) or SSIS (later on, of course) were allowed by the companies because they didn't want to take the time to set it up, expand their security footprint, or increase what they've called the "tower of babel" especially since it's "known" that both typically require scripting in another language (I've reworked a couple of major DTS packages that used Perl, VBS, calls to Active-X, SQL scripts, and calls to stored procedures all in the same package. The direction given for the rework was to change it all to calls to stored procedures.). I don't know enough about SSIS to say it's different in that area or not but the perception of many companies is that not much has changed in that area.

    Sidebar: The Enterprise Architect at my current job forbids the implementation of SSIS because of how many times it "bit" him. His words, not mine. Someday, I'll get a couple of beers in him and press for the details. I've also worked for companies that have asked me for help to dismantle SSIS for various reasons mostly including those I've already spoken of.

    To answer your other question, what ends up happening in such cases is the use of Linked Servers is approved and nightly jobs to get the data are done. As bad as that can sometimes be (and I usually am able to dramatically improve the performance by working with the folks at the data source. DB2, in this case.), it's deemed by these companies to be the more attractive method compared to SSIS.

    To be honest, none of that has been particularly painful for me since one of my fortes is the import and processing of very large and sometimes seriously complicated "flat" files and some "not so flat" files that can even contain multiple segments/formats in the same file (although I can see how SSIS might be a big help in that area). It also helps immensely to be a bit of an iconoclast when it comes to the use of certain features available in T-SQL. 😛

    Shifting gears and as you've mentioned, I have seen the use of breakpoints and Data Viewers by some folks using SSIS. That's impressive and quite handy. As you say, the alternative is the use of temporary/staging tables but that's not required me to do "extra" coding because I never import directly to the final target table. I always use staging tables of one sort or another even if it's a wholesale replacement of a table.

    None of that withstanding, I'll admit that this thread has rewet my interest in learning how to use SSIS. Thank all of you for that.

    I'd still be interested in seeing what anyone has done to solve a problem in SSIS like the spreadsheet examples I posted. If that turns out to be simpler than I think, that would be strong impetus for me to learn more about SSIS sooner than later.

    --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)