Here is what happened:
1. SQL Server 2005 Management Studio
2. Tasks > Export data
3. Wizard gives you the option to Execute Immediately and/or Save as an SSIS package
4. Selected option to Exec Imm and Save SSIS package to File System (pkg protection level = default = encrypt sensitive data with user key)
5. SSIS pkg named, described and saved to a local path (filename.dtsx)
6. SQL Server 2005 Business Intelligence Development Studio > File\Open the previously saved dtsx file
7. Modified the SSIS package in the BIDS designer gui.
8. Used the File > "Save" option to save the modified SSIS package in SQL Server under the "Maintenance Plans" list tree.
9. Back in SQL Server Mgt Studio, created a Job with Step 1 that executes an SSIS pkg.
10. The job step's General Properties specifies Type = SQL Server Integration Services Package, Run As: SQL Agent Service Account, Package Source = SQL Server, Server = [the SQL 2005 instance where the pkg was Saved to], Windows Authentication selected, and the Package location was selected by hitting the ellipsis button ... this let me browse the SQL Server instance for the saved package, which I found under the "Maintenance Plans" node.
11. The original DTSX file object is now gone - someone deleted the file and its containing folder and it cannot be recovered.
12. The modified SSIS package is being executed as Step 1 of the SQL Job, but now a new requirement means the SSIS package needs to be modified.
13. The SSIS package apparently is "stored" in the SQL Server instance under something called "Mainteance Plans", but I need to find it so that I can open it in the BIDS designer to make the necessary design changes.
14. I am using the same Windows account and same SQL account (as applicable) as were used when the original DTSX file object was opened and modified in BIDS, and then saved as an SSIS package in SQL Server, under the "Maintenance Plans" tree node.
15. I now cannot find a way to open / retrieve that SSIS package. Where is it? How do I open it so I can modify and/or save it as a DTSX object again? Screen shot of the SQL job step is attached. SQL-2005-job-with-SSIS-pkg-in-St.jpg
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
-Mike Kapnisakis, Warner Bros
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.