Is it possible to recreate a package based on the one deployed to SQL Server?

Question

Is it possible to recreate a package based on the one deployed to SQL Server?

mister boom

SSChasing Mays

Points: 603
More actions
March 9, 2012 at 4:30 am

#146700

I created an SSIS package about 2 years ago. We deployed it to SQL Server, sceheduled it, and it's been running every day since then.
We now need to make a change to the package, but someone has deleted the source package and solution :crazy:
Is there any way that we can retrieve the definition of the package from the one that is deployed to SQL Server, and use that to recreate the package so that we can amend it?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 1

Sure.

Log into the SSIS server, export the package to the filesystem (right click on the package), create a new solution in BIDS and add the package.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

mister boom SSChasing Mays Points: 603 More actions · Answer 2

Hi

Thanks for the reply.

I have connected to Integration Services through SSMS. Although there is a folder listed under Stored Packages - File System that the package should be contained within, the folder is empty. Other folders contain packages, and I am able to use the Export Package functionality as you suggest.

It seems odd that the folder is empty when SQL Server Agent is able to run the job, but I have just spotted something which may be relevant:

The server was originally set up as SQL 2000. We then installed SQL 2005 on top, and with an instance name. If I try to connect to Integration Services through SSMS, it doesn't allow me to specify an instance name; just server name. When I do so, this returns some of our 2005 packages. However, if I look at the SQL Server Agent job for this package, I can see that it is set to package source "SQL Server", and the Server dropdown shows the server name followed by the instance name.

It looks like the package has been saved to the instance, but I'm not able to access the instance, just the server. Does this make sense?

Thanks

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 3

Look in the MSDB node instead of the File System one.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

mister boom SSChasing Mays Points: 603 More actions · Answer 4

Hey Koen, thanks for all your help.

When I clicked MSDB, I got a message:"Failed to retrieve data for this request....The SQL server specified in SSIS service...."

I managed to find that this was because the MsDtsSrvr.ini.xml file had the server set to ".". When I changed this to the server\instance name, the MSDB folder became available, the package was listed, and I was able to export it.

Thanks again

Alun

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 5

mister boom (3/9/2012)
Hey Koen, thanks for all your help.
When I clicked MSDB, I got a message:"Failed to retrieve data for this request....The SQL server specified in SSIS service...."
I managed to find that this was because the MsDtsSrvr.ini.xml file had the server set to ".". When I changed this to the server\instance name, the MSDB folder became available, the package was listed, and I was able to export it.
Thanks again
Alun

Great! Glad the issue was resolved.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

As an FYI - you can also do this from a client machine with visual studio. From within an Integration Services project you can "Add Existing Package" which will allow you to bring it down from the server directly to the local machine.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 7

Matt Miller (#4) (3/9/2012)
As an FYI - you can also do this from a client machine with visual studio. From within an Integration Services project you can "Add Existing Package" which will allow you to bring it down from the server directly to the local machine.

You're right, I totally forgot about this possibility. Good to know.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 8

DTUtil.exe is what I use for exporting packages from servers. It's included in the client tools, available from the command-line.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato