April 4, 2008 at 9:16 am
How would I find out of 400 plus DTS packages which one references a particular table?
Thank you.
April 4, 2008 at 1:09 pm
If you are talking about the scheduled packages, probably you would like to create a server trace with:
Events:
T-SQL Batch Starting
Columns:
like textdata, start time and end time
Filter:
textdata LIKE %YourTableName%
and check the trace at what time the batch was executed with your table name and then check what job was executed at this time.
Regards,Yelena Varsha
April 6, 2008 at 10:53 pm
David Bellach (4/4/2008)
How would I find out of 400 plus DTS packages which one references a particular table?Thank you.
[font="Tahoma"]You can use the below query to check.. enter the name of the dts in between the two percentages.
select * from msdb.dbo.sysdtspackages where name like '%%'[/font]
Regards..Vidhya Sagar
SQL-Articles
April 7, 2008 at 8:48 am
Vidhya,
David needs the table names, not the package name.
Regards,Yelena Varsha
April 8, 2008 at 4:32 am
Table names is correct, though this stuff is helpful in other functions I need to do.
April 8, 2008 at 6:58 am
You could save your DTS packages as Visual basic files (.bas) then search for the table name within the files.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 8, 2008 at 8:15 am
Yes, that is a good solution - save as VB files and search them.
Does somebody know how to export text for 400 packages?
Regards,Yelena Varsha
April 8, 2008 at 5:53 pm
To save as Visual Basic? I thought the DTS pacakges were stored possibly binary?
April 9, 2008 at 2:57 am
There are a number of scripts on the web that can do a bulk export of DTS packages. Most of them (including those in SqlServerFineBuild) are aimed at transferring the packages to a diferent server so you may have to tweak the output definition to get the VB output, but this is easy to do.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 9, 2008 at 8:35 am
Hi David,
You can save DTS package as VB file - when doing Save as.
You can open this file with Notepad. Also if you copy all of these files to one file with the help of Copy command, rename it to txt and you will have one nice big file to search.
Hi Ed,
Thanks for the response, I will try to find one. Is SQL Server FineBuild free or we have to buy it?
Regards,Yelena Varsha
April 9, 2008 at 9:58 am
SqlServerFineBuild is free, but you can make a contribution (it goes direct to charity) if you wish.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 9, 2008 at 11:24 am
Thanks Ed!
Regards,Yelena Varsha
April 10, 2008 at 8:56 am
Nigel Rivett's script has been a big help for us. We have been using it for a couple of years and it is very, very good.
http://www.nigelrivett.net/ScriptDTSProperties.html
I also found something this morning at SQL Server Central, and it refers to SQLSpec 3.8. Unfortunately I just downloaded it but have not had time to check it out
April 12, 2008 at 7:40 am
Thanks!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply