December 27, 2008 at 12:40 pm
At least he tries. I think he's slightly in over his/her head with this stuff, but he's plugging along.
December 29, 2008 at 7:39 pm
Ok... I thought that things like DTS and SSIS were supposed to make things simple for people to do things in a database... I'm seeing more and more posts like the following...
http://www.sqlservercentral.com/Forums/Topic626558-9-1.aspx
... Looks to me like DTS and SSIS just aren't cutting the mustard when it comes to making life simple for folks that don't know T-SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2008 at 12:07 am
Jeff Moden (12/29/2008)
Ok... I thought that things like DTS and SSIS were supposed to make things simple for people to do things in a database... I'm seeing more and more posts like the following...http://www.sqlservercentral.com/Forums/Topic626558-9-1.aspx
... Looks to me like DTS and SSIS just aren't cutting the mustard when it comes to making life simple for folks that don't know T-SQL.
The issue here is that DTS/SSIS do not provide a MERGE task, at least not in 2005, I don't know if SSIS 2008 has one, so there are a couple of ways to handle this in SSIS. You can use a Lookup task that inserts new and uses an OLE DB Task to perform an update (really slow as it is SSIS RBAR), you can use the lookup insert the new rows into the destination and the updates into a staging table and do the inserts or you can just insert the source data into a staging table and then use T-SQL to do a set-based update (the most efficient way, excluding storage).
After all that I guess you do still need to know some T-SQL.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 30, 2008 at 12:24 am
I was also looking at today's article on SSIS and what I think is a fairly simple import...
http://www.sqlservercentral.com/articles/SSIS/64766/
All I can say is "Good Grief"... it took this person a couple of days to figure something out and ended up having to write a custom script to do what? Import a lousy 10 million rows, do some really simple validations, and it all takes 90 minutes? Heh... What the hell are they gonna do if they ever get some real volumes of data? 😛
If that's the best they can do with SSIS for such a simple problem, and I don't care if you have to compare 15 denormailized columns, then I'm real happy I didn't waste any time learning it. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2008 at 12:28 am
Jeff Moden (12/27/2008)
BWAAA-HAAA! Excellent candidate for this year's SQL Darwin award...http://www.sqlservercentral.com/Forums/Topic478330-8-1.aspx#BM480712
Scroll up from that post to see the hell he caused a bunch of people because he thought Sybase and SQL Server worked the same what. 😛
"Number Five is alive! Need input!" :hehe:
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 30, 2008 at 12:36 am
Jeff Moden (12/30/2008)
I was also looking at today's article on SSIS and what I think is a fairly simple import...http://www.sqlservercentral.com/articles/SSIS/64766/
All I can say is "Good Grief"... it took this person a couple of days to figure something out and ended up having to write a custom script to do what? Import a lousy 10 million rows, do some really simple validations, and it all takes 90 minutes? Heh... What the hell are they gonna do if they ever get some real volumes of data? 😛
If that's the best they can do with SSIS for such a simple problem, and I don't care if you have to compare 15 denormailized columns, then I'm real happy I didn't waste any time learning it. 😉
Well, I agree that this may not be the best way to solve the problem, even in SSIS, as it will be a bit a of a dog, especially if the data is already in SQL Server, which I believe it is, based on the article. To me once the data is in SQL Server, the best solutions are usually T-SQL. In this case I would probably have cleaned up the data prior to putting it in the staging table, but without seeing the source, it's hard to say, but with the data already in SQL Server T-SQL would be what I would use.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 30, 2008 at 12:59 am
Jeff Moden (12/30/2008)
I was also looking at today's article on SSIS and what I think is a fairly simple import...http://www.sqlservercentral.com/articles/SSIS/64766/
All I can say is "Good Grief"... it took this person a couple of days to figure something out and ended up having to write a custom script to do what? Import a lousy 10 million rows, do some really simple validations, and it all takes 90 minutes? Heh... What the hell are they gonna do if they ever get some real volumes of data? 😛
If that's the best they can do with SSIS for such a simple problem, and I don't care if you have to compare 15 denormailized columns, then I'm real happy I didn't waste any time learning it. 😉
Honestly, SSIS is getting a bad rap because there are some really incompetent programmers out there. I have an SSIS package that extracts data from my live system on a daily basis - with some tables having millions of rows, and our processing on the live system is completed in about half an hour.
I've seen some really stupid things done in both DTS and SSIS. This does not mean either tool is bad - just that the person who implemented the process didn't know what they were doing.
For example, I had one DTS package that would drop the indexes on the destination table - extract the data from the source system, import the data into the destination (approximately 10 million rows every time as a full load instead of an incremental process). The destination table had four additional derived date dimension columns (foreign key pointers to the date_dim table). After rebuilding the indexes, they would then issue an update statement for each date dimension, one at a time. It could take several minutes for each date dimension column to update times four (or more, depending upon the table).
And - these people blamed the problem on DTS and SSIS. Go figure...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 30, 2008 at 1:13 am
Heh... ya know? Thanks for the feedback, Jeffrey... Seems like the intent of Microsoft was to make things easier for folks that don't know how to do it in T-SQL... but some folks just insist on making life tough and very slow.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2008 at 1:27 am
Yeah, I know what you mean - but, I think I have it a bit harder than you do. If you have ever worked with any flavor of Mumps (DSM, GT.M, Cache, MSM, etc...) you would know.
Mumps is actually a great database product - however, that is all it really is - a database. It does not have a DBMS built into it, you have to build your own. The latest version (Cache) does come with a built in DBMS that is modelled more as an OODBMS than an RDBMS - but it really can do either.
Anyways, in these previous versions - all programming and database access was procedural. We *ordered* through a global (similar concept to a table) and processed each record. We still have programmers who have not transitioned to set-based processing (old mumpters). One in particular constantly creates a view, that is then used in another view, that is used in another view, etc... all because he is trying to 'process' the data using that new fangled SQL stuff. He just does not get it - and I am afraid that he never will.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 30, 2008 at 1:36 am
Jeff Moden (12/30/2008)
Seems like the intent of Microsoft was to make things easier for folks that don't know how to do it in T-SQL...
I don't agree with that. SSIS is an ETL tool. It's used best for extracting data from some source (maybe SQL, maybe not), applying some transformations to that data and then loading it somewhere else.
Using it as an alternative to writing T-SQL is like using a screwdriver to put a nail in a wall. It'll work, but...
The times I've seen SSIS work best is when the data source was not SQL (DB2, Oracle, flat file, excel), the transformations were relatively simple (derived columns, conversions, lookup/fuzzy lookup) and the destination was either SQL or another database, and was the end of the process.
If complex processing is needed, that should be done in T-SQL, either after the SSIS package completes, or as a T-SQL task in the control flow, but not as a component in the data flow.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2008 at 4:37 am
GilaMonster (12/30/2008)
Jeff Moden (12/30/2008)
Seems like the intent of Microsoft was to make things easier for folks that don't know how to do it in T-SQL...I don't agree with that. SSIS is an ETL tool. It's used best for extracting data from some source (maybe SQL, maybe not), applying some transformations to that data and then loading it somewhere else.
Using it as an alternative to writing T-SQL is like using a screwdriver to put a nail in a wall. It'll work, but...
The times I've seen SSIS work best is when the data source was not SQL (DB2, Oracle, flat file, excel), the transformations were relatively simple (derived columns, conversions, lookup/fuzzy lookup) and the destination was either SQL or another database, and was the end of the process.
If complex processing is needed, that should be done in T-SQL, either after the SSIS package completes, or as a T-SQL task in the control flow, but not as a component in the data flow.
I was talking about that (SSIS is an ETL tool), Gail. It's just that I can do a lot better with the miniscule ETL tools available in T-SQL and SSIS was supposed to make ETL easier for people who couldn't do it in T-SQL. It, apparently, has not. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2008 at 4:54 am
I am always amazed at the number of people that have trouble with getting SSIS to insert new rows and update existing ones.
For small tables, the Slowly Changing Dimension transformation does exactly that.
For larger tables, a merge join and a conditional split work fine, or if you have trouble with it, the TableDifference component available here: http://www.sqlbi.com/Projects/TableDifference/tabid/74/language/en-US/Default.aspx works great.
But for really large tables, using a staging table and T-SQL usually perform the best.
SSIS is a great tool. It is flexible - therefore complicated. It does work great when you know how to use it. I think a lot of people assume that it must be simple because it is "free" and don't want to spend any time reading or taking a class on it. I have some complicated ETL processes that move several million records each day and they take 20 minutes to complete and rarely error.
December 30, 2008 at 5:23 am
Jeff Moden (12/30/2008)
It's just that I can do a lot better with the miniscule ETL tools available in T-SQL and SSIS was supposed to make ETL easier for people who couldn't do it in T-SQL.
Good for you. Personally, if the source and destination are not the same SQL instance, I'll take SSIS any day to do ETL.
Just because a lot of people use it badly, doesn't make it a bad tool. I don't think it was ever designed to be easier than T-SQL. It was designed to be easier and more extensible than DTS.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2008 at 5:31 am
GilaMonster (12/30/2008)
Just because a lot of people use it badly, doesn't make it a bad tool.
Heh... yeah, I keep forgetting stuff like that in my old age. Look what some folks do to T-SQL. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2008 at 5:36 am
Very true, if you have to judge a tool by the number of people that misuse it, Analysis Services must be a total failure as well.
Viewing 15 posts - 511 through 525 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply