Why SSIS? Is there a need for it?? The question is where and in which circumstances, isn't it?? I have been around in BI for quite some years and stumbled into MS conferences, where MS and others have tried to convince my poor brain. But all the time they have failed, so I started to wonder if the fault was mine. Have I gone too old??? Installing a lot of new SW on my harddrive including SSIS recently,I decided to give it a new chance. I also invested a lot of time in Webcasts where convincing people tried to get me going.. Still I wonder, is there a need for it, I am not capable of seeing it so please help!! HaveI gotten blind or mad? All I have done so far in my life with SQL should now be done in SSIS? And I start asking why! Is it better? (No), Is faster (Not for me, but maybe for the computer), Is it more maintanable? (No, just more messy) Is it graphical? (Yes, but that dosen't mean easier to use than T-SQL - I get totally confused when it comes to context and overview... When I need to see a detail, I have to investigate it with at least five clicks and try to find it in a lot of hidden places where plain code makes much more sence) AND when I look at a 'Showplan' i T-SQL, for meit lookslike the thing I am nowexpected to construct manually... SQL = Explain what you want SSIS = Explain how to do it <-> IE aShowplan. Am I right or am I wrong?

I don't think you are entirely wrong, however you seem a bit misguided. SSIS is not intended to replace the Database Engine but meant to compliment it and enhance it. It's very effective as an ETL tool. For instance, let's say you wanted to extract a list of clients from one of your database systems and email that list of data to a Direct Mail house every week. With SSIS this is a very easy thing to put together. There's an easy means of transferring data from SQL Server to a file (CSV, Excel, etc) and sending an email. Additionally SSIS is nice in that it has the Script Task which will allow you to write VB Script to do a variety of things. From my history I've used it to perform data access and send data from one system to another. This will effectively operate the same as if you were to have an application doing the data transfer... but the great thing about SSIS is that you can deploy the package and schedule it run with the SQL Server Agent. In regards to your Show Plan question, I'd recommend running a SQL Profiler trace on your source that you are executing your query against. Since there are several different methods that you could be using to query your data in SSIS, they may all behave differently. Then view the plan of the query that comes up in your trace.

Thanks for your reply! One good reson is when you need to extract data and distribute it by email, then it is a great tool!!

ETL is "Extract, Transform, and Load". Typically, this isn't acheivable all within SQL Server - because that's not typically where all of the data you want to "transform" is located. The source data you want to consolidate, mash, validate (whatever) is quite frequently located in very many disparate locations - flat files, FTP sites, Web Services, other databases, Excel files, and of course maybe a SQL Server. The same can be said for the output - it's not always destined for a relational database. T-SQL can get at and produce many of those things, sure. But it can't get at all of them easily or transparently. By that, I mean understandable by a non-T-SQL expert. SSIS does take learning to understand, and the more used to "typing code" (T-SQL included) the more you'll have to twist your brain to understand how SSIS is intended to work. Maybe your situation is different - everyone's is. IMO, the #1 advantage an SSIS ETL solution has over a T-SQL ETL solution is understandability. Understanding a T-SQL SP compared to an SSIS flow is like understandingwhat a Windows application is supposed to do by reading ten pages of C# versus looking at the Windows Forms Designer. I am in no way saying that it's less complicated in SSIS. You simply can't achieve the "same thing" in SSIS as in T-SQL without having close to the same level of "complexity". But you can CLEARLY see what depends on what. It's very close to self-documenting, which bare T-SQL can't hope to match. T-SQL is a general purpose tool for manipulating RDBMS data. It has some extensions for doing other things, like reading and writing text files and executing external processes - but it's not "great" at that. SSIS is a general purpose tool for manipulating tabular data from heterogenous sources to heterogenous destinations. It has some extensions for manipulating SSAS cubes and performing SQL Server maintenance tasks - but it's not "great" at that.

the main strength of ssis lies in its ability as an etl tool to create very sophisticated etl solutions using very little code. while it is true that pure sql can accomplish the same thing as ssis, creating such a solution would require much more life cycle time than it would require using ssis. hth

Todd! Thanks for giving me the time of your explanations trying to help me. I have some comments though .... Getting stuff into SQL-Server if in Excel, flat files or what so ever is a no problem in most cases,or? I usually use either Access or just simply set up a linked server if that is feasible. 99% of data used in my DW's is possible to get either by ODBC or flat files. So it is simple to just copy them into SQL-Sand have them as RDB Tables, right. By the way there is no tool like Access when it comes to flexibility to handle different types of input and in my view Access as an Office product is much more understandable and userfriendly than I have got the feeling of SSIS. ALL, I SAY ALL DATA MUST BE TYPED!!! Usually the stuff you get is not so well formatted, is it? So far I have never been up to any WEB-service at all (might of course happen in the future... ), FTP is a no problem I mean if you are up with FTP it is just a matter of moving it to your own computer, or? Creating non RDB-stuff, well I found that there could be something into it I have not observed, look at my previous comment. Agree totally that some people like to show their brains by producing non understandable T-SQL. But if you are more normal person, you would probably like to make your system understandable and maintainable by others, so using T-SQL in a way so most of the stuff you do could be displayed in the graphical UI (the designers applied these days) for the SQL. Then you are in a much more high level language situation than using SSIS, because in SQL you tell the computer what you want and then you ask SQL-Server to do it. In SSIS you have to tell the computer how things should be done and then it is your business to see if it is what you get, you need to tell SSIS all the ugly matching etc which you actually don't need to tell i T-SQL. That is why SSIS is much more low-level than SQL. To take an example, if I have a table which I should update with changes since last update, in my opinion such a statement is done with say 20 clicks in the graphical tools for SQL, but in SSIS I would have to use at least 50 clicks. Every situation is special, we are all unique. But sometimes the truth about things is not obvious.... When it comes to how to understand I do not totally agree with you. I agree that the flow displayed by SSIS is compelling compare with boring text in SQL. But when I need to look into details, the interface given by SSIS gives me headache, I can't se the wood for all trees, I have to dig into each box and scroll around for properties I don't understand to really understand what is happening. So in my view error trapping is a night mare. Thank you for your opinion and please excuse my terrible English...

Duane! I like your approach of being convinced in believing in what you have learnt. ETL great call it what ever you like, I am in business of solving business problems independent of any acronym... "Code or not to code" that is the question which have been asked for many years. I think text in some situations is much more effective than GUI, sometimes I believe in the opposite. It all depends on how intelligent the tool is that I have to use, I really dont care about buzzwords. Life cycle time(?), sorry about not being native. If you mean CPU-cycles I agree that if you are keen in SSIS you have the possibility to outperform a computer (Showplan) doing the job, but in that case it will take some of your life cycle time to achieve. And of course how much time it will take for you or me depends of the knowledge we have in each tool. Problem I have is I know SQL, so I wonder if it is worth trying to learn SSIS. You know SSIS and of course your way of solving a problem is much easier done with SSIS. The only time my customer cares about is, if the report is there in the morning when they start working.. If it has been ready 4h35min or 2h22min they dont care. The want it when they need it, period.

Why SSIS?That is kind of philosophical question. SSIS is not more that another ETL tool, so you could also ask yourself why ETL tools? In my opinion ETL tools have close a big gap in data integration projects; and beleive there is plenty of information available in the web about why and where you should use ETL tools.

I know what ETL is for and why. I have been working with ETL at least ten years, ie loading data into a DW from a variaty of sources. Always used SQL either from JET or T-SQL with no problems of doing it running it in night shift. Keeping down volumes and not reloading what was loaded yesterday... So what I am trying to find out is: Will I do a better job if I invest the time needed to learn SSIS instead of keeping to the methodI know, solving these issues with SQL. My idea is to use SSIS where other methods either is missing or not as good as SSIS. So far I have found one reason - when exporting via email it has some nice features. While trying to use it for importing data to a DW I found it much more complicated than pure SQL or SP. I also see a need for if needed running things in parallell for example overlapping some of the stored procedure to do the backbone of the process. Is this a meaningful strategy??

rixol wrote: Life cycle time(?) i'm referring to "full life cycle" of software development: planning, development, testing, documenting, maintainence, etc. ssis solutions are self-documenting when they're created in BIDS. an experienced ssis developer can understand someone else'ssophisticated ssis etl solution much faster than an experienced sql developer can understand someone else's sophisticated sqletl solution. deciphering ssis packages in the package designer is much more intuitive than deciphering sql code. i know this because i have experience doing both.

I think what is missing from the big picture is SSIS Light for the same audience who loved and cherishedDTS. SSIS is a great ETL tool for dedicated senior ITprofessional, but it is definitely not for the casual SQL-er. There is a steep learning curve to master SSIS. DTS was comparetively easy. If you are not a full-time ETL-er, it is difficult to maintain your working currency in SSIS even if you learned it once. The easiest way to start with SSIS is the SSIS Import/Export Wizard. You can save the wizard created package and experiment with it in BIDS.

Some further comments: Regarding sources: Yes, ODBC is a "staple" and T-SQL can handle that just fine. I won't get into pros or cons of Access - because I personally loathe it. Regarding type-strictness: SSIS ismore type-strictthan T-SQL, IMO a good thing. (Not sure where your comment lands you.) SSIS is much more maintainable than T-SQL ETL due to this type strictness and immediate pinpointing of type incompatibilities. SSIS will show you right away exactly where types are incompatible - at design time. T-SQL "may" let you know about type incompatibilities at runtime... maybe. ETL is all about "clean" data - and using a tool that lets you "get away" with what can be dangerous implicit conversions is.. well.. dangerous. Regarding FTP, Web Services, and other external things: We're talking about automated processes here, all in the samedesign environment. You may be able to do all those things with xp_cmdshell, but it's pretty opaque and doesn't tell you when it's broken like SSIS will. Understandability and maintainability: SSIS wins on that one hands down - if you'd only "give it a chance" (which I understand you're seriouslytrying to do). I sympathize with yourleanings -when it comes to"getting things done" in SQL, I tend to use T-SQLrather than the SSMS tools to get them done, simply because we have always had T-SQL - that's what we learned on - and the SSMS comparable actions don't thereforeget learned and ingrained. In C#, Inever makedata-bound forms - Ialways hand crank them,because that's what I'm used to. SSIS beats T-SQL by a knockout here because it's a "type strict compile time" type beast where T-SQL isa "polymorphic interpreted" type of thing. SSIS will let you know when you've broken something, and exactly what you have to fix (but notnecessarily how), whereas T-SQL won't tell you squat. Difference is, you're used to doing the heavy lifting, so it's not heavy to you, and the helpSSIS is giving is unfamiliar and irritating. But on the flip side, SSIS is "new", and the interface is still largely a "version one" type thing. It's likely to get better, and reduce those 50 clicks. It's all point of view - especially on the understandability. I prefer SSIS to T-SQL for ETL any day, because it's so transparent and easy to understand the flow. With a comparable T-SQL process, I would have to read it ALL to make sure I understood it - probably twice. With SSIS, I don't need to read it all. I only need to read the part I need to know about, because it's immediately obvious which part(s) I need to know about. I appreciate that this discussion hasn't turned "religious" - like C# vs VB, or XML vs EDI, or...

SQLUSA wrote: If you are not a full-time ETL-er, it is difficult to maintain your working currency in SSIS even if you learned it once. this is a great point. to maintain fluency in ssis, one is obligated to use the product on (at least)a monthly basis. ssis is pretty easy for someone who's mastered it. however, the knowledge base is so vast that anyone would need to use the product on a regular basis just to remember all the moving parts, let alone how to use them.

Thanks all, so far, and thanks for avoiding religion. I prefer to make decisions based on facts, as I tell my customers. Internet is amazing You need some advice on a strategic issue put it out in a discussion forum and people are there to help you for free!! My story is: I have 20+ years experience of SQL half of the time with MS Access and today almost fulltime with the graphical tools for constructing SQL in SQL-Server environment. (Most of the time I prefer Access query-builder, could be an old habit but I dont think so, it is still simply better and more user-friendly). I work as a consultant and am responsible for DW-design and implementation with a handful of customers representing moderate size companies being subsidiaries in bigger companies. For one of my customers I will in the beginning of 2009 do a changeover of a DW in SQL Server 2000 to 2005 and have to make some strategic decisions on how to do it. It will not be a transition but a migration to tidy things up, a data warehouse which have been in production for almost ten years looks messy. A lot of stuff not used anymore. If you listen to what MS and other people say about when and where to use SSIS I have got the impression it is good for: First and most important, maintain the ETL-process of a data warehouse. Its capability of running processes in parallel if you need that. Very effective to handle great amounts of data with good performance. On the backside looking into it I have found some obstacles to get going: As a programmer I think the GUI is annoying, to see what happens I need to uncover a lot of properties hidden in the boxes. I think it is very hard to find where things really are stored and what all the properties are used for. Looking at code gives me both overview and detail very easy. To me the GUI is an old-time flowchart which is supposed to give an overview, but where everything else is hidden in boxes with some odd features and functions that I dont see in the big picture. This approach I think takes much more time to comprehend and get into. The handling of parameters for the ETL process seams so complicated, just getting a constant from the outside into the package. I listened to a webcast recently where the guy used one hour of explanations on how to achieve such a simple function. The pain to put the package into production seemed for me to be a one day job when you know how to do, so I guess I will need a week to do it the first time. I my world of T-SQL to move to production is just to comment some lines and put some other into action I have always disliked typing of variables; I think machines these days are smart enough to handle these boring functions for me. (Almost religious I agree) Having used the approach of delegating type conversions to the machine have been my way of solving problems for at least 25 years now. And so far I have never gone it the hard found errors everyone who prefers hard-typed languages refer to, I have only saved my customers a lot of my developer time. I do understand that if you ask for hard typing in a system it will be much better on performance, but I dont need that performance in my solutions, all my ETL-jobs are done within an hour and a half, so I have a lot of more hours during night shift. I find it very hard to intuitively take a problem and decompose into the different tasks needed in SSIS; it is not in anyway straight forward. After listening to all good advice so far I have come to a first conclusion on how I will use SSIS in my project: Most of the things I want to do I will construct T-SQL with SSMSs graphical tools which I think these days are good enough. (In 2005 I finally have what I missed so much from Access, CTEs, I just hate SUB-Selects!) I keep to nice programming habits of putting up my own boxes of code in some stored procedures. I will use SSIS to start these procedures and if I need to I could use the parallel executions of SSIS. Have to investigate how it will solve concurrency issues in that cased so I dont run into deadlocks. I will ignore all the messy stuff in importing, matching, script etc which I feel is very cumbersome to get into, secondly to maintain the knowledge of how to do it and thirdly because all this stuff could be done in T-SQL. I really look forward when my customers go for 2008 and I could start to use MERGE. When I need to export or email results from the DW I will consider SSIS and try to find out how to do it. The two books on SSIS I bought, I will put back into the shelf, so I know where they are if I will ever need them.

SSIS is a data movement tool. It does so with the ability to transform data along the way, while being able to handle errors, upserts, etc... It can perform external calls; to a web service for instance. It has the power of using memory buffers. It has the power of lookup, aggregate, sampling, fuzzy logic transformations. It has the power to have multiple data paths, all having their own data manipulation logic. It has the ability to dramatically reduce development time. When transforming data, it has the ability to outperform regular t-sql statements. It has the ability to allow non-sql developers to design ETL-type packages. It has the ability to outperform Informatica. It also allows quick source-destination loading. Using MS Access to import files into SQL Server isn't an enterprise solution; it doesn't work in 64-bit environments. (Jet drivers are 32-bit only) SSIS enforces strict data types... This is the best feature, in my opinion. It enforces data integrity. SSIS does have a steep learning curve to use it effectively. Once learned, it is an extremely powerful tool. DTS it is not. MS Access in no way, shape, or form is a substitute for proper data extraction, transformation, loading experience. It may work, sure. It doesn't write ANSI sql, it isn't a database engine, it's a client tool not a server tool, etc... If you're going to use a query builder, use the one that comes with Management Studio. Writing queries in MS Access language creates a production support issue; whereby the SQL isn't platform agnostic. Depending on what you implement, the SQL may not be able to be executed on native SQL Server, for instance. Those are just a few points I can think of. Phil

Phil! Data movement yes, and WEB-services, so far I have not been up to it. My need of transformation in the ETL-process so far has been possible to handle by means of T-SQL. Look-up for me is a JOIN!! Agregate is GROUP BY!! Multiple paths?? Many tables in a JOIN, Great!! All these stuff is handled by the engine in the best way,I have to only to describe what I want, and the machine is figuringout HOW to do it, the things I don't want to careabout. Highlevel language! With fuzzy-logic you have a point that I will have to investigate. Not obvious it works with Swedish though, which happends to be my native language. Performance I understand could be better than T-SQL if you understand all underlying details, I don't need the performane though. I am not non-SQL so I don't care. When it comes to development time I have seen no real investigation only emotions expressed, so it is in no way proved. When I mention Access I mean JET not the enduser tool in total, even SSIS uses this old part when interacting with Excel for example, this peace of software is more than ten years old!! NO JET - no Excel support. Access has a querybuilder that still beats SSMS even if it has not been changed for ten years, which in my view is remarkable that the SQL-Server team seems almost incapable of constructing a really user-freindly tool, it has gotten better I do agree. Still that builder have some tricky behaviour, why doesn't it keep my layout in the tablearea when I edit SQL "on the fly", to mention one. Every time I have to rearrange my tables to see the joins in the way I want. In Access all my queries and subqueries the layout of the table area is remebered between interactions. And there are more if you want them... Access for importing is quick and dirty, I agree, but unbeaten for one time shots though. Compare the import wizard in Access with the one in SSIS... But for production running every night of course not an alternative. Access is also unbeaten to protoype which is where I use it most and for putting up an SQL-Server database from nothing as a starting point, thenyou could easaly move your tables and the queries you have developed with care of course, could be moved one-by-oneup to SQL-Server, but as you say it is not compatible, which I think is a shame. The SQL-Server team has owned JET for almost ten years without putting any effort into it, that is the real problem. Thanks Phil for taking the time, Fuzzy-logic and care of using the right tool in the right circumstances. I totally agree.

rixol wrote: Phil! Data movement yes, and WEB-services, so far I have not been up to it. My need of transformation in the ETL-process so far has been possible to handle by means of T-SQL. Look-up for me is a JOIN!! Agregate is GROUP BY!! Multiple paths?? Many tables in a JOIN, Great!! All these stuff is handled by the engine in the best way, if I have to only to describe what I want, and the machine is figuringout HOW to do it, things I don't want to careabout. Plain and simple: We aren't going to force you to use SSIS. Use whatever tool works. I would encourage you to learn SSIS though, because I think you'll be surprised at what you've been missing out on. A look-up is similar to a join, but quite different. In SSIS, you can take different actions depending on if a record was found or not. You can't do that in t-sql, at least not as fast. Using GROUP BY aggregates, yes. However, in SSIS you may use a Multicast component to split the data flow into multiple paths so that you can aggregate a count of records, perhaps, and union them back to the main data path so that each record has the count of all records. Or, each data path could go to a different destination or other action. SSIS also allows you to join tables from different databases (SQL, Oracle, etc...) and from potentially different servers. You can do this generally with t-sql using linked servers, but it will be slow. Again, if you're unwilling to sit down and learn SSIS and use it for a few weeks, there's really no point in continuing listing the benefits. Use a tool that works for you and that is supportable by all members who may need to support it. Also again, you say you use JET. As I said earlier, there is no JET driver for 64-bit machines. If SSIS is to consume an Excel file or Access database, then yes, it must use 32-bit. However, as someone who manages a multi-terabyte data warehouse, Access and Excel sources have no real value - largely because they do not enforce data types strictly and allow business users to not follow defined layout standards. Consuming Excel files creates more work than it is worth, even though unavoidable at times. As I stated before, writing queries in Access creates issues with the query not following real database language standards. If you're only going to run the queries in Access, then so be it. But if you write in Access and copy-n-paste to SQL Server, you're setting yourself up for maintenance issues as a result of Access allowing commands not allowed in most other database engines.

Phil, Thanks for your engagement. I don't have a terrabyte warehouse, but I believe if I run to one, SSIS is a much better way to go. I am working with DW in the size of 10-50 Gb, there are a lot more of thoses than terrabytes, right. So many of your point is not really feasable on my situation. That is why I wonder which path to take, I really don't see all the good things you see from a terrbyte-perspective in my little world in the north of Scandinavia. I have never understood why big companies construct those gigantic warehouses meant to solve all subsidiares' problems. I work in those subsidiares and they never use the enterprise solution because it doesn't fit. The build their own DW, beacuse as you say they don't get the results in the way they want, ie Excel. The get a report that they could print - how useful is that? If they are lucky they could get the numbers into Excel without formatting but then they need a lot of skill to get it and to make it useful. Anyhow your description convices me in a point I have had for a long time SSIS is for terrabyte, is that a correct conclusion?

rixol wrote: I have never understood why big companies construct those gigantic warehouses meant to solve all subsidiares' problems. I work in those subsidiares and they never use the enterprise solution because it doesn't fit. The build their own DW, beacuse as you say they don't get the results in the way they want, ie Excel. The get a report that they could print - how useful is that? If they are lucky they could get the numbers into Excel without formatting but then they need a lot of skill to get it and to make it useful. Many companies fail to implement reporting correctly. Many do. There are many reporting solutions out there that do what you're after, Business Objects, Microstrategy, SAP, Crystal Reports, etc... How the data is loaded is a completely different topic than users being able to get data out in a way that meets their needs. rixol wrote: Anyhow your description convices me in a point I have had for a long time SSIS is for terrabyte, is that a correct conclusion? No, not a correct conclusion. Using SSIS as the right tool for the right job is the correct conclusion.

My last point, I promise. I am really happy in your engagement, which may make my mind to flip. My point about big companies terabyte DW is that those solutions are often felt less valuable than expected out in the subsidiaries, I see this effect very often and that does not depend only on the way data is served, in many cases the cubes offered are to general or not feasible for the situation in that special subsidiary, they might need other KPI's not offered in the general solution etc etc. But my view is of course is limited since I work in Scandinavia, wheremany things are smaller. In the last sentence I was a little bit fuzzy, what I meant was that most of the features offered by SSIS has bigger valuein the big DW compared with in the small, making it more right more often there. I know I have my own free will, but when I make strategic decisions for my clients I want to haveit well investigated, it is not what I want it is what is best for the client that counts.

Phil Brammer wrote: Using SSIS as the right tool for the right job is the correct conclusion. Phil, How about "SSIS Light" for the average Joe the SQL-er?

Monday Monday Thanks all you guys for your advice, my conclusion was to give SSIS another try this morning. So I set up a remote desktop to the server where VSBI is installed and where I made my efforts two weeks ago. I did some trials and created two small projects, which did some simple stuff. This morning when I try to open them I receive the following message from VSBI: Microsoft Visual Studio.The application for project\\eslmfs.....\MovusStage.dtproj is not installed. Make sure the application for the project type (.dtproj) is installed. OK The application is not installed?? I have not taken it away and I am the only one using this server?? Who has removed it?? OK this stuff was not important so I try to start a new project: Microsoft Visual Studio. Exception has been thrown by the target of invocation. OK ??????? Nice now I know exactly what to do. Obviously will someone above try to help me to understand that my guts feeling is correct AVOID THIS STUFF, it will take you a massive investment in time and effort to reach some minor functions I have lived without for so long. Goodbye SSIS and VSBI! It is not for Mr Average like me coming from a small country in Scandinavia, it must be for the rich guys who could afford wasting their time on not working solutions. Seems I am not alone: http://blog.anildesai.net/?p=248

Hi Rixol - I'm not answering your above question (actually, after reading it, I guess I am)... just wanted to weigh in on your original question/premise.I, too, have 10 years of ETL experience via "hand rolled" projects. I've used VB.Net/SQL/TSQL/whatever to do ETL long before I knew that ETL was an acronym. Most of my work were either "one offs" (one time imports of data from some source to SQL Server), or were set up to run daily. The projects that would run daily were set up as DLL's that a Service Project would schedule/run.I've never used DTS. A royal PIA (pain in the, uh, arse) to me. There are two reasons why I am switching to (and learning ... ) SSIS. 1) It's the future. I'm not going to fight the future. I'm going to learn it. Like you, I've been rather set in my ways. Unlike you (probably), I'm worried about my future employment! Knowing SSIS makes me more employable.2) SSIS does seem to handle larger data sets better. I know my prior work could bog down when larger data sets got involved.So... SSIS it is for me. I need to get back to my studying. I've had a month or so off bc/ my home PC died - I'm just now getting the environments set back up.Good luck to you (and me for that matter)!ScottSanford, Maine

Scott04073 wrote: 1) It's the future. I'm not going to fight the future. I'm going to learn it. Like you, I've been rather set in my ways. Unlike you (probably), I'm worried about my future employment! Knowing SSIS makes me more employable. great point! i don't see the growing demand for ssis developers slowing down anytime soon. ssis is rapidly gaining in mindshare, which, it itself, makes learning it worthwhile investment for ETL developers. as a matter of fact, i expect some time in the future that ssis will pass informatica as the market leader in ETL tools.

Here's a great article by SQL CAT that discusses the Top 10 SSIS Best Practices. Some of the Top 10 relate to using SQL Server over SSIS. http://sqlcat.com/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx CheersJeffSQL Server MVP / WARDY IT Solutions, Solutions Architect

Duane Douglas said: i don't see the growing demand for ssis developers slowing down anytime soon.Let's keep track of it. On Dice title only search for SSIS - 28 hits. Much more if body search.Link: Search Dice.com for SSIS jobs in title.Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com

Duane Douglas said: i don't see the growing demand for ssis developers slowing down anytime soon. Let's keep track of it. On Dice title only search for SSIS - 28 hits. Much more if body search. Link: Search Dice.com for SSIS jobs in title . Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com Almost 6 months on there are 49 hits. My twopenneth? SSIS is another tool in your toolbox that you can use if you want. Its another option.http://jamiethomson.spaces.live.com/ | http://blogs.conchango.com/jamiethomson | @jamiet

Duane Douglas said: i don't see the growing demand for ssis developers slowing down anytime soon. Let's keep track of it. On Dice title only search for SSIS - 28 hits. Much more if body search.Link: Search Dice.com for SSIS jobs in title . Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com Almost 6 months on there are 49 hits.My twopenneth? SSIS is another tool in your toolbox that you can use if you want. Its another option. http://jamiethomson.spaces.live.com/ | http://blogs.conchango.com/jamiethomson | @jamiet i wouldn't just search dice as it's only one of many sources of job listings. a better barometer (imo) is simplyhired.com a simplyhired.com ssis title only search - 211 hits link: seach simplyhired.com for SSIS jobs in titleDuane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.

a few more <a href="http://www.linkup.com/results.php#q=SSIS">SSIS jobs</a> on Linkup.com

I've only ever used SSIS for loading and exporting data to CSV/Excel/zip whenever there is a need to periodically send/receive data. I also used DTS quite a bit before SSIS came along. Otherwise I've found that beyond the initial load from file/source, all of the manipulations and logging I do during staging in ETL are much easier and faster using set based SQL. I attempted to use SSIS for this, but it seemed to just get in the way. It didn't handle changes to the database schema gracefully. I found myself having to delete things and rerun wizards, which meant any manual tweaking/custimizations/parameterizations I had previously made would need to be done again from memory. This is why I hate tools like SSIS, because all the really helpful/productivity geared features are in wizards, and wizards are only useful on initial creation of something. Instead I use a Visual Studio Database Project. All my ETL is driven by stored procedures and I can easily find/replace whenever there are changes in the database. If I worked in an environment where there was more than just me working on the DW and there was a need for source control/versioning/branching, I can't imagine what a headache it would be to try and do comparisons between SSIS projects. With a Database Project everything from DDL to DML are in some form another SQL that comparisons can be done on. With SSIS I was struggling to do things that were fairly simple in SQL, and the solutions I found to these problems in SSIS felt like dirty hacks. I could have just used tasks that ran my SQL scripts, but I didn't see much benefit to burying my SQL inside tasks where I can't easily search them or put extra steps in the way whenever I want to make edits. Overall, I think the concept of SSIS is good, but I think maybe the approach/implementation is bad.

