December 9, 2009 at 2:18 pm
I've been working on upgrading our DTS packages to SSIS for about 12 months now and have been doing what I thought was a pretty reasonable job of it.
Anyway, a co-worker of mine found out that I had rewritten a package that puts data into a table that she uses & managed to get it stopped from going into Production (after it had successfully made it through Test). 4 weeks later, she comes up with the following:
SSIS Problems
- The IDE for SSIS is obtuse
- The only way to find out what is in an SSIS package is to open a new project, then load the dtsx into it. When the changes are finished, you go through about 20 steps to deploy it.
- We need to set up the SSIS so they are easy to support - at 7am in my case.
- The old DTS were not well constructed
- We should not port the problems from the DTS into SSIS.
- If the DTS had been well constructed, they would have only been used for transferring data from external sources to db tables
- There would not have been VBA script in them
- There would not have been global variables in them
- All data transformations would have been done as stored procs.
- If we want to rewrite the DTS packages as SSIS, we should take the time to do this the best way. The workflow and data flow should be made straight forward before the SSIS is constructed.
- SSIS should not have global variables
- SSIS should not have VBA script (even if it is .net)
- If the minimal amount of SQL in in the SSIS package, it will be easier to debug and change (at 7am)
- If the SSIS is as simple as possible, there won't be as much stuffing around in the obtuse IDE.
- The less there is in the SSIS, the less probability of having to change it and go through the 20 step deployment.
- The fewer versions of a package, the less the likelihood (sic) of someone picking up the old version on their PC and overwriting the latest version
Now - out of all that, there are only two statements that I agree with. ( - The old DTS were not well constructed
- We should not port the problems from the DTS into SSIS.)
When you've all picked yourselves up off the floor from laughing so hard, I wouldn't mind some ideas on how to handle this? By the way, she's 'junior' to me, but has the boss's ear and for some reason he seems to listen to her more than me.
December 9, 2009 at 2:32 pm
Funny.
Our SSIS projects are saved in VSS (we aren't using Team Edition). If someone on my team creates a new project, I can just check it out from VSS and edit it just fine. Not sure about those 20 steps to deploy, seems like only 5 or so iirc, and it really isn't that difficult. Would be nice, however, if it were integral to BIDS.
Some of what she said makes sense. We have views that accomplish much of the data transformations instead of doing them in SSIS. Does make those changes easier as you don't have to modify the package, unless you are adding or taking away columns.
I think what you need to find out, is what bothers her about the packages you have already written. Perhaps she just feels left out of the process. Just be sure you have facts to support your position not just opinion. If your boss is good he should be able to decern the difference.
December 9, 2009 at 3:05 pm
Our SSIS projects are saved in Subversion - what she's referring to there, is that when person A creates and deploys a package, you cannot open the .sln file from another machine without getting errors (usually connection errors). To avoid this, person B has to create a new project, then add the original .dtsx (refer to MSDN to see why). Unfortunately, its the only way to do it from a PC that is different to the one the package was originally constructed on.
I'm not sure what she refers to when she's talking about the 20 steps. We just use the deployment wizard and have a config file directory. She's a bit pissed off because she's used to working directly in Production with DTS.
I certainly agree - a "right click->deploy" would be a great addition to BIDS.
December 9, 2009 at 3:06 pm
The interface for building SSIS packages is extremely complex and difficult, assuming you aren't familiar with such arcane concepts as drag-and-drop, right-click, or flow charts. The deployment process does take at least 20 steps, if you start far enough away from your keyboard and mouse. And so on.
My approach on this one would be education. She needs training in SSIS, and source control.
Honestly, when I first started trying to use SSIS, and just thought of it as a "new version of DTS", it was tough to adjust to. When I began to realize it was a whole new product, not a new version of an old one, and began to use it a bit, it "comforted out" a lot, and did so quickly.
There are aspects of it that are still a pain (though deployment is absolutely not one of them), but it's minor pain, and will certainly resolve with even more use and familiarity.
But the key for me was definitely realizing that DTS is only an ancestor of SSIS in the same way that lemurs are a cousin to human beings. There's a relationship, but it's distant.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 9, 2009 at 3:11 pm
Funny - our experiences seem quite similar.
I totally agree with you about the education - the reality is that we did the MS course together...the boss now wants me to put together a 'show and tell' for her. As if I don't have enough to do!
December 9, 2009 at 7:30 pm
4 weeks later, she comes up with the following:
Heh... or should I say "Ack!" 🙂 I'll probably catch hell from everyone on this forum for saying so, but I REALLY LIKE HER! 😛 I don't know about the 20 steps she's talking about, but I pretty much agree with what she's saying. I spent a good portion of my last job replacing "special steps" that used Perl, VBS, and a wealth of other "sins" that appeared in DTS jobs because the original authors really couldn't figure out (or wouldn't make the time to figure out) how to do database tasks in the bloody database using T-SQL. And, yes, peformance improved drastically after the conversions to T-SQL.
And consider this... the reason why she might have the boss' ear is because she's 100% correct about DTS and SSIS in my eyes especially if it has to make an outside call to some other language... maybe the boss thinks so, as well.
Whatever you do, don't take her suggestions personally just because you're doing the rework. Instead, I'd strongly recommend that you pay her good heed because if she can do what I did with DTS packages, the boss is really going to like it... a LOT!
--Jeff Moden
Change is inevitable... Change for the better is not.
December 9, 2009 at 9:39 pm
Hmmmm... I've also seen something similar to this about 10 years ago. So, on the flip side of the coin... she's only right if she can actually do what she says. Otherwise, she's just another ugly bag of mostly water providing lip service to those that will listen. 😉 That wouldn't be a good thing... in fact, that could turn out to be a real PITA. I don't envy your position if it turns out that way.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2009 at 7:42 am
Jeff: Her complaint that SSIS takes too many steps to deploy, and her statements about how to open SSIS packages for editing (both of which are just plain wrong), make me think it's not about her being an expert on the subject with a valid opinion.
SSIS does have problems (what product doesn't?), but opening packages for editing, and deploying them when you're done doing so, aren't on that list.
You're right about non-T-SQL code overuse. I just recently rebuilt a package that used this horrific VB object to parse out some XML. Replaced it with a few XQuery selects and some standard transaction control statements. Took the runtime down from 2 hours to under 2 minutes, and got rid of some [ sarcasm ]minor[/ sarcasm ] issues like ocassionally leaving business-critical tables empty. (Why would anyone have a truncate command and the following insert in separate transactions, deliberately, on tables that are necessary for the business to carry out day-to-day functions? Especially in a case like this where the prior day's data is a minor issue, but completely missing data stops whole departements in their tracks? The mind boggles, but they had it that way for over 4 years, and "just lived with it when it had to be fixed from backups a few times a month".)
But judging by the miscalls on SSIS editing and deployment, I don't really think she's in that category of fixing things. Could be wrong, but I doubt it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 10, 2009 at 9:19 am
I was thinking the same thing, Gus... that's why I posted the retrospective. It took me a while but I remembered running into someone like this 10 or so years ago... again, we can't tell from here but I hope this person doesn't turn out to be the same type of useless, gold digging, ladder climbing, ring knocker who needed her head handed to her moroff that I ran into.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2009 at 1:28 pm
Thanks for your thoughts - there is a lot more that I could say, however I'm not willing to say it in a public forum, suffice to say that I am waiting to see what she comes up with. Apparently it was only going to take 4 hrs for her to rewrite the package in t-sql. That was over a week ago.
Oh well, today is another day. 🙂
December 10, 2009 at 8:41 pm
Thanks for sharing your story with us. I feel I am in the same position as you, except my coworker has the boss' ears and is 'senior' to me in the company. He is of the opinion that SSIS packages are a pain to deploy and modify, and that a lot of things can be done in SQL (using cursors and triggers, go figure !) much easier and faster in TSQL.
Can't say I totally disagree with him, but want to look ahead in life and use building blocks for the future.
December 10, 2009 at 8:57 pm
I agree that a lot of things can be done very quickly in T-SQL... just not with cursors. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2009 at 8:59 pm
Glad to be of help 🙂
It actually helps to know that there are other people out there facing the same problems. Thanks everyone
December 10, 2009 at 9:04 pm
SSIS, if used properly, can be well used to do some very good things. If using perl inside of it - you should get your head checked. SSIS is very straightforward and easy to use. As for the difficulty with the dtsx, I have not run into that issue. If the entire package (sln and all) is source controlled - anybody should be able to access it and open the package without going through the rigamorow of recreating the sln blah blah blah.
We use SSIS to interact with the database, application and to do soap calls for credit auth and credit card payment etc. When done properly, it is very fast!!!
As for the DTS crap - I personally wouldn't want to convert them haphazardly - I would open the packages and then manually convert it - not importing any of the DTS routines.
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
December 10, 2009 at 9:12 pm
Hmm...we really do have an issue with the .sln files & dropping connections when its opened on a machine other than the one that it was created on. We're using Subversion for source code control and the default settings for BIDS.
steps:
Create a new project
Give it a relevant name (TestSSIS)
Make sure its pointing to the correct local directory (C:\SSISPackages\)
Tick "Create directory for solution"
Tick "Add to Subversion"
Click OK
When the package opens, change the name of the package1.dtsx to TestSSIS
click ok when you are asked "Change the package object too?"
Create a data source
Create an oledb connection from the data source
use that connection somewhere
save, exit
Commit all changes to Subversion (or ClearCase)
Person 2 copies the files to the working folder on his machine (folder structure is identical)
Tries to open BIDS from the .sln file - opens ok, but has errors (can't find connection xxxxx)
The only way around it, is to create a new solution (called by the same name) then import the original dtsx into it.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply