CODEWITHSUNDEEP
ssissql-server-2008-r2
Sparky
Sparky

Reputation: 763

After importing package remains on database even after deletion

I am a SQL Server newbie. I imported a package stored on my filesystem into my SQL Server 2008R2 database. Later on I deleted the package from the file system. However the package continues to remain in the SQL Server database and even executes!

What little I know implies this should not happen since even SQL Server will be referring to that package stored in the filesystem. So my question is: is this behaviour normal? If not what could be the possible reason for it

Cheerio

Upvotes: 0

Views: 616

Answers (2)

Sparky
Sparky

Reputation: 763

After much pondering and asking some serious sql-savvy people I feel this behaviour it pretty much natural. The two folders have been created just to distinguish amongst the sources from where the packages were obtained. Once imported the package is saved in the database. Then it's location does not matter much. In my example, a copy of the package is created and stored in the database. Thank you all for your help and time :)

Upvotes: 0

billinkc
billinkc

Reputation: 61231

When the package is on the file system, then it's sitting well where ever you want it. When the package is in the database, it will be stored in the msdb catalog in dbo.sysssispackage for 2008/2008R2 and 2012 if you are using the package deployment model. 2005 uses dbo.sysdtspackages90 and 2012 in project deployment model will use the SSISDB catalog.

So now you know that it's in a table and that is what the agent is able to reference. If you wish to get rid of it, call the appropriate stored procedure. In this case, it's msdb.dbo.sp_ssis_deletepackage It takes 2 parameters, the name of the package and the folder the package is located in. The root folder is 00000000-0000-0000-0000-000000000000. Otherwise, run the following query with the proper package name in there and it will generate the call you need to delete said package.

As always, evaluate the statement before blindly running it.

SELECT 
    P.name
,   P.folderid
,   'EXECUTE msdb.dbo.sp_ssis_deletepackage @name = ''' + P.name + ''', @folderid = ''' + CAST(P.folderid as varchar(50)) + '''' AS run_me
FROM 
    dbo.sysssispackages AS P
WHERE
    P.name = 'Package'

Upvotes: 1

Related Questions