Get SSIS package that loaded data into a Database Table

Question

I am in a situation where i am inheriting an SQL Server 2008 R2 database with out any form of documentation available. The database "Probably" gets loaded with data from an SSIS package & i am trying to hunt down this package.

Is there a T-SQL command i can run against the database which would then show me the last time the database was loaded with data and where that data was from?

Answer 1

Congratulations. Well, not really but welcome to your new opportunity to learn plenty of new things. I remember a similar question on ServerFault - 10 servers to administer and I'm a history major

1. sp_blitz if you know nothing about taking care of a SQL Server, start here. They provide a handy script you can run on the server as well as training and advice on the results of that script. They'll also be happy to provide some great consulting services. The StackExchange network uses Brent Ozar Unlimited so keep that in mind. Anyways, that stored proc is going to tell you most everything you need to know after having inherited a server (when were backups run last, are there any triggers out there, how much dumb is packed into this thing)

2. Open up SQL Server Management Studio, connect to the instance and look at the SQL Agent. Double each of the jobs you see there and look through all the job steps. This is going to provide you an opportunity to see what is scheduled to run at what intervals. You will also need to look at Windows Scheduler (Control Panel\All Control Panel Items\Administrative Tools\Task Scheduler) %windir%\system32\taskschd.msc /s For SQL Agent, you will want to keep any eye out for job subsystem of type "SQL Server Integration Services". You should also pay attention to any OS commands because you might also see a reference to dtexec.exe That is the executable that makes an SSIS package "go". If task scheduler, then you'll be looking for the dtexec call (and hoping it's not wrapped in a nebulous .bat file like ImportData.bat). Also inquire whether your organization uses a different scheduling tool. A few jobs ago, we used Tidal to great effect. At any rate, this is where you can determine what is performed periodically.

3. Find the packages. Packages are going to be in the database or on the file system. Database is easier, as they will be stored in msdb.dbo.sysssispackages for 2008, 2008 R2 and 2012 Package Deployment Model projects. For 2005, look in msdb.dbo.sysdtspackages90. I have a handy query to pull this information out on SSIS Package Query. If it's on the file system, then you can pray it's on the local box and a search for all files ending in .dtsx will help you. However, since you can reference packages on a network file share, that can get ugly fast. Once you find them, you will need Business Intelligence Designer Studio, BIDS, which is a custom shell for Visual Studio. Since this is SQL Server 2008 (and R2), you will have to get the Visual Studio 2008 shell running. Until the 2012 release of SQL Server, the team was tied to the corresponding VS install. Find the SQL Server Installation media and perform the install from there.

To specifically address "Is there a T-SQL command i can run against the database which would then show me the last time the database was loaded with data and where that data was from?" No. It's really about that simple. If you had good designers, your tables probably have insert and modification dates that reflect when operations occurred.

What else can be getting data into your system? Everything. There could be .NET services, either web or windows pushing data in there. THere could be triggering event based on WMI, like a file watcher waiting for the newest data file to be dropped. Etc etc.