March 22, 2006 at 5:09 am
Ok Jamie, you're on!
Let me manipulate the data so it hasn't got any kind of personal info in it (it is from one of our customers after all) and I'll send it to ya! If you do it (under 1 min and 17 secs don't forget), I'd love to know how ... no, really I would - I soooo want to be proven wrong on this
March 22, 2006 at 7:43 am
I've had the most fun and games trying to use C# and the SSIS object model to program some components and packages to run on the fly SQL statements.
My humble opinion is that I'm going to ask Microsoft to include a complimentary membership in the Hair Club for Men (and Women) with every copy of SQL Server 2005 / SSIS destined for C# developers.
Has anyone had much success programming w/ C# and the object model? I would love to trade notes with you.
Thanks!
DougB
March 22, 2006 at 8:02 am
Sorry Doug, I've only just got into trying to do it through the GUI! Ooh, doing it through C# - hardcore! (And you have my deepest sympathy with the hair thing
March 22, 2006 at 2:56 pm
March 22, 2006 at 5:32 pm
March 23, 2006 at 2:54 am
Hi David, it would be interesting to hear your experiences of SSIS and why you came to scrap it entirely 🙂
Cheers, Mark
March 29, 2006 at 7:54 am
Well, it appears that no-one has been able to rise to my challenge and convince me that SSIS is an improvement over DTS. Not only is it NOT an improvement, it's one of the most monstrously complicated pieces of poo-poo I have ever seen (apart from trying to program the video to record Coronation Street). I'm afraid the argument of 'it's complicated for a reason' doesn't wash with us - we need the simplicity of DTS. I do feel that Microsoft have missed the point entirely of DTS and have replaced it with something that is just way over the heads of ordinary users.
Of course, all the extra training that is going to be needed for even the most basic of operations means that Bill will be able to afford a new country after all.
Thanks to everyone for contributing.
(Thanks to Jamie for attempting the challenge - did you manage to do it? - you were the only one brave enough to have a go!)
March 29, 2006 at 7:58 am
No, I didn't sorry. I promise I still will. Just been very busy y'know!!
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
March 30, 2006 at 9:24 am
DTS is gone. So I'm curious what are you doing, writing your ETLs as C#/VB assemblies, doing it all in TSLQ? What's your new methodology?
thanks!
Skål - jh
March 31, 2006 at 10:21 am
I think Microsoft really dropped the ball with SSIS. They took an easy-to-use and very intuitive product, and turned it into a needlessly complex nightmare. Primarily because of the SSIS disaster, we are putting off migrating to SQL 2005 and may even skip this version of SQL Server entirely. We are probably also going to research alternative products.
Our DBAs are also not at all happy with the "visual studio" interface for SQL 2005, but that's a whole other discussion.
April 1, 2006 at 11:13 am
Mark,
I finally got around to trying this. I apologise that it took me so long. Everytime I booted up to try it something ended up interrupting me y'know. Emails to answer...IMing etc...
Anyway, I've tried. I spent some time getting famiilar with the import wizard first. Given that I've never used it before I thought that was fair to put me on an equal footing to yourself seeing as you are familiar with DTS2000 import wizard.
Prior to starting the stopwatch I created the table with the CREATE TABLE script that you sent me and placed the source file into c:\temp. Nothing else.
I then used the import wizard to import the data into that table and subsequently save the package to SQL Server. The whole thing took 44 seconds compared to yours, Mark, using DTS which took 77 seconds.
I was also able to rerun the package successfully afterwards - proving that the contents of the table do indeed get deleted (or else it would fail with a PK violation)
I won't crow and proclaim SSIS to be any better on the basis of a simple test like this. I am willing to accept other people's opinions about SSIS being a step backward as long as people accept my own that SSIS is a far superior product than DTS. Remember that one of the motivation for building SSIS and many of the new features was complaints about DTS.
Yes, its a different tool and hence things which worked in DTS won't work the same in SSIS but as with any new tool, get to know how to use it and I'm confident that you will see the benefit.
-Jamie
P.S. Just to prove the test here's the output after running the package from the wizard:
The execution was successful
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Warning)
Messages
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 0" with a length of 50 to database column "Branch" with a length of 20.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 1" with a length of 50 to database column "BranchType" with a length of 1.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 3" with a length of 50 to database column "Name" with a length of 30.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 4" with a length of 50 to database column "Addra" with a length of 30.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 5" with a length of 50 to database column "Addrb" with a length of 30.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 6" with a length of 50 to database column "Addrc" with a length of 30.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 7" with a length of 50 to database column "Addrd" with a length of 30.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 8" with a length of 50 to database column "Addre" with a length of 30.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 9" with a length of 50 to database column "Postcode" with a length of 12.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 16" with a length of 50 to database column "Supplier" with a length of 10.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 21" with a length of 50 to database column "User1" with a length of 10.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 22" with a length of 50 to database column "User2" with a length of 6.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 23" with a length of 50 to database column "User3" with a length of 8.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 24" with a length of 50 to database column "User4" with a length of 12.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 25" with a length of 50 to database column "User5" with a length of 12.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 0" with a length of 50 to database column "Branch" with a length of 20.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 1" with a length of 50 to database column "BranchType" with a length of 1.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 3" with a length of 50 to database column "Name" with a length of 30.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 4" with a length of 50 to database column "Addra" with a length of 30.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 5" with a length of 50 to database column "Addrb" with a length of 30.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 6" with a length of 50 to database column "Addrc" with a length of 30.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 7" with a length of 50 to database column "Addrd" with a length of 30.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 8" with a length of 50 to database column "Addre" with a length of 30.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 9" with a length of 50 to database column "Postcode" with a length of 12.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 16" with a length of 50 to database column "Supplier" with a length of 10.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 21" with a length of 50 to database column "User1" with a length of 10.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 22" with a length of 50 to database column "User2" with a length of 6.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 23" with a length of 50 to database column "User3" with a length of 8.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 24" with a length of 50 to database column "User4" with a length of 12.
(SQL Server Import and Export Wizard)
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 25" with a length of 50 to database column "User5" with a length of 12.
(SQL Server Import and Export Wizard)
- Saving (Success)
- Prepare for Execute (Success)
- Pre-execute (Success)
Messages
Information 0x402090dc: Data Flow Task: The processing of file "C:\temp\BRANCHTEST.CSV" has started.
(SQL Server Import and Export Wizard)
- Executing (Success)
Messages
Information 0x402090de: Data Flow Task: The total number of data rows processed for file "C:\temp\BRANCHTEST.CSV" is 63.
(SQL Server Import and Export Wizard)
- Copying to [Blockingtest].[dbo].[Branches] (Success)
63 rows transferred
Messages
Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)
Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)
- Post-execute (Success)
Messages
Information 0x402090dd: Data Flow Task: The processing of file "C:\temp\BRANCHTEST.CSV" has ended.
(SQL Server Import and Export Wizard)
- Cleanup (Success)
Messages
Information 0x4004300b: Data Flow Task: "component "Destination - Branches" (97)" wrote 63 rows.
(SQL Server Import and Export Wizard)
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
April 3, 2006 at 3:15 am
Well, this proves it CAN be done - Well done Jamie for rising to (and beating) my challenge.
I've either got some serious learning to do or consider a change of career coz I haven't got a clue how you've managed to do it. I've been through that wizard so many times and not once have I got it to work (I still can't get the package to run that you sent me - not that I don't believe you, it just won't run as the debug menu is disabled: any ideas?)
Thanks for giving it a go and showing us all that it can indeed be achieved - maybe I should go back to the drawing board.
Cheers, Mark
April 3, 2006 at 5:12 am
The debug window in BIDS? That's very strange. Are you sure you have an Integration Services project open? You need to add the package to a project in order to do much with it (I think)
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
April 3, 2006 at 5:21 am
Hi Jamie.
To be honest, there's an entirely-likely possibility I've not done it right. Saying that, I've had a quick look at the package you sent and it does make things a little clearer (even if I can't get it to run yet!) - I can see the logic that is happening behind the control and data flows (which eluded me before) and it's starting to look a little more interesting (as opposed to frustrating). I'll keep you posted.
Mark
April 3, 2006 at 6:29 am
Mark,
That's great news, I'm delighted to see you wavering
I think this kinda reiterates the point that there is a definate learning curve with SSIS. That's unfortunate of course, but hopefully beneficial in the long run.
There's a growing number of resources out there to help you understand it better:
I also think this article will be beneficial to you: http://www.sqlservercentral.com/columnists/jthomson/thenewetlparadigm.asp
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply