Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need list of COlumns/Table Names used in SSIS/SSRS Expand / Collapse
Author
Message
Posted Monday, September 17, 2012 3:12 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 5:28 PM
Points: 56, Visits: 286
Hi All,

Recently we are planning to do some schema level changes to few tables, so now i have to find out all the depended scripts/SSRS reports/SSIS packages which are using those columns.
we have around 60 reports and around 20+ T-SQL scripts.

As its regular task as part of dev cycle, my manager asked me to create report with all Object References to packages/SSRS

To be more clear the Object references report will have below fields

BI Type Name TableNames ColumnNames
SSRS Report1 SimpleTable1 Col1,Col2,COl3
SSRS Report1 SimpleTable2 COl4,Col5,COl7
SSIS Package1 Table5 Col6,Col1

Please let me know if there is any automated way to find reference fileds.

I know there is way to find out related tables in a Stored procedure but is there any way i can find column names also ?

Regards,
Krish
Post #1360472
Posted Tuesday, September 18, 2012 3:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 953, Visits: 2,626
One tool you might want to look at is Pragmatic works BI Documenter http://pragmaticworks.com/Products/BI-Documenter.aspx

It isnt too bad, and you can download a free 14 day evaluation.

We had mixed results in our testing but that was with a version from a couple of years ago, and it looks to have matured a bit, in the end it was a helpful starting point especially when you have a lot of reports and SSIS packages to trawl through.

It also creates either HTML or CHM files for you to browse through.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1360641
Posted Tuesday, September 18, 2012 11:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 5:28 PM
Points: 56, Visits: 286
Hi Jason,

Thanks for your response and i tried that but the problem its not giving column level details.

If we want till table level i think its best tool..

Regards,
Krishna
Post #1360918
Posted Wednesday, September 19, 2012 2:07 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 953, Visits: 2,626
I take it that your reports and SSIS use stored procs to encapsulate the logic?

Do you use Visual Studio (VS2008 GDR/VS2010 Premium) database projects for storing your Database code? Or a different tool?



_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1361182
Posted Wednesday, September 19, 2012 10:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 5:28 PM
Points: 56, Visits: 286
Hi Jason...

Yaa we use Visual Studio 2008....

Is there anyway we can do it from Visual Studio ?
Post #1361475
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse