April 13, 2010 at 5:34 pm
Is there any advantage to using SSIS (2005) over just writing code in Visual Studio? I've been trying to learn SSIS for some simple data import tasks, but I could have done the same thing in VS much faster.
Does SSIS have functionality that Visual Studio Professional (also 2005) does not? I don't mind writing code so should I even bother to learn SSIS?
April 13, 2010 at 6:58 pm
I would say yes. SSIS will be faster for most data movement tasks. It was built for that. I would be willing to bet money you can't code it faster if you had more SSIS experience.
The big thing, is you don't have to write code in SSIS for most things. Add a couple connections, drop a dataflow on the package. Add a source and a destination in the dataflow, connect them and do some simple mapping and boom you are moving data. Can you do all this in .net, sure, but you can't code it as fast AND the SSIS tools are built to move data fast. First pass of your written code won't be optimized for speed.
The only reason you might have done it faster is you probably know .net pretty well. But, for example, I could setup a simple data move in about a minute without writing a line of code. It would take me longer to open the tools and the solution than it would to build the package.
CEWII
April 13, 2010 at 7:37 pm
I agree with Elliott. Yes - learn SSIS. .Net is useful in many ways, and SSIS is useful for dataflow type tasks.
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
April 14, 2010 at 3:16 am
I couldn't agree more with Elliot W.
Sure you can build it in .NET, but pass a couple of million rows throught the pipeline, combined with some (complex) logic, and you'll see that SSIS is much faster. If you can build it faster in .NET, sell your program, because you've created a SSIS competitor 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 14, 2010 at 11:02 am
OK, I'm convinced. elliot is right, I have written a lot of dot Net code, especially lately, but the last time I needed to create SQL Server packages it was in DTS for SS2000 about 5 years ago. Now my job requirements have changed and I'll be doing more etl work.
I suspect my visual Studio experience is hindering my SSIS learning.
Thanks.
April 14, 2010 at 11:06 am
I'm certainly not saying that your .net experience isn't useful in SSIS, there are things that can more easily be done with a script task. However, data movement isn't one of them. Once you build a few packages it will become clear.. Good luck!
CEWII
April 14, 2010 at 11:24 am
An example of where we used .Net in ssis was in a shipping and ordering application. We used SSIS for all of the data movement, but then used .Net to go out to the CreditCard companies to make the charges. It worked rather well.
Just as a reinforcement to what Elliott said. .Net can be used inside ssis and works rather well. I just wouldn't necessarily use it to move data.
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
April 15, 2010 at 1:38 am
2C worth,
In SSIS you get the graphical interface as well, which help a good bit if you have complex movement of data. My current SSIS packages all make use of a VB .Net application which I wrote to do some transformation of data which can not be caterred for in SSIS itself. Previous I used DTL, DataStage and Teradata's utils to do ETL, but I think overall SSIS is the better option for ETL.
April 15, 2010 at 10:52 am
Part of the learning curve is the grapical IDE. When I get frustrated I want to bypass it and paste working code somewhere. :crazy:
April 15, 2010 at 12:00 pm
If you think the GUI is painful. Wait for the pain that directly editing the DTSX file brings. It gets easier.. I promise. The first several packages are painful but as you get to understanding it better it gets easier..
CEWII
April 15, 2010 at 12:06 pm
For any other newbies out there, I've discovered something helpful. I use the SQL Serveer Import/Export wizard. By saving the packages I can import them into the SSIS designer and get a head start on learning on packages I already know will be directly applicable. Big help!
April 15, 2010 at 12:13 pm
Elliott W (4/15/2010)
If you think the GUI is painful. Wait for the pain that directly editing the DTSX file brings. It gets easier.. I promise. The first several packages are painful but as you get to understanding it better it gets easier..CEWII
The nuances of the GUI are painful in the beginning and still somewhat painful after using for some time. It does get easier.
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
April 16, 2010 at 7:45 am
Basically, you're choosing your long term maintenance strategy now.
Do you want to maintain .NET code as the .NET framework changes and is eventually replaced (as old VB6 and .NET 1.1 was), or do you want to do the same for SSIS as it suffers the same fate (as old DTS was)?
Do you want to maintain some different codebase (SQL with BULK INSERT and BCP? Some other language you like/support)?
What skillsets are common in your shop now? What's your roadmap?
Personally, I don't like debugging graphical languages, and I've some complex DTS setups that need to be completely rewritten due to DTS going away, so I avoid SSIS in favor of SQL, bcp, BULK INSERT, and where necessary whatever the standard programming language of choice is.
April 16, 2010 at 11:43 am
Nadrek,
I largely agree with you. However, I am a fan of the right tool for the right job, and let me be clear, .net or almost all procedural language are NOT the best option for moving data. Period. I do agree that you are choosing your tools moving forward. Avoiding SSIS is usually not a good idea.
As far as available skillsets. If you do a lot of SQL development you should probably have a guy (or gal) who has strong SQL skills.
Also, SSIS isn't a graphical language, it is a tool.
I guess my biggest point is using the right tool for the right job. I have learned new languages and tools when the skills or tools I was using wouldn't cut it. Don't get stuck in the mindset where every problem SHOULD be solved with the tools I know.
CEWII
April 16, 2010 at 12:22 pm
Personally, learn both as much as you can. There are some tasks you can do with native SSIS tasks but some tasks like the file system task can be a bit kludgy to make them work correctly. It's in those scenarios that I write some .net code.
I also like the Import/Export wizard as someone recommended. That's how learned SSIS (and DTS). Great learning tool!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply