InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 18:01:44 GMT20http://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxHi Greg, That is what I am saying, yes. You can develop SSIS packages on any machine just by installing the Microsoft Business Intelligence components. As far as I know, the same licensing applies to SSMS. Hope this helps,AndyMon, 01 Apr 2013 06:14:59 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxHey Andy,Thanks for the reply. That was how I read all of the Microsoft doc, but our "software police" seem to insist that a SQL license/install accompany BIDS or SSIS on any machine, which makes no sense to me. I can attach to whatever servers I need to, develop the packages on SSMS on my workstation, and then install/store them on the actual server where they need to live, as I see it. And it sounds like that's what you're saying too.OK, many thanks. This is all pretty new to me, compared to stored procedures. Just haven't had the need to get too heavily into it before.Thanks again.GregMon, 01 Apr 2013 05:14:08 GMTgregweigoldhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxHi Greg, You can develop SSIS using Business Intelligence Development Studio (for 2005, 2008, and 2008 R2) or SQL Server Development Tools (for 2012) on any server with the workstation tools installed. You cannot execute packages outside the development environment without a SQL Server relational engine license. For this reason, I refer to SSIS as "free" (in double-quotes). Hope this helps,AndySat, 30 Mar 2013 19:36:20 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxHey Andy, I'm just coming into the world of SSIS and I'm fighting my corporate DBA's about it.Can you answer a fundamental question? And I know this probably isn't a good place for this, but...If I install SSIS on a server that doesn't have SQL Server on it, just SSMS, do I need a full SQL license just to use SSIS to convert some DTS packages? They will eventually run on SQL2008, but we're waiting on the new servers at this point. Based on lots of postings, I don't believe so. And Microsoft literature seems to be contradictory on the matter.ThanksGregFri, 29 Mar 2013 05:21:20 GMTgregweigoldhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxHey Andy, I'm just coming into the world of SSIS and I'm fighting my corporate DBA's about it.Can you answer a fundamental question? And I know this probably isn't a good place for this, but...If I install SSIS on a server that doesn't have SQL Server on it, just SSMS, do I need a full SQL license just to use SSIS to convert some DTS packages? They will eventually run on SQL2008, but we're waiting on the new servers at this point. Based on lots of postings, I don't believe so. And Microsoft literature seems to be contradictory on the matter.ThanksGregFri, 29 Mar 2013 05:20:39 GMTgregweigoldhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxAs a prod DBA I've been a bit snobby and dismissive of SSIS in the past (even preferring DTS!) but with some clients I've had no choice but to get right in there and debug failing SSIS packages, but I will conceed it's a good feeling when you get them working. Will be following this series closely, knowledge is power and all that. ;-)[b][i]qh[/i][/b]Mon, 26 Nov 2012 04:42:51 GMTquackhandle1975http://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxOne suggestion - at the bottom of each tutorial, could you add a link to the next tutorial? I know it's minor, but I hate having to go back to the menu and then click the next item, rather than just moving on to the next step.Mon, 22 Oct 2012 12:08:31 GMTjoresnikhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspx[quote][b]joannschlosser (10/22/2012)[/b][hr]...was told in a SQL class that I should "just use SSIS," accompanied by a bunch of shaking heads.[/quote]Hi Joann, Please drop me an email at andy.leonard@gmail.com.Thank you,AndyMon, 22 Oct 2012 09:51:57 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxNot a complaint! An observation and I am grateful for the help I get from you guys. I will take a look at it this week and get back to you. The project I have in mind is one where we (I) have to manually download a set of data every week and import it into our db, replacing the current data and was told in a SQL class that I should "just use SSIS," accompanied by a bunch of shaking heads. Will let you know how it goes. Thanks!Mon, 22 Oct 2012 04:32:30 GMTjoannschlosserhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspx[quote][b]dataman777 (10/12/2012)[/b][hr]Andy:I have been working with SSIS for some time and basically learned most of the tricks the hard way. I'm looking forward to your future articles and would like to suggest some topics to include. 1. When working with loading data from file servers into SQL go into detail with some practical examples of parsing excel files with multiple worksheets from remote shares. This is a common practice as many small companies keep files all over the place and want them parsed for BI.2. When working with data flows go into detail and show some good examples of properly setting up and utilizing iterations "running processes through a loop container" this was a difficult task for me to figure out and I'm sure others as well.3. Package variables. Most examples of setting up package variables and vague. Please go into depth on some reasons why one would use package variables and how to properly set them up, and most importantly... how to troubleshoot them when the fail. This leads to my final wish list item.4. Troubleshooting package errors. Examples of the most common errors, what they mean, and how to correct them is most warranted.Thanks and keep up the great work.[/quote]Hi DataMan777, Thank you for providing detailed feedback and for your kind words or encouragement. In the later articles, I strive to show more errors that can occur and explain what they mean. Troubleshooting SSIS packages during development is often time-consuming because some of the errors provide non-intuitive feedback. The great news is: Nowadays, you can copy that error into a search engine and likely find information from someone who has experienced something similar. (Note: that was not the case when [i]I[/i] was learning SSIS... get off my lawn! But I digress...) I am outlining more data flow patterns for the next phase of this series; your timing is impeccable. In the meantime, you may want to work through the examples I provide in [url=http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/64014/]SSIS 101: Object Variables, ResultSets, and Foreach Loop Containers[/url] as it deals with the coupling between Variables, Execute SQL Task Resultsets, and the shredding ADO Recordsets / ADO.Net Datasets using the Foreach Loop Container. This article touches on several of your points above.:{&gt;Fri, 19 Oct 2012 20:52:23 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxHi ThisIsFutile, All magazines, whether online or in print, follow formatting rules. SQLServerCentral.com has been around for years and has been following the same publishing pattern for nearly that long. Another way to look at this is: Now you have nine articles instead of merely one. Nine is better than one, isn't it? it is for me, especially when I want to learn more about a topic new to me. While I read your frustration at not realizing this was a reprint when you first read this article, I cannot say I understand it. You did not know things are the way they are, and now you do. As a person who works in this industry (and has for decades), I believe a tolerance for learning things one doesn't know comes with the job and the territory. My advice (if I may be so bold): Enjoy the free, quality material posted at SQLServerCentral.com. Learn and grow with us. I would be willing to venture you may one day submit an article for publication here. If / When you do, I hope others extend courtesy to you.AndyFri, 19 Oct 2012 20:43:54 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspx[quote][b]joannschlosser (10/15/2012)[/b][hr]My answer to the question,'“I’ve Never Used SSIS Before…” Are you sure about that? Do you recognize this screen? 'is NO.What step did I get dropped in at?[/quote]Hi Joann, I appreciate your feedback and frustration. I have added more material near the end of this article in the hope that it offers some clarification for beginning an SSIS project from scratch with no experience. That was my intention in writing this series, to help those who have never used SSIS learn the platform. The new section begins with "Play Along at Home." Could you do me a favor? Will you start there and see if you are able to create an SSIS project? I would like to know - either way. People learn differently, and no one learns "wrongly" in my opinion. This is one reason why writing pragmatic tutorials can be challenging. Another reason is authors of tutorials (me, in this case) often do not remember what it was like back at the beginning when they were just starting to learn something. We "don't know what we know" - at least sometimes. And so we overlook some parts and just leave other parts out. I assure you it is not intentional. And the last thing I want to read is a complaint such as yours. But, your feedback motivated me to take a fresh look at the material and add more content. Hopefully, this improves the value of the material for you. But I will not know unless you tell me. AndyFri, 19 Oct 2012 20:35:37 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxHi Everyone,I corrected the typo in Level 2 and added a link to the first step (Level 1) of this series. I also updated Level 1 to that it walks you through creating the initial SSIS project.I accept full responsibility for any confusion and I apologize. I hope many will enjoy this series and learn more about SSIS by reading it and building the solution.Thanks to everyone who provided feedback - please continue.:{&gt;Fri, 19 Oct 2012 20:26:36 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxPeople tell me all the time, "oh, SSIS can be used to do that." So I thought WONDERFUL when I read your “Why can’t someone write a practical tutorial;” you wonder, “one that gets me started quickly and then explains the more advanced concepts?” I am always griping about being dropped into the middle of a "solution" at step 10 that assumes I know about steps 1-9. Grrrr.My answer to the question,'“I’ve Never Used SSIS Before…” Are you sure about that? Do you recognize this screen? 'is NO.What step did I get dropped in at?Mon, 15 Oct 2012 08:01:41 GMTjoannschlosserhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxI've been on the SQL Server Central mailing list for some time (5 or 6 years?) and I've always been confused why recycled topics like this one get sent out as if they're brand new articles. I noticed dataman777 posted a reply thinking this was new and I've done it too...heck, I was going to do it on this one until I read the first post and it coincidentally mentioned the dates. Perhaps something can be done to warn the user that this is an old article? It's a great article and I'm actually glad I don't have to wait to read the other steps (I apparently missed it the first time around), but I can't help but think we can be warned first. Obviously, it's easy to read the dates and see it's outdated but that doesn't stop new (and sometimes older) users from posting to it like it's brand new.I know this is off-topic for the original article, but since it's being recycled in the email list I feel this is a fair question now...Fri, 12 Oct 2012 09:25:18 GMTthisisfutilehttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxAndy:I have been working with SSIS for some time and basically learned most of the tricks the hard way. I'm looking forward to your future articles and would like to suggest some topics to include. 1. When working with loading data from file servers into SQL go into detail with some practical examples of parsing excel files with multiple worksheets from remote shares. This is a common practice as many small companies keep files all over the place and want them parsed for BI.2. When working with data flows go into detail and show some good examples of properly setting up and utilizing iterations "running processes through a loop container" this was a difficult task for me to figure out and I'm sure others as well.3. Package variables. Most examples of setting up package variables and vague. Please go into depth on some reasons why one would use package variables and how to properly set them up, and most importantly... how to troubleshoot them when the fail. This leads to my final wish list item.4. Troubleshooting package errors. Examples of the most common errors, what they mean, and how to correct them is most warranted.Thanks and keep up the great work.Fri, 12 Oct 2012 08:54:53 GMTdataman777http://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspx[quote][b]sajjanmukesh_83 (10/12/2012)[/b][hr]Great Article! I feel this is the platform to learn more about SSIS.Looking forward fot the series!![/quote]If you got here from an email today (and if you didn't notice that this article is being recycled...I didn't at first), you'll be happy to know that the rest of the series is already completed. You can find it here: http://www.sqlservercentral.com/stairway/72494/I too am looking forward to reading them.Hope this helps.Fri, 12 Oct 2012 07:52:48 GMTthisisfutilehttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxGreat Article! I feel this is the platform to learn more about SSIS.Looking forward fot the series!!Fri, 12 Oct 2012 00:11:03 GMTsajjanmukesh_83http://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxHi SQL Oracle, In the Control Flow toolbox, there is a group labeled Maintenance Plan Tasks beneath the Control Flow Items group.Hope this helps,AndySun, 11 Mar 2012 13:02:32 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxAndy:In the level 1: you showed the screenshot where the controls under the Maintenance Plan Tasks are enabled. Could you explain briefly how you did it? The controls in my system are all grayed out.Many thanks in advance.Fri, 09 Mar 2012 14:47:08 GMTSQL ORACLEhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspx[quote][b]Dave-739983 (3/30/2011)[/b][hr]Does anyone know if anything similar has been done for Analysis Services... [/quote]Hi Dave, Check out [url=http://www.sqlservercentral.com/stairway/72404/]Bill Pearson's Stairway to MDX[/url].:{&gt;Sun, 03 Apr 2011 13:06:12 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspx[quote][b]kwoznica (3/30/2011)[/b][hr] You mention C# as a scripting tool in conjunction with SSIS. How important is it to know C# for SSIS and what shortcomings do you face without it?Is it difficult to take queries I have successfully run and transport them into an ssis package to be run as a job.[/quote]Hi kwoznica, You have the choice of C# or VB for scripting in SSIS 2008 and 2008 R2. It's not mandatory to know how to script to write SSIS packages, but knowing scripting adds another tool to your toolbelt for building SSIS (and other) solutions. I recommend you learn as much as you can. You can use the Execute SQL Task to run queries in SSIS. Most developers mature as they work with a language or platform - their coding style evolves. Many SSIS developers start learning SSIS by building packages using the Import and Export Data Wizard. This wizard actually creates an SSIS package in RAM and then executes it. You can save the package and open it for editing or re-execute it later. Other SSIS developers begin by using only Execute SQL Tasks for T-SQL operations. They use the SSIS Control Flow as a "workflow engine" (it's an excellent workflow engine!) and learn the Data Flow later.:{&gt; AndySun, 03 Apr 2011 13:03:08 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxAndyThis is great stuff... Eager to read the rest....Thu, 31 Mar 2011 06:48:22 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxGood stuff. Looking forward to the rest of the articles to improve my SSIS knowledge.Thu, 31 Mar 2011 03:51:54 GMTJoachim Seminckhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxHi Andy,Great article I am dying to know more about SSIS, looking forward to read the rest of the series.thanks buddy great work Wed, 30 Mar 2011 23:51:34 GMTghanshyam.kunduhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxAndy,Great article i am dying know more about SSIS, looking forward to read the rest of the series.thanks buddy great workWed, 30 Mar 2011 23:42:22 GMTghanshyam.kunduhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxThanks Andy, I am looking forward to the next step in the series.Wed, 30 Mar 2011 16:17:05 GMTMeet George Jetsonhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxLove your work Andy... look forward to reading the subsiquent articles.Does anyone know if anything similar has been done for Analysis Services... "now there is one puppy I have trouble understanding."DaveWed, 30 Mar 2011 16:16:35 GMTDave-739983http://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxThank you! I Look forward to Step2 and beyond. I work at a place that provides very little technical training, so this type of article is invaluable.Wed, 30 Mar 2011 10:07:36 GMTvsolomonhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxGood article Andy, I look forward to reading the rest of the series..CEWIIWed, 30 Mar 2011 09:50:06 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspx[quote][b]jberg-604007 (2/17/2011)[/b][hr]Hi Andy, Any chance that you can move your articles out of the future and into the present? The first article has a posting date of 2011/03/30 and this is only 2011/02/18. Thanks for articles and allowing us to go back to the future to read them!Jim[/quote]I had the same initial thought. I thought. But my take was, FINISH THE SERIES NOW!!! :w00t: Great article to start off the series.Wed, 30 Mar 2011 09:21:18 GMTRead Ithttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxAndy, Great article and I am looking forward to the rest of the series. I am just starting to learn SSIS as my company is looking for faster ways to view data. I am hoping to run SSIS packages which reads data and dumps it into an excel file which can be viewed by managers. We are finding reports from our ERP to be very slow and the time it takes to run queries which produce the same data is 10 times faster. I just have a couple of questions for you. You mention C# as a scripting tool in conjunction with SSIS. How important is it to know C# for SSIS and what shortcomings do you face without it?Is it difficult to take queries I have successfully run and transport them into an ssis package to be run as a job?Thanks for the article and your reply.Wed, 30 Mar 2011 07:00:04 GMTkwoznicahttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxThanks for the article - I look forward to the next ones.I try and use SSIS for any new ETL functionality we need, but as for having the time to migrate our hundreds of old DTS packages, forget it. Whatever Microsoft want, DTS will still linger on for a few years yet it many organisations I think.Wed, 30 Mar 2011 03:24:43 GMT99zardozhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxNice introductory article, Andy! Can't wait for the rest of the series.Tue, 29 Mar 2011 23:59:48 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxHi Andy, Any chance that you can move your articles out of the future and into the present? The first article has a posting date of 2011/03/30 and this is only 2011/02/18. Thanks for articles and allowing us to go back to the future to read them!JimThu, 17 Feb 2011 11:15:52 GMTjberg-604007http://www.sqlservercentral.com/Forums/Topic1063676-208-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Integration+Services+(SSIS)/72492/">What is SSIS? Step 1 of the Stairway to Integration Services </A>[/B]Mon, 14 Feb 2011 10:37:06 GMTAndy Leonard