Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need list of COlumns/Table Names used in SSIS/SSRS


Need list of COlumns/Table Names used in SSIS/SSRS

Author
Message
krish.sett
krish.sett
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 319
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
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3229
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
krish.sett
krish.sett
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 319
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
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3229
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
krish.sett
krish.sett
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 319
Hi Jason...

Yaa we use Visual Studio 2008....

Is there anyway we can do it from Visual Studio ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search