Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: DTS & SSIS - much cop?

    Hi

    I am in the job market at the moment and keep seeing DTS listed as a requirement in the specs, typically right after "excellent T-SQL Skills". I kind of thought the latter made the former redundant. I don't really use DTS anymore - debugging a complex package is about as excruciating as pulling teeth - and just do it all in SQL. Load the data into staging tables using BULK INSERT and bugger about with it there before loading into the data tables.

    Am I missing something? Is there some great feature and functionality in DTS (or latterly in SSIS) that I am missing out on? Perhaps I just haven't come across the complex problems these organisations have.... Or is it just another drag and drop GUI that keeps you at arms length from the application and therefore less effective? Are DTS advocates the sort that would swear by EM over QA too?

    I have DTS on my CV since I can (and have) used it but I am nervous that there are perhaps some advanced features I am not familiar with that could catch me out at interview.

    I know there are some vehement critics of DTS on this forum and some agnostics but I don't know of anyone that is a real fan. Anyone got any opinions?

    Ta
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am not sure if there are any advanced features of DTS that can add whole dimensions of functionality. I doubt it, but I am not a DTS advocate. I think you can make a DTS package just as complex as any C program, PERL script, or T-SQL procedure. I think you will get farther knowing which tool to use for what purpose.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    My .02:

    I use DTS as a crutch and as what the military would call a "force multiplier".

    1. Crutch

    It's a really easy and fast way to move data from one place to another (with or without transformations). I'll use it over BCP any day.
    a. I'll use it in production to move a remote database's data to a local database when there are issues with connection reliability and remote db performance (can't be a huge db and the move is done off hours when there are fewer issues).
    b. I'll use DTS to archive off aged data to another database (perhaps on another server).
    c. I'll use it to pull in data from a non-SQL system, transform it and make it (the data) queryable to anyone in the organization for reporting purposes (without having to maintain multiple sets of db drivers on client machines).

    2. Force multiplier

    It's a force multiplier in a couple of ways:

    a. DTS makes the database a (more) important component of my employer's business model. It places the database server squarely into the middle of my employer's revenue stream and makes it an asset s/he pays attention to. It's Machiavellian perhaps, but the idea is that while my employer may not understand databases or database administration, s/he does understand information that is accessible. DTS makes data more accessible and is thus valuable to my employer.

    b. The more scheming part of (a) is that DTS (by extension) makes ME more valuable. My employer no longer needs to hire an additional ETL specialist to gain access to the data; I can provide that access in the same or less amount of time.

    c. Manageability. Since DTS packages can be scheduled through SQL Agent, I have one place to go to track the success/failure of a job. I don't have to scurry from one Scheduled Tasks window to another or check on MQ Queue on a third server or do a whole bunch of other tasks. I come in, settle down, check my scheduled tasks and we're done. It's on to other things.

    DTS does have some evil aspects:

    a. You can make packages too complex; complex packages can be a total nightmare to manage. I write my packages to be as austere and simple as can be. I try to stick with the very basic tasks/components and try to avoid too many in situ transformations.

    b. They can be hard to port from one environment (dev) to another (prod). Appropriate use of the Dynamic Properties task can help, but it's always a challenge.

    c. It runs in a separate memory space from the database server. You have to allocate memory to it (or rather make an allowance for it if you are using it heavily).

    d. You have to be mindful of the context in which you are executing a DTS package. I find it best to T/S in to the server (even in production) to validate that things are running how I expect them to run. If you consistently try to run them exclusively from your desktop through EM, you WILL get burned one day.

    DTS is a tool. It is nothing more and nothing less. Learning to use it well (and appropriately) can enhance your value to a prospective employer or a current one. From everything that I hear, SSIS offers even greater opportunities to enhance the role/function of the database server within an organization.

    I am sure that database purists shudder to hear such heresy. Ultimately, I try not to look at myself a simply a DBA: my role is to help my employer make money. The more I help my employer, the more (I hope) that I can share in the rewards that follow.

    My .02. It's worth what you paid for it.

    Regards,

    hmscott

    PS. Pootle: best of luck to you on your job search. I really value the insights you have to share on this board. I certainly empathize with your position and nothing I have said here is intended to detract from you or anyone else.
    Last edited by hmscott; 11-08-06 at 18:36.
    Have you hugged your backup today?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    DTS is a handy way to logically group tasks together. It also makes process flow much easier to manage. I personally seperate doing the heavy lifting, usually in a stored procedure, from the business rules and logic around how, when and why said heavy lifting occurs. The former tends to be accomplished with raw "code", the latter happens through DTS.

    It's the same manner in which I would seperate BI objects from the database through a data access layer, certain logic belongs in a certain place and becomes kludgy if you try to do otherwise.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks guys. I confess I still have a couple of packages and they are reasonably similar to those describeed - they started out as full ETL routines but were eventually stripped back to just the E, the T & L being completed by the calling stored procedure.

    I also use DTS (via the wizard) over bcp etc for one off imports so I guess I am not totally pooh-poohing it. I just probably wouldn't consider it something I would even consider putting on my cv unless potential employers specifically looked for it.

    Quote Originally Posted by hmscott
    PS. Pootle: best of luck to you on your job search. I really value the insights you have to share on this board. I certainly empathize with your position and nothing I have said here is intended to detract from you or anyone else.
    How terribly kind I would be very interested to know one single insight I have shared that has been valuable to you - my posts are really for my own amusement or for people less experienced than me - not the uber DBAs. And I didn't read anything that detracted from what I said (or if it did I don't take it personally - I am just interested in others' experience).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Bah! You have more to say than you suspect, Pootle. And the "uber DBA's" were not born uber*. You just give back to the community as a whole, and don't worry over who is happy to get an answer from you, just know they are happy to get an answer. Like this guy: http://www.dbforums.com/showthread.php?t=1610893.

    Besides, if we had a job opening here, I would offer it to you. I just think you would tire of the commute relatively quickly with the concorde being out of service, and all.

    *They were in fact cloned from other uber DBA's

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Gosh - how terribly kind of you all. Sniff.

    If you offered me the job and provided me with my own Lear jet I would certainly give it serious consideration

    ... Lear jets can make it across the Atlantic can't they?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I took it up with upper management here, and their counteroffer is a pair of water wings. Actually, their initial offer was a single water wing, but I managed to talk you up enough that they doubled their offer.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I accept. When do I start?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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