How can I find out where on the filesystem an SSIS package exists

Question

How can I find out where on the filesystem an SSIS package exists

cunningham

SSCarpal Tunnel

Points: 4595
More actions
July 19, 2012 at 6:00 am

#278050

I have a couple of SSIS jobs that are of type Operating system(cmdexec).
I did not create these packages but need to know where they live so I can be sure they will still work after a cluster failover. Obviously if they are on the C drive of one node but not the other, then I will have problems.
Looking at the job step proerties in SSMS They execute the package via the dtexec program using the below command
"D:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEXEC" /DTS "\File System\<directory>\<package>" /SERVER "<servername>" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /Decrypt <password>
As you can see the location of the package starts \file system\ but how do I find out on which disk and in what location this package is being called from? Would this depend on where the package was created?
Any help appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ant-Green SSC Guru Points: 113498 More actions · Answer 1

In the BINN folder is MsDTSSrv.ini.xml, open that in notepad and where it sales file system it should have storepath as somethng like ..\packages, that will tell you where the packages are stored.

If it is ..\packages then its in the packages folder at the same level of the BINN directory.

cunningham SSCarpal Tunnel Points: 4595 More actions · Answer 2

Thanks Anthony that is spot on. As I feared the jobs exist on a disk that is not shared between the nodes in the cluster, as a result SSIS won't work in the event of a failover.

Thanks again

Ant-Green SSC Guru Points: 113498 More actions · Answer 3

SSIS is not cluster aware.

Always save to MSDB and never to the file system, that way the packages move with the SQL service and dont stay on disk.

cunningham SSCarpal Tunnel Points: 4595 More actions · Answer 4

The developers have started saving packages to MSDB but there are some older packages they had saved to this disk location.

I have copied the packages to the same location on the passive node and ensured the ini file is exactly the same.

Integration services is installed on both nodes and the directories and files within D:\Program Files\Microsoft SQL Server\90\DTS\Binn look good and DTEXEC.exe is present so it SHOULD work when we failover. I hope anyway!!

Ant-Green SSC Guru Points: 113498 More actions · Answer 5

I would extract the dtsx files from the filesystem and load them into MSDB and change the job to point to the database instead of the fileserver. But thats just my personal preference, and also as we dont do Windows level backups on our SQL servers as its quicker in most cases to just rebuild and reinstall the OS & SQL from the templates also means we have a backup of the packages in MSDB without having to go into TFS to extract and redeploy.