May 5, 2011 at 1:04 pm
I'm building a collection of stored procs across about 20 dbs on our server that I want to deprecate, the initial pass being based on the 'proc names that don't appear in our front-end code'.
I'm trying to whittle down this list so as to avoid deprecating procs that are still in use somewhere, and the next place I want to check for dependencies is in our large collection of Legacy DTS jobs, many of which I know call various stored procs.
Is there any way to compare my compiled list of SP names (I've collected their names in a table) against these DTS packages to see if any of these procs are called therein?
Or am I stuck doing it manually ... there's like 200 jobs and 1600 procs involved here, so ... obviously an automated method would be highly preferable 😀
TIA,
Brett
May 5, 2011 at 2:01 pm
The .dtsx files are XML (aka formatted text), so you could do a bulk import into a varchar(max) column, and then do a like comparing the proc names to that column.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 5, 2011 at 2:34 pm
Thanks Wayne.
That sounds like a great solution.
Is there any way I could trouble you to expound a bit upon this methodology? I've used Bulk Import in procedures that import single .csv files that I've placed on the server before, but I don't know how I'd go about doing what you suggest.
Specifically, my questions would be:
1) The actual DB is at a hosting facility, and I know they always install SQL Server in a very generic way in terms of the file path, so what would be the path to where these files would probably exist, given a default install of SQL Server 2005?
2) How do I 'walk through' this set of DTS XML files in the directory, once I've found where they live?
3) How is the bulk import command formatted when doing an import from an XML file in this way?
Once the XML text is loaded into a table, I'm all good ... it's just getting it there that's the trick. I'm much more of a developer than an administrator, in case you couldn't tell :rolleyes:
BTW, I need this process to work entirely via script.
Thanks Again,
Brett
May 6, 2011 at 8:14 pm
Bump for more help from Wayne 😀
May 6, 2011 at 8:45 pm
bvaljalo-1000038 (5/5/2011)
Thanks Wayne.That sounds like a great solution.
Is there any way I could trouble you to expound a bit upon this methodology? I've used Bulk Import in procedures that import single .csv files that I've placed on the server before, but I don't know how I'd go about doing what you suggest.
Specifically, my questions would be:
1) The actual DB is at a hosting facility, and I know they always install SQL Server in a very generic way in terms of the file path, so what would be the path to where these files would probably exist, given a default install of SQL Server 2005?
2) How do I 'walk through' this set of DTS XML files in the directory, once I've found where they live?
3) How is the bulk import command formatted when doing an import from an XML file in this way?
Once the XML text is loaded into a table, I'm all good ... it's just getting it there that's the trick. I'm much more of a developer than an administrator, in case you couldn't tell :rolleyes:
BTW, I need this process to work entirely via script.
Thanks Again,
Brett
1. If the SSIS packages are saved in MSDB, you might need to connect to it with BIDS and load them in to BIDS first, then save them to a file.
2. Use xp_dirtree with the first parameter being the path to check, and the second and third parameters being 1. You will want to load this into either a table variable or temp table via INSERT INTO EXEC. Search of the "file" column = 1, and for the subdirectory column like '%.dtsx'. In a loop, load each of these files into a table as shown in step 3.
3. Check out this article: Loading XML Data into SQL Server (SQL Spackle)[/url].
I'd open up one of those files in notepad++, and determine in which element(s) the stored procedure names are at, then figure out the xpath query to retrieve that information.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 6, 2011 at 9:01 pm
This will load the information into a temp table on your system, and show you which procedures are in which SSIS packages.
-- declare and initialize variables for this script.
DECLARE @path VARCHAR(200),
@name VARCHAR(200),
@SqlCMD NVARCHAR(500);
SET @path = ''; -- set to the path where your .dtsx files are at
IF RIGHT(@Path,1) <> '\'
SET @path = @path + '\';
-- declare a table variable to hold all of the files in the specified path
DECLARE @test-2 TABLE (
FileName VARCHAR(200),
Depth TINYINT,
IsFile BIT);
-- get all of the files in the specified path
INSERT INTO @test-2
EXECUTE xp_dirtree @Path,1,1;
-- create a local temp table to hold the file information.
-- it will be in scope when the dynamic sql is run
IF OBJECT_ID('tempdb..#SSISFiles','U') IS NOT NULL DROP TABLE #SSISFiles;
CREATE TABLE #SSISFiles (
RowID INT IDENTITY PRIMARY KEY CLUSTERED,
FileName varchar(200),
PathName VARCHAR(200),
FileData XML);
-- build a c.u.r.s.o.r to load each file in to the table one by one.
DECLARE cSSISFiles CURSOR FOR
SELECT FileName FROM @test-2 WHERE FileName LIKE '%.dtsx';
OPEN cSSISFiles;
FETCH NEXT FROM cSSISFiles INTO @name;
-- for each row in the table variable, load that file into the temp table
WHILE @@fetch_status = 0 BEGIN
SET @SqlCmd = N'INSERT INTO #SSISFiles SELECT @Name, @Path, * FROM OPENROWSET(BULK N''' + @path + @name + ''', SINGLE_BLOB) rs;'
EXECUTE sp_executesql @SqlCmd, N'@Path varchar(200), @Name varchar(200)', @path, @name;
FETCH NEXT FROM cSSISFiles INTO @name;
END;
CLOSE cSSISFiles;
DEALLOCATE cSSISFiles;
-- shows all files that have the procedure name anywhere in it.
SELECT t1.*, ProcName = sp.name
FROM #SSISFiles t1
CROSS APPLY (SELECT name FROM sys.procedures) sp
WHERE CONVERT(VARCHAR(MAX), t1.FileData) LIKE '%' + sp.name + '%';
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 9, 2011 at 2:17 pm
Awesome Wayne, thanks for the code :w00t:
I'll definitely be giving this a try today and report back.
Thanks Again,
Brett
EDIT: Okay, it looks like I have a problem ... perhaps a serious problem. These files do not appear to be .dtsx files, they are all .dts files, which, based on the contents when opened in notepad, appear to be in an encoded format of some type.
Is there a way to get these converted into a format that can be searched through in this same way? I'm guessing the package migration wizard is the path to accomplishing this?
May 9, 2011 at 3:24 pm
If you search this site for 'Search DTS for string' you'll see several leads.
Is this a a 2000 server or a 2005 server with legacy DTS packages?
May 9, 2011 at 5:30 pm
Thanks Chrissy ... I think Wayne's script does exactly what I need to do here, I just need to get the packages in a state such that they're 'available' to be imported via his code.
BTW, this is 2005 install w/legacy DTS packages.
So ... I used the migration wizard, which completed for all the DTS packages w/just a few errors I skipped (not too concerned as I don't need the SSIS version of these packages to actually 'work'), but when I search the server for .dtsx files ... there still aren't any. I presumed that migrating them would create the needed .dtsx files on the server, but it seems not.
Any idea what I need to do next so that I can use Wayne's code?
Do I need to use BIDS next, as suggested by Wayne? I've never used this tool and don't think it's installed. I presume it's on my MSDN-provided SQL Server 2k5 install disc? Will I need a license just to do this one thing using this tool? If I do need a license, I definitely need to make sure it's what I actually need in order to complete this project.
Thanks again everyone,
Brett
May 10, 2011 at 9:25 am
This seems to indicate that when using the migration wizard you can go directly to the msdb database or dtsx file.
http://msdn.microsoft.com/en-us/library/ms143496.aspx
Since you can't see the files I assume you went to MSDB. Can you rerun and go to the file destination?
May 10, 2011 at 1:36 pm
A-Ha!
Yes, I did import them into the database. I see how to change the destination in the Wizard, so I'm going to try that now.
Hopefully that's the missing piece of the equation.
Thanks again Wayne and Chrissy! SSC is an awesome resource.
May 11, 2011 at 8:00 pm
Okay, got it all imported, and the script you provided worked a treat Wayne. Chrissy, exact right call, I just had to import to .dtsx instead of the db itself. You folks are awesome, thanks again!!!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply