I have saved an SSIS pacakage on my sql server. I am able to see and run the package through Integrations services. My question is, is there a way to edit the package through Management studio? Thanks Jim

sorry, no. Currently you cannot load a package from Mgt studio for editing. From Mgt studio you can see whats running, whats stored, and can execute a package but not edit.

Naturally you can import the package from the server into your solution, edit the package in the solution + test and then save to server.

Werener.. thanks for the reply. I am assuming by your post you mean that I have to use VS2005, Business Intelligence Dev Studio? I did see what you mean by importing it. Thank you. Jim

How exactly does this work? After I edit the package in BI studio, the only option is to save it as a file on the file system. And the import/export wizard appears to be for just importing/exporting data. If I right click on the SSIS packages folder in the Solution Explorer, the import/export wizard is the same one for importing/exporting data. There's no data source you can select for SSIS packages. This is highly frustrating as any SSIS package I've saved to the server from the Data Import/Export wizard is uneditable at this point.

As Craig pointed out we do not enable editing of deployed packages. This is to keep a clear distinction between packages still in development and packages which are tested and in production. The best practice would be do work in the development environment then when complete and tested move production copies to the server by deploying them. When changes have to be made, they are made to the original development packages in the dev environment - once tested, copies of the modified packages would then be deployed to the server over the existing production packages. As you have found, it is still possile to use the Import/Export wizard in the development environment for convenience. However, some users have simpler scenarios - they build quick packages for simple import/export jobs and save them directly to the server. For such scenarios, it may be easier simply to run the wizard again with new options and deploy new versions to the server - certainly some administrators prefer this simpler approach to working in the development environment. You may have a third case, where you have created packages in the wizard, and need to make changes to them that cannot be accomplished simply by running the wizard again - perhaps introducing some logic that the wizard does not cover. In such a case, use the Export option in SQL Server Management Studio (right click in the Object Explorer for Integration Services should have that option) to save a server package to the file system. Now, in the BIDS environment, you can start an SSIS project and using Add Existing item, you can add the file system package to your project. This last case is really only worth the effort if (a) you have logic that cannot be supported in the wizard and (b) you wish to follow the develop-test-deploy practices. For many simple admin cases, where SSIS is being used just an import / export utility, it would be easier just to run the wizard again and deploy new packages. hth Donald Farmer

OK! Everything understood. In DTS 2000 you can see the package in the left menu. In 2005 you can't. Or maybe I do something wrong when i import the package to Mgm Studio ? There are 2 methods to import: file system and sql server. I tried to import inside the sql server (because I was thinking I can see it after in the menu, but ...) and run a job every day for this package. Problem: My job don't run or run with "Package can't be found or loaded" error. Any ideea?

I really miss good ole DTS packages. The old days when you could write a process and edit it without running through a ton of unnecessary hoops. Microsoft is a tools provider. It isNOTMicrosoft'splace to sayHOW we use the tools. Keeping a clear distinction between development and product is something that I am supposed to make decisions on without being told that I have to do it a certain way. Your response to "hey, this is needlessly complicated and well, stupid." is "it may be easier simply to run the wizard again with new options and deploy new versions to the server - certainly some administrators prefer this simpler approach to working in the development environment." If it wasn't written down I would not believe that you said it. SSIS is a definate step back from DTS.

I have been working with SQL Server extensively in a DBA capacity since version 6.5. Since Microsoft introduced SSIS it has been a nightmare. Microsoft - in future, if you are going to entirely change the paradigm under which exisiting functionality operates then you MUST provide an obvious, intuituve interface for users to be able to achieve what they used to do before. Finding documentation to explain how to perform simple DTS tasks in SSIS has been impossible. Even the responses to this forum from Microsft representatives have been sketchy and elusive. Lift your game Microsoft. Here is what I have worked out so far. No, you cannot edit an SSIS package in SQL management studio. Yes you must use visual studio to edit a package. It would appear you cannot use visual studio to edit a package that is stored in a SQL database. Is that true? Yes you can export a package to a file and edit using visual studio. Apparently you can test a package while you are editing it, so you dont need to import it into SQL first? It would appear that you cannot edit or create SSIS packages using the standard visual studio. You must use the one that is installed with SQL. Is that correct?

Actually...I cant work out how you run an SSIS packae while editing it within Visual Studio. Is this actually possible? When I select the help it defaults to some topic about smart devices. I am a SQL DBA, I am not interested in Smart Devices. Nor am I interested in Office Development, Foxpro, C#, J#, F#major or any of the other help topics in visual studio!!!! Microsoft - If you want to force SQL DBA's to use visual studio for editing SSIS packages, then make it relevant and easy to use. Where is the help topic on SSIS in visual studio???????????

LeeMatt wrote: Actually...I cant work out how you run an SSIS packae while editing it within Visual Studio. Is this actually possible? When I select the help it defaults to some topic about smart devices. I am a SQL DBA, I am not interested in Smart Devices. Nor am I interested in Office Development, Foxpro, C#, J#, F#major or any of the other help topics in visual studio!!!! Microsoft - If you want to force SQL DBA's to use visual studio for editing SSIS packages, then make it relevant and easy to use. Where is the help topic on SSIS in visual studio??????????? Push the green "start button, F5 key, or use the Debug->Start Debugging menu item to execute the package.When viewing help, in the tree on the left, collapse everything, go to the root (Books Online) and then expand Integration Services.Also use the FAQ link at the top of this forum for tips and techniques for beginners. (Including tutorials)

One thing to be aware of - you need to open the package in the context of a project to debug it. If you have exported the package from SQL Server, you can create a new Integration Services project, and add the exported DTSX file to the project.

Thanks. I managed to sort that one out. The run button is disabled in VS unless you create a new project, and then add the dtsx file to the project. Again, a process that Microsoft has provided no documentation on at all...well, none that I can easily find anyway. And yes, some information on SSIS is available in SQL books, but its mostly at a conceptual level rather than practical. My point was that visual studio is the chosen interface for creating and editing SSIS packages. Yet there is no practical help topics on SSIS in visual studio...well, no topicsat all in fact. Anyway, my rant is over. Thanks againfor your help.

Donald, As stated by at least another in this set ofposts, you and/or Microsoft is not clearly stating or explaining SSIS in general. For example you stated: "...we do not enable editing of deployed packages." What do you or MS mean by deployed in relation to SSIS packages? How does a package get deployed? Is a package considered "deployed" if we create it in the wizard and save it to a SQL server?? If so, how would you create a SSIS package and not deploy it, but still have the ability to view it and edit it? I miss the 2000 DTS stuff. Also... when you save an SSIS package to a SQL server, where does it go? How do I find and access it? I have found no information searching the help or online that explains the whole SSIS mess! Furthermore, each release of SQL Server since 6.5 has become more difficult to use as far as creating tables goes. Case in point, in 6.5/7.0 I could right click, choose new table, then just start typing and tabbing my way through the columns and rows provided to create my fields and set my field settings. Then, inSQL 2000, MS move part of the field setting information to the bottom of the table creation window, like it is in Access,what a pain to have to stop at each field, grab my mouse, click on the bottom part of the window, finish filling out my field information, then have to grab the mouse again to go back up the grid to start my next field/column. I cursed the MS person who made that decision every time I have to create a new SQL Table. Then I really did not think it possible or that it would happen, but MS made creating tables in SQL Mgt Studio even MORE DIFFICULT and time consuming then in previous versions. Right-click, choose new table, type your field name, hit tab, type in your data type, then tab, and hit spacebar to toggle the allow nulls, hit tab and your are creating your next field/column; but wait, I want my first column to be an identity column, so I have to take my hand of the keyboard, slowing me down, grab the mouse and go to the bottom of the window, try to grab that stupid divider handle and resize the bottom window so I can actually see what is displayed there. Then I still cannot set the identity column, I have to click on this LITTLE plus sign to expand the Identity setting, then double click on NO to make it YES, and finally I can return to the grid above to start my next field/column, god forbid that I have to set defaults or any other extended information for those fields as well, or I might be here all day creating a SQL table. (I know there is a toolbar shortcut for identity, but not for the other features in the extended window area). Did MS even field test this interface? Did they even use it? Can any MS employee that uses SQL on a daily basis like I do, look me in the face and tell me they are more productive with the SQL Mgt Studio over SQL 2000 Enterprise Mgr???? That fact is that I am a Microsoft customer and have been for years, and now I am seriously considering moving my organization away from Microsoft products, as they are becoming increasingly difficult to use and productivity in my business has dropped considerably from my development staff to my sales people and admins (who use Office 2007 what a joke the new menus are [or lack there of]). SQL 2005 is a down grade from my (a customer) point of view. In SQL 2000, I could easily copy an entire database and its data over the wire to another server (remote), but with SQL 2005 and the annoying End-Point and overly stringent security settings, it is impossible! I have never been able to get it work. I am getting the feeling that MS has run out of creativity juices, and is rev'ing their product lines just to keep revenue coming in, regardless of customer opinion. Its like Microsoft does not understand the term "If it is NOT broke, then do NOT FIX it"

yup, leave it to the zenophobes at MS to take a useful product like DTS and screw it up royally. I'm just trying to copy a few databases from one server to another. what a pain. A few hours into this mess and I'm thinking I should just import ss 2000 to access and export to ss 2005. I can't figure out what they were thinking when they came up with ssis but it wasn't good.

SSIS is a big MESS!!AMEN

SSIS is definitely a step backwards. Most small companies don't have the luxury of a fully baked development/production separation and must operate many times on the fly in order to get things done and move forward. What a royal pain this "new" SSIS is compared with the DTS of old. As with most things Microsoft, if they had to actually use their own software like the rest of us do, it would work differently.

I have followed the File System method and then discovered that I could access the package in the MSDB folder from SQL Server Business Intelligence Development Studio. Both methods follow, first the File System then the easier method.Run SQL Server Management StudioOpen Object Explorer if it's not openSelect Connect, click on Integration Services from the listExpand the server labelled Integeration Services in Object ExplorerExpand Stored PackagesRight click on File SystemSelect Import package...Select your server, authentication and package path, which should be in the MSDB folderSelect the package that you saved earlier and want to edit, DO NOT change the protection level Now you can access the package from within a new Integration Services projectRun SQL Server Business Intelligence Development StudioSelect File, New, Project...Select Integration Services Project from the templates listed, give it an appropriate name Select Project Add Existing Item...Browse through to where you installed SQL eg. "C:\Program Files\Microsoft SQL Server\90\DTS\Packages"Select the package, something.dtsxIt will now appear under SSIS Packages in Visual Studios Solution ExplorerOR in your new project without importing the package from MSDB into File System in SQL Server Management Studio Select Project from the Visual Studio menuSelect Add Existing PackageSelect your Server, Authentication and the existing package from the list in your MSDB folder It now appears as before under SSIS Packages in Visual Studios Solution Explorer

Thanks for all the answers. I'm trying to do something relatively simple, but can't see any way to do it. I use SSMS to run the Import Export Wizard. In the wizard I specify the source file, type, destination sql server, and then I edit the mappings. Then I run the import. So far so good. Now I want to alter some of the mappings - I can't find any way to get back into the wizard with the package I just ran and edit it to just alter some of the mappings. I don't want to have to specify all the source and destination details again, I just want to be able to go to the mappings for iterative editing. Is there any way I can do this without having re-enter details that I have already entered before? If not why not? Regards, Dave

Wow, I'e just spent two days tring to figure out how to build an editable SSIS package using SQL Server 2008 and after reading the frustrations posted in this thread I give up. Previoulsy I have used DTS extensively to transfer data from a variety of sources to a variety of Targets as part of large scale data migration projects. I'm staggered by the crass incompetance and arrogance of Microsoft. Much as I'm loathed to say it my solutions henceforth will be Oracle based wherever possible http://download.oracle.com/docs/html/B10544_01/ch1.htm#1005778 Good luck guys

This can solve problem to some extent as long if you have had packages written in 2000 imported into new versions.. But like Mr.Jim if you have saved your import export data process as DTS inside SQL server. There seems to be no way of editing it. Unless someone has some other idea . The package doesn't show up under MSDB in Integration services in the object explorer.

Hi Gunku You can access the package for editing but not via SSMS object Explorer. You have to do so via VS BID (Biz Intelligence Dev). Just create a new SSIS project and from the solution explorer right-click and chose "Add an existing package". It will take you to a window where you select your SQL server and provide connection parameters to access the MSDB folder. I really suffered to get this figured out because Microsoft did not provide enough help and guide on this issue. If they are serious about wiping out DTS then SSIS should have been simpler. Give it a try and call back. Regards

WOW I was feeling really stupid, before I read this thread. It's nice to know that I'm not the only person who thinks SSIS made things hard. Tjc1961 took care of 90 percent of what I needed to know. I have the package out and edited, but is there an equally easy way to push it back into SSIS? The original packages were installed by an archival product, and I don't want to go through an intire install again. A quick comment about the posting from itpsl who thinks Oracle is better. He obviously hasn't dealt much with Oracle to make such a statement. At least Microsoft tracks issues, and can give you updates. Oracle released a significant defect with Oracle 10g, which I've reported multiple times, because they can never find anything about my previous calls and emails. The last three times I sent them the same email, with their response acknowledging that it is a defect and promising to update me on its resolution. It was interesting because as soon as I opened the issue with Oracle, I started getting calls from our Oracle rep, trying to get us, as a software vendor, to push the use of Oracle over MS SQL to our customer base. I've started emailing the defect to each new rep, when they contact me every six months. For those that want to know about the Oracle defect. In MS SQL you can create very complex stored procedures to do high volume db writes, but with Oracle you have to use "Packages" (two part procedures) if you want anything close to the same performance level. With the release or Oracle 10g there is an undocumented maximum length to the Package call. If you exceed the length by one character, the program making the call to Oracle will be shut down in windows. No error, no warning, it simply vanishes from the screen. I duplicated this with multiple products, including WinSQL. Oh, here's the best part. The call never makes it to the actual Oracle package. You can send the call to a total empty Oracle database and your program will still get closed.

I concur Andy 3991 !! I too was feeling stupid, after looking forward to finally getting a chance to delve into the magical powers of SSIS after using only bespoke flat file conversions and Lotus Enterprise Integrator on projects before, but being sadly disappointed at the 'make do and mend' way that the user experience has been put together!!! Other than going into SSMS, connecting under Integration Services and right-clicking on MSDB under Stored Packages to import the file from the Visual Studio folder, I can't see any easy way of 'pushing' back into the database.....? Yes, Oracle is no better. (I speak from the experience of discovering an error in their published white paper back in the Millenium bug days!!!!).

Other than going into SSMS, connecting under Integration Services and right-clicking on MSDB under Stored Packages to import the file from the Visual Studio folder, I can't see any easy way of 'pushing' back into the database.....? Here is an easy way to upload or to download multiple packages at once from/to the SSIS database. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

All these years later and I still must concur with everyone here. SSIS is messed up. Non-intuitive interface, excedingly difficult to maintain, no real gain in terms of production safety (this was apparently the intent). </gripe>

I agree with most here that SSIS is a big mess and there is no clear guidelines . DTS was lot better .I don't understand the logic behind the changes. I hope someone from MS will be able to understand from developer's point of view and will correct it in future releases.

Anyone who uses MicroSlop products ought to be aware of what they are dealing with. Their technical people are not competent, firstly. Secondly, the organization is arrogant beyond belief. This is the organization who are the fascists of the technical world. They know better than you do what you REALLY NEED and they are going to give it to you, like it or not. This SSIS problem is a perfect example of what I mean. Read Donald Farmer's posts again. Read what he is really saying to you -- they have decided not to allow you to edit the packages because they KNOW WHAT IS BEST FOR YOU. You, the stupid user, does not know what you need, right? Here is my example: Someone here at my company made a bunch of DTS (oh, yes, now they call it SSIS) in 2005 and guess what -- they left no documentation on what the packages do and no description anywhere. I am following behind trying to figure out what this person was doing. Do I REALLY have to run the package to see what is in it? So far, this has been the only way I have been able to see the steps this **** thing is taking. I tried exporting and loading into new projects, etc. I have tried every method POSSIBLE to see what this person did, but MicroSquish, in its infinite wisdom, has prevented me from doing so. Following Donald Farmer's convoluted instructions has not worked so far. I hesitate to run all of these packages which are deployed and therefore are INVISIBLE to me just to see what the **** they do! I never choose this company's products if I have a choice to select open source or even another private vendor. Seriously, if you care about your company don't use these products. End of story.

Anyone who uses MicroSlop products ought to be aware of what they are dealing with. Their technical people are not competent, firstly. Secondly, the organization is arrogant beyond belief. This is the organization who are the fascists of the technical world. They know better than you do what you REALLY NEED and they are going to give it to you, like it or not. This SSIS problem is a perfect example of what I mean. Read Donald Farmer's posts again. Read what he is really saying to you -- they have decided not to allow you to edit the packages because they KNOW WHAT IS BEST FOR YOU. You, the stupid user, does not know what you need, right? Here is my example: Someone here at my company made a bunch of DTS (oh, yes, now they call it SSIS) in 2005 and guess what -- they left no documentation on what the packages do and no description anywhere. I am following behind trying to figure out what this person was doing. Do I REALLY have to run the package to see what is in it? So far, this has been the only way I have been able to see the steps this **** thing is taking. I tried exporting and loading into new projects, etc. I have tried every method POSSIBLE to see what this person did, but MicroSquish, in its infinite wisdom, has prevented me from doing so. Following Donald Farmer's convoluted instructions has not worked so far. I hesitate to run all of these packages which are deployed and therefore are INVISIBLE to me just to see what the **** they do! I never choose this company's products if I have a choice to select open source or even another private vendor. Seriously, if you care about your company don't use these products. End of story.

Anyone who uses MicroSlop products ought to be aware of what they are dealing with. Their technical people are not competent, firstly. Secondly, the organization is arrogant beyond belief. This is the organization who are the fascists of the technical world. They know better than you do what you REALLY NEED and they are going to give it to you, like it or not. This SSIS problem is a perfect example of what I mean. Read Donald Farmer's posts again. Read what he is really saying to you -- they have decided not to allow you to edit the packages because they KNOW WHAT IS BEST FOR YOU. You, the stupid user, does not know what you need, right? Here is my example: Someone here at my company made a bunch of DTS (oh, yes, now they call it SSIS) in 2005 and guess what -- they left no documentation on what the packages do and no description anywhere. I am following behind trying to figure out what this person was doing. Do I REALLY have to run the package to see what is in it? So far, this has been the only way I have been able to see the steps this **** thing is taking. I tried exporting and loading into new projects, etc. I have tried every method POSSIBLE to see what this person did, but MicroSquish, in its infinite wisdom, has prevented me from doing so. Following Donald Farmer's convoluted instructions has not worked so far. I hesitate to run all of these packages which are deployed and therefore are INVISIBLE to me just to see what the **** they do! I never choose this company's products if I have a choice to select open source or even another private vendor. Seriously, if you care about your company don't use these products. End of story.