I've only ever used SSIS for loading and exporting data to CSV/Excel/zip whenever there is a need to periodically send/receive data. I also used DTS quite a bit before SSIS came along. Otherwise I've found that beyond the initial load from file/source, all of the manipulations and logging I do during staging in ETL are much easier and faster using set based SQL. I attempted to use SSIS for this, but it seemed to just get in the way. It didn't handle changes to the database schema gracefully. I found myself having to delete things and rerun wizards, which meant any manual tweaking/custimizations/parameterizations I had previously made would need to be done again from memory. This is why I hate tools like SSIS, because all the really helpful/productivity geared features are in wizards, and wizards are only useful on initial creation of something. Instead I use a Visual Studio Database Project. All my ETL is driven by stored procedures and I can easily find/replace whenever there are changes in the database. If I worked in an environment where there was more than just me working on the DW and there was a need for source control/versioning/branching, I can't imagine what a headache it would be to try and do comparisons between SSIS projects. With a Database Project everything from DDL to DML are in some form another SQL that comparisons can be done on. With SSIS I was struggling to do things that were fairly simple in SQL, and the solutions I found to these problems in SSIS felt like dirty hacks. I could have just used tasks that ran my SQL scripts, but I didn't see much benefit to burying my SQL inside tasks where I can't easily search them or put extra steps in the way whenever I want to make edits. Overall, I think the concept of SSIS is good, but I think maybe the approach/implementation is bad. Interesting comments Aaron, many of which I agree with. I'm a huge fan of VSDB projects too and see a lot of advantages in writing T-SQL sprocs versus SSIS dataflows. the SSIS tooling also has a long way to go One thing you said puzzled me though. "Overall, I think the concept of SSIS is good, but I think maybe the approach/implementation is bad." Well the concept of SSIS is a database agnostic tool which can move data between heterogenous data sources. All the things that you describe above are, in my opinion, artifacts of that concept rather than being due to a bad implementation of it. As an aside, there is one absolutely fundamental difference between SSIS dataflows and T-SQL that I don't think is stated clearly enough. T-SQL is a declarative way of moving data about - by that i mean you tell the engine what to not how to do it - that's the job of the Query Optimiser. SSIS is wholly imperative - with SSIS you are the query optimiser - it is you that decides on the best on moving data from A to B. It is this fundamental difference couple with the agnostic nature of the tool that, in my opinion, leads to some of the issues you talk about here Aaron (e.g. not elagantly handling changes to the schema). That's not a good thing or a bad thing - its just the way that SSIS is, and for good reasons. http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me