Post reply

Cannot find SSIS package design files to determine datasources, steps, etc...

  • October 8, 2016 at 8:13 am

    #313782

    I'm reviewing a SQL Server 2008 environment someone (who is not available) designed back in 2006-2008 that has a bunch of SSIS packages on them. Meaning if you connect to SSIS using SSMS, under the "Stored Packages > MSDB" folders you can see about 30 different packages listed there. I want to be able to see what the packages are actually doing, however, I'm unable to find the design files for them.

    I assumed there were developed using Visual Studio/BIDS so I've done a number of file searches for them using different variations (e.g. '*.dtsx" or "*.sln" or the packages name, etc..), however, I've have not found anything.

    Is there any way I can find this information or recover the files or packages or projects in Visual Studio?

    Is it possible they didn't even design these packages in Visual Studio and used some other tool/application?

    I believe these 30 packages are being called by various job schedules that are active in SQL Server Agent. I see a lot of 'DTSrun.exe' references with encrypted text in various job steps. Since I can't read the text, I don't know which package it's calling, but I assume it's calling them.

  • October 8, 2016 at 8:57 am

    #1905522

    all those packages are actually stored in an[image] data type in the [msdb].[dbo].[sysssispackages] table.

    you could preview them or manually convert them to xml stringsand save them to disk.

    select convert(nvarchar(max),convert(varbinary(max),packagedata)) from [msdb].[dbo].[sysssispackages]

    you can use the SSIS import wizard to grab them and open them one at a time, but I've used the code here to grab everything and send them to disk:

    http://www.ssistalk.com/2011/03/14/ssis-export-all-ssis-packages-from-msdb/

    then you can create a project, and add them so you can see what they were actually doing.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 8, 2016 at 9:45 am

    #1905523

    Interesting. Thank you! I'll try and report back

Viewing 3 posts - 1 through 2 (of 2 total)

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