September 12, 2011 at 4:11 am
Hi All,
Greetings!
Plz let me know this.
I have deployed all SSIS packages into SQL Server.
Now I want to take out all SSIS packages with the same Query Text, used in Execute SQL Task.
To brief Out:
Consider the Query Text: Select Name from sys.databases (in Execute SQL Task). I want to list out all ssis package names that uses this query in the execte sql task.
Can i take these details from msdb database
Is this possible. Please help me out.
Thanks in advance.
Regards
Priya
Regards
Priya
September 13, 2011 at 4:23 am
Hi Priya,
This is not possible from MSDB without an extremeply long winded method. You can convert the packagedata column from sysdtspackages90 back into it's xml and then read that xml and look for that sql string.
I think first of all you should be asking why you want to do this. It's really not someting which is undertaken very often and the above method is probably not documented anywhere.
May be an easier method would be to configure your package logging to capture this and this would lead you back to the packageid.
Thanks
Graeme
September 13, 2011 at 5:51 am
My prod envt has almost close to 300 packages, designed long time back.
I now have a situation to chnage the SQL statement used in the EXECUTE SQL TASK (in many packages) and want to figure out which packages holds this statement.
Instead of opening each and every package manually, I wanted to check if the same can be taken from the msdb database, as all the packages are deployed to SQL Server.
Thanks for ur response Graeme.
Regards
Priya
September 14, 2011 at 7:41 am
You have two alternative, albeit somewhat ugly, options.
If the EXECUTE SQL TASK is performing a SELECT statement against a table, then perhaps what you could do is re-name that table to a different name, then create a view for that table, and have the changed SELECT statement be reflected in the creation of the view.
For example, if you had a table which contained an ID column and a Value column, and the EXECUTE SQL TASK was performing the query "SELECT ID FROM TABLE WHERE VALUE > 5", and you wanted to change that to "SELECT ID FROM TABLE WHERE VALUE > 10", you could instead rename the table so the data is all still there, create a view with the old name of the table, and then populate the view with the query "SELECT ID, VALUE FROM TABLE_BKP WHERE VALUE > 10". This way your EXECUTE SQL TASK will work correctly.
If on the other hand the EXECUTE SQL TASK is performing an UPDATE or a DELETE, then you could instead put a trigger on the table, either as an AFTER UPDATE, DELETE or as an INSTEAD OF UPDATE, DELETE, depending on your requirements.
September 15, 2011 at 6:12 am
Thanks for ur response. Will try the code and update.
But let me know if there is any possibility to identify the packages those uses the same query in the execute SQL task.
Regards
Priya
September 15, 2011 at 7:18 am
You could probably identify which packages are doing the work by using some form of audit mechanism. Like, for example, if it's an UPDATE on a table, you could put an UPDATE trigger and store the user name and system name of the object doing the update, that might tell you what is going on. You could also run a trace, which might give you some details.
September 15, 2011 at 7:50 am
kramaswamy (9/15/2011)
You could probably identify which packages are doing the work by using some form of audit mechanism. Like, for example, if it's an UPDATE on a table, you could put an UPDATE trigger and store the user name and system name of the object doing the update, that might tell you what is going on. You could also run a trace, which might give you some details.
I think that you're over-engineering this. You can do a query to get these results. The packages are stored in msdb.dbo.sysdtspackages90. You can query that table to find the packages that use that statement. I don't think the query is even that complicated, although you may need to account for slight differences in formatting, e.g., did they sometimes use the schema and sometimes not, etc.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 15, 2011 at 7:57 am
Didn't know that - personally never used the SSIS store for packages, always deployed mine by using DTEXEC. Good to know. Can you update them the same way?
September 15, 2011 at 8:43 am
kramaswamy (9/15/2011)
Didn't know that - personally never used the SSIS store for packages, always deployed mine by using DTEXEC. Good to know. Can you update them the same way?
I wouldn't. The GUI handles a bunch of bookkeeping functions that are much harder to get correct when you manually edit the package. It's really easy to corrupt your packages by manually updating them.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 16, 2011 at 3:02 am
Drew,
As my above post states the sysdtspackage90 (sql 2005 as its sysssispackages in sql server 2008) stores the package as an image. SSIS packages are all essentially xml so you would have to query the xml.
I wouldn't say it was quite that easy if you weren't used to working with xml.
G
September 16, 2011 at 3:20 am
Try this code so you can do a string search with the package xml:
SELECT [name] AS SSISPackage
, CONVERT(XML, CONVERT(VARBINARY(MAX), packagedata)) AS PackageXML
FROM msdb.dbo.sysdtspackages90
WHERE CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), packagedata)) LIKE '%YourTableName%'
This will at least give you the packages which are updating a certain table.
If you are planning to update multiple packages via a sql script then I wouldn't recommend it unless you do some thorough testing (and even then I wouldn't recommend it).
Hope this helps you.
G
September 18, 2011 at 11:04 pm
Thats awesome. Worked great for me....
Thanks again.
Regards
Priya
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply