SSIS General Discussion
Locked
I can no longer attach the OLE DB Provider for ODBC drivers as a data source. Thanks M$. You just broke backwards compatability with the rest of the world.
I can't imagine how much rework this is going to involve for everyone and for absolutely no purpose.
Besides, when you select anything in the buggy UI, the properties window does not refresh to display the properties of the currently selected object. You must select, right click, choose properties from the right click menu to force the properties pane refresh.
Then the property you need to set is not there! Or the event that you needed is unavailable. Or the event is renamed to some obfuscated tag.
VS 2005 sucks. SSIS sucks. My job just got a lot harder because you guys abandonded support for ODBC, ADO, Smart Tags, and Windows Help files.
The help is now totally useless because the references for .NET 1.1, 2.0, 3.0 are co-mingled and propose development features that were either never implemented or do not work correctly or deprecated. I especially like that deprecated word. That one is getting used a lot since '01 when you started diverging from a path that would make software development productive to a path that makes software development mired in ...
oh well. enough ranting. linux and command lines, here i come.
- Changed TypeTodd McDermidMVP, ModeratorFriday, October 23, 2009 9:13 PMRant
- Edited byPatrik SchneiderMSFT, ModeratorMonday, November 16, 2009 9:43 PMInappropriate title.
All Replies
- Yes you can... Stop it. Create an ADO.Net provider against your ODBC source.
Also, Microsoft does not need you as a customer if you aren't willing to LEARN the tool and acknowledge it's shortcomings. We all have our gripes, but we work through them in this community, and where there are show stoppers, we submit BUG/FEATURE requests.
Phil Thanks for the insight Phil.
MFMH...You may find that SSIS is extremely fast and makes data integration a lot easier once you devote some time to learn new technologies. Staying current in our field is what makes us successful. Well … most of us anyway.
- No-ones forcing you to use SSIS, if you're happy with DTS or whatever you use and don't want to put in the time and effort required to learn SSIS (and it does require a considerable effort - it's much more powerful and correspondingly more complex that DTS) then fine, but there's no point ranting about it.
- Oh, and the properties pane refreshes just fine for me when I click from object to object... Perhaps your installation is corrupt.
Ok. So, SSIS sucks because you're unfamiliar with the product. I think it would suck for anyone (such as yourself) who isn't yet familiar with it and is unwilling to invest the time to learn it.
That's a shame because it should take much longer to accomplish with linux and the command line the same thing that can be accomplished by anyone well versed in SSIS.
Interesting that no one posted any real response to the issues here. If SSIS is capable of using the OLE DB Provider for ODBC drivers, then why does it not appear in the selection list of connection providers for the data connection ?
And if anyone can make a case for taking time to learn a new tool to do a task that you can do with an existing tool that you already know, then that person does not place a very high value on time.
It is frustrating to know that there are better, more productive tools out there to get the job done. Even prior versions of MS software have been great.
- Edited byPatrik SchneiderMSFT, ModeratorMonday, November 16, 2009 9:50 PMUnproductive as part of the discussion.
- I told you to use the ADO.NET connector for ODBC.
mfmh wrote: Interesting that no one posted any real response to the issues here. If SSIS is capable of using the OLE DB Provider for ODBC drivers, then why does it not appear in the selection list of connection providers for the data connection ?
mfmh wrote: And if anyone can make a case for taking time to learn a new tool to do a task that you can do with an existing tool that you already know, then that person does not place a very high value on time. Right. So because I already know DTS, I should just continue with it. That's not exactly the most logical thing I've heard in a while. SSIS provides FAR more features and data checks that DTS didn't provide. DTS is comparable to an end-user tool as compared to SSIS. SSIS has its shortcomings, yes, and we're working through them. However, some of the things that "just worked" in DTS might not work in SSIS because SSIS requires the developer to think about the design and ensure that datatypes match, for instance, so that the processing can be as fast as possible.
mfmh wrote: My time bills out at $100.00 plus per hour. Because I do spend the time to learn M$ tools. That don't mean that they are the best, just what makes me money. That's too much in my opinion. It doesn't sound like you've given 10 minutes to learn and understand SSIS and here you are wasting everyone's time by not providing anything constructive to this forum. I've answered your question. Do you have more specific questions that you would like answered? If not, start your own blog somewhere to post your rantings.
mfmh wrote: It is frustrating to know that there are better, more productive tools out there to get the job done. Even prior versions of M$ software have been great. Microsoft didn't claim that they produced the best tool for ETL when they released SSIS. They never claimed that it would be the world standard either. To each his own. You need to decide which product is best for your situation.
mfmh wrote: oh well. enough ranting. linux and command lines, here i come.
Sounds like a good idea. Flaming like this on here helps no-one, least of all yourself.
- This is an extremely bold statement considering that your SSIS skills are obviously lacking.
mfmh wrote: It is frustrating to know that there are better, more productive tools out there to get the job done. BTW, SSIS ships with an ODBC Connection Manager which can be used to connect to ODBC data sources.
- I know this is an old post, but...
http://rip747.wordpress.com/2007/10/01/sql-server-2005-import-export-reset-identity-keys-no-workaround-2005-sucks-period-the-end/ brianary wrote: I know this is an old post, but...
"This is an extremely bold statement considering that your SSIS skills are obviously lacking."
This is how Microsoft responds to consumer frustration, folks. It's your fault, not their implementation. You should be spending more time giving them the benefit of the doubt.FYI. Those are not the words of a Microsoft employee, past or present.
Oh yes, SSIS requests:
1. Tiny text entry points for conditional split conditions (and many other places). I have to key my conditions into a text editor and copy/past them into this POS.
2. The most common things to do in data land are hard in SSIS, starting with importing an Excel SS into a non-unicode table. What, MS does not know that my table is not unicode and that Excel is? I'll take implicit conversion any day that I AM IN A HURRY.
3. Today's issue of the moment: I'm doing an incremental load of dozens of tables with hundreds of fields whose structure I am not familiar with, and the conditional split is complaining about conditions that evaluate to NULL, with no way to globally handle the situation.
What, does MS not know how common incremental loads are? I should just be able to select my source and dest and press the execute button.
- Edited byPatrik SchneiderMSFT, ModeratorMonday, November 16, 2009 9:54 PMSanitized.
Hey Microsoft! I have an idea!! Why not just leave the old DTS available for people who just want a 'quick and dirty' way to get a simple text/Excel/Access/yadda yadda yadda file into the database!?!? I DON'T HAVE TIME TO LEARN A NEW INTERFACE for what USED to be a 5 minute exercise!!! For crying out loud!!!! I shouldn't have to create new project and dig for 2 hours to figure out how to do something that used to be OBVIOUS!
I am confident that SSIS is a powerful product, but for the simple tasks of quick and dirty development....yes, the old DTS was MUCH better.
- Is "former" telling?
I know this is a rant thread - but please don't make claims that are stupidly easy to disprove. There are over 20,000 threads in here, almost 13,000 which are answered. Return your calculator to the store. It's broken.
SSIS 2008 is far from buggy and unstable.
I know that CozyRoc has an Informix Destination - don't think they have a source. Does an ODBC connection not work for you? A script source?
- This will be the last post I'll waste. You are free to have your opinion, and even free to say it just about wherever you please.
I'll compare the SSIS forums to the T-SQL one. I'll assume you think that T-SQL is "not buggy". T-SQL shows 14000 answers in 20000 questions - roughly over 1/4 unanswered. I'm not proud of anything, and I don't see how you could misinterpret anything I said as bragging. Except possibly that I have a calculator that works. And how fair is it to count answered posts in the first three pages? What do you think the install/help base is for SSIS compared to T-SQL? And how many posters actually get around to marking their posts answered in a timely fashion? And how many people post AGAIN in already-answered threads? (Like this one?)
If you actually read Oren's post - which I did again about an hour ago off someone else's post here - as well as the comments and responses from other people, I think you'll find that:
A) Oren's comments are related to SSIS 2005. Nowhere does he, or any other person make claims about 2008. All of the posts and comments are in 2007. Not that I think they're much different - both have been very stable for me.
B) Oren doesn't say it's buggy.
C) When Oren says it's unstable, he's referring to configurations, which plenty of comments describe (rightfully so) as not unstable, but incorrectly used. I should know - I can't/don't use them because I haven't taken the time to understand the execution order yet.
D) Oren says SSIS breaks easily because he doesn't understand how it uses metadata, or configurations.
All that said, you should actually read through some of the links. Jamie Thomson provides a spectacular discussion on those complaints - some of which he completely agrees with, as do I.
- Edited byTodd McDermidMVP, ModeratorTuesday, June 02, 2009 7:20 PM
- Whether we want to admit it or not, I think we all of have these sorts of rants in our minds as we use any sort of technology (not just Microsoft technologies).
I think what is most frustrating about SSIS is what it seams that it should be able to do, but cant.
For instance why do all of the destinations not allow any outputs? How many times do we need to know the identity of a row that we just inserted for further processings. If SSIS desintations supported an output that contained the SCOPE_IDENTITY then that column could be used in further processing. Instead we have to create surrogate keys and store those surrogate keys in a different database to be referenced later.
Another thing that would be helpful would be the ability to quickly create a new column that has a guid value. I know that at my company every row has a guid field that uniquely identifies the row for replication. When new data is migrated in, it would be nice to have the ability to import a new guid for that row. Currently I use a script transformation. But it would be nice if it was a part of one of the current transformations. The "derive column" transformation has a lot of neat little expressions that you can use to manipulate data, but if it had just a few more functions it would really improve SSIS.
It would also be nice to be able to alter the data in a row from within an ODBC command transformation. So if you were to do something like "select ? = max(paymentdate) from payments where customerid=?" then i could retrieve the largeest payment for a customer and add it to the buffer all in one swoop. In the past I have had to create a complicated join in the source to retrieve this data and add it to the buffer. This might not be a problem, except that SSIS has an awful time in interpreting sql and often is not able to to interpret valid sql simply because it is too complex.
Perhaps the smallest complaint i have is that making the package aestheticly pleasing is hard. If you could do some of the things that you can do while creating windows forms it would be very nice. I know it seams small, but i really hate it when the ssis package does not look professionaly done. If you could traighten the lines of the data flow arrows. align the transformations, and have if boxes displayed a description (that didnt have to be unique) instead of a name it would really help SSIS.
But i think this is also a pretty good indication of where SSIS is. It is a product with a lot of ability, but really doesnt seam to have been given the love it needs by the development teams at Microsoft. With a few fixes I think a lot of people would want to use SSIS a lot more. But perhaps that is just the way it is. It does come bundled with SQL Server which means that it is not a real revenue generator for microsoft. And if microsoft is like most companies, then anything that doesnt have revenue assosciated with it just gets pushed down the line. For instance why do all of the destinations not allow any outputs? How many times do we need to know the identity of a row that we just inserted for further processings. If SSIS desintations supported an output that contained the SCOPE_IDENTITY then that column could be used in further processing. Instead we have to create surrogate keys and store those surrogate keys in a different database to be referenced later.
AFAIK, you can't do that in T-SQL either. Although it would be great to have in SSIS and T-SQL...
Another thing that would be helpful would be the ability to quickly create a new column that has a guid value.
I second that. In fact, that's so easy to do, it's on my list of things to code and add to the SSIS Community Tasks and Components project.
It would also be nice to be able to alter the data in a row from within an ODBC command transformation.
I'm not sure I understand your request on this one - you mean OLE DB Command transform, right? Have you tried the Lookup component? That would allow you to look up the "max(paymentdate)" for each row, then you could push that into an OLE DB Command transform to update your database. But to perform nicely, you should probably just use a set-based UPDATE from an Execute SQL Task.
Perhaps the smallest complaint i have is that making the package aestheticly pleasing is hard. If you could do some of the things that you can do while creating windows forms it would be very nice. I know it seams small, but i really hate it when the ssis package does not look professionaly done. If you could traighten the lines of the data flow arrows. align the transformations, and have if boxes displayed a description (that didnt have to be unique) instead of a name it would really help SSIS.
Take a look in the Format menu. The same capabilities you have for aligning and arranging items in a Windows Form are there. Select two (or more) Control Flow or Data Flow items, then use the Format tools to align them, space them evenly... And you did know that you can edit the names in the boxes... didn't you?
But i think this is also a pretty good indication of where SSIS is. It is a product with a lot of ability, but really doesnt seam to have been given the love it needs by the development teams at Microsoft. With a few fixes I think a lot of people would want to use SSIS a lot more. But perhaps that is just the way it is. It does come bundled with SQL Server which means that it is not a real revenue generator for microsoft. And if microsoft is like most companies, then anything that doesnt have revenue assosciated with it just gets pushed down the line.
Yikes. That's a pretty disparaging comment against a (small) team that you also acknowledge probably doesn't get that much budget. I happen to agree with you that it's a shame they don't get more budget...
Overall though - perhaps you should spend some more time with SSIS. It seems like there's still a few more things for you to pick up. I know I still learn something new quite often.
Just a correction Todd, you can do this with T-SQL as the following script demonstrates:For instance why do all of the destinations not allow any outputs? How many times do we need to know the identity of a row that we just inserted for further processings. If SSIS desintations supported an output that contained the SCOPE_IDENTITY then that column could be used in further processing. Instead we have to create surrogate keys and store those surrogate keys in a different database to be referenced later.
AFAIK, you can't do that in T-SQL either. Although it would be great to have in SSIS and T-SQL...CREATE TABLE FOOB(ID INT IDENTITY(1,1), Data VARCHAR(20)); GO INSERT INTO FOOB(Data) OUTPUT inserted.ID, inserted.Data SELECT Name FROM syscolumns WHERE LEN(Name) > 1 AND LEN(Name) < 20
-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
Please! Remember to Vote all helpful replies as HelpfulAh! You're correct. I was mistakenly interpreting the BoL page for OUTPUT, where it says:
The exceptions are identity and computed columns. These must be skipped.
I must admit, I've never tried - I just believed the (misread) documentation. Thanks for the info...
- Let's leave this thread alone. It was started in 2006, and is quite irrelevant. Start a new thread if there are new comments to be made.
Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer - "Also, Microsoft does not need you as a customer if you aren't willing to LEARN the tool and acknowledge it's shortcomings. "
That is the most absurd thing I have ever heard coming out of a MS mvp.
Doesn't need you as a customer huh? I wonder how Microsoft would feel about you saying foolish things like that on their behalf? "Also, Microsoft does not need you as a customer if you aren't willing to LEARN the tool and acknowledge it's shortcomings. "
That is the most absurd thing I have ever heard coming out of a MS mvp.
Doesn't need you as a customer huh? I wonder how Microsoft would feel about you saying foolish things like that on their behalf?
I was not an MVP at the time of that posting.
Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
