July 2, 2012 at 2:41 pm
I recently had to examine an existing SSIS package and I noticed that in several job steps within the package, it contained T-SQL text code containing 4-part FROM clauses.
SELECT a.Acct_ID, g.Acct_Name
FROM [SQL01].[CRM].dbo.[Accounts] AS a
INNER JOIN [SQL01].[CRM].dbo.[gl_acct] AS g
ON a.Acct_ID = g.account_id
I was puzzled about this construct because we no longer had a server named SQL01. Our production CRM jazz all runs on a server named SQL05 now.
I eventually discovered that SQL01 was aliased in DNS to point at SQL05.
Now we are looking at installing a SQL 2012 server and someone has decided that we need to implement the new SQL Server as SQL01.
This means that I need way to identify all of the places where SQL01 is used within the SSIS packages and get it changed.
I've searched SQL Server Central and MSDN and I can't find any references to anyone needing to do something quite like this. We probably have around 50-60 packages, and most have multiple steps which could contain T-SQL code such the example above. I've discovered that if I open the SSIS package in BIDS, I can perform a search for text in all files within the project, but it seems like this is a common enough issue that there ought to be a better way to root out all of these server references than opening up each package individually and performing a search.
Does anyone know of a way to do this?
July 3, 2012 at 12:06 am
Packages are just XML files, so you just need to find a way to search in multiple XML files at once.
edit: something like this: Search text across multiple files with Windows Grep
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 3, 2012 at 12:05 pm
I hadn't realized that all of the components of these SSIS packages were XML files...I thought only the designer-related jazz was kept in XML. I guess I've got some more homework to do then.
Many thanks for your answer Koen. I'll start taking a look at grep-like utilities that might be able to parse out what I need from XML then. It didn't look to me like WinGrep would work on XML...bummer.
January 14, 2015 at 11:03 am
I know this is an old thread but I still like to share what I use for searching multiple .dtsx files at once.
I use a free text editor called Notepad++.
In notepad++, click Search->Find in Files...
type your string in "Find what:" box
type/select your .dtsx folder in "Directory:" box
Click "Find All"
Hope this will help someone in future.
Thanks
March 16, 2015 at 12:31 am
Thank you, the notepad trick helped 🙂
March 10, 2017 at 2:09 am
You can use the below code to search msdb for non SSIS catalog packages:
SELECT TOP 1000[name]
,[id]
,[description]
,[createdate]
,[folderid]
,[ownersid]
,[packagedata]
,[packageformat]
,[packagetype]
,[vermajor]
,[verminor]
,[verbuild]
,[vercomments]
,[verid]
,[isencrypted]
,[readrolesid]
,[writerolesid]
FROM [msdb].[dbo].[sysssispackages]
WHERE CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX), packagedata)) LIKE '%SearchString%'
March 10, 2017 at 2:22 am
Jim Mackenzie - Friday, March 10, 2017 2:09 AMYou can use the below code to search msdb for non SSIS catalog packages:
SELECT TOP 1000[name]
,[id]
,[description]
,[createdate]
,[folderid]
,[ownersid]
,[packagedata]
,[packageformat]
,[packagetype]
,[vermajor]
,[verminor]
,[verbuild]
,[vercomments]
,[verid]
,[isencrypted]
,[readrolesid]
,[writerolesid]
FROM [msdb].[dbo].[sysssispackages]
WHERE CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX), packagedata)) LIKE '%SearchString%'
*gasp* people still use MSDB to store SSIS packages?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 13, 2017 at 3:45 am
Koen Verbeeck - Friday, March 10, 2017 2:22 AMJim Mackenzie - Friday, March 10, 2017 2:09 AMYou can use the below code to search msdb for non SSIS catalog packages:
SELECT TOP 1000[name]
,[id]
,[description]
,[createdate]
,[folderid]
,[ownersid]
,[packagedata]
,[packageformat]
,[packagetype]
,[vermajor]
,[verminor]
,[verbuild]
,[vercomments]
,[verid]
,[isencrypted]
,[readrolesid]
,[writerolesid]
FROM [msdb].[dbo].[sysssispackages]
WHERE CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX), packagedata)) LIKE '%SearchString%'*gasp* people still use MSDB to store SSIS packages?
I know right. The vast majority of our stuff is all in the SSIS Catalog, but we do have some older packages that still live in MSDB...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply