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 12»»

SSIS Mapping documentation Expand / Collapse
Author
Message
Posted Tuesday, October 19, 2010 12:05 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 26, 2011 3:46 PM
Points: 78, Visits: 33
I am working on a large data migration using SSIS. In past projects we have used SQL stored procedures and manually generated mapping spreadsheets to provide to the client. We have now been tasked with providing this type of documentation on the current project. I would have though that generating data flow mapping documentation would be something that SSIS would do "out of the box" similar to what I have seen from Informatica. I have yet to find any solutions to this issue, and wonder if anyone on this forum has run into a similar situation and how went about automating the documentation process?

thanks in advance.
Post #1007207
Posted Tuesday, October 19, 2010 12:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 13,522, Visits: 11,312
As you said, SSIS can't provide the documentation you need out of the box.
You can check out BI documentor, but I haven't found real evidence on their site that it also can do mappings.

You can generate mappings with the ILM models in Powerdesigner, but it isn't the easiest tool to work with.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1007223
Posted Wednesday, October 20, 2010 7:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 5,615, Visits: 6,413
I'm a little confused. What exactly are you trying to map?

Are you just trying to show the "guts" of a SSIS package? Or is this something else?


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1007708
Posted Wednesday, October 20, 2010 7:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 13,522, Visits: 11,312
Normally the mapping of an ETL flow is some sort of matrix where you "map" the input columns with the output columns.
E.g.: Input_Street + Input_StreetNumber = Output_Address




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1007713
Posted Wednesday, October 20, 2010 7:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 5,615, Visits: 6,413
I'd still like the OP to answer. Assumptions about this sort of thing are dangerous.

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1007716
Posted Wednesday, October 20, 2010 7:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 13,522, Visits: 11,312
Brandie Tarvin (10/20/2010)
I'd still like the OP to answer. Assumptions about this sort of thing are dangerous.


Well, that's why I added "normally".




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1007719
Posted Wednesday, October 20, 2010 7:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 26, 2011 3:46 PM
Points: 78, Visits: 33
Thanks for the replies. The mapping I refer to is like this;

source.last_name + source.first_name --> destination file_as_name

this is a simple example of what we are trying to achieve. Problem is we have tons of these and some of the expression logic is fairly complex.

Really just seeing if some sort of tool exists that will document this data transformation logic showing all data flow destination columns and how the source data was transformed for the insert.

Thanks again for the responses.
Post #1007727
Posted Wednesday, October 20, 2010 7:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 13,522, Visits: 11,312
Well, it can be done in Powerdesigner, but not that easily.
(and Powerdesigner isn't actually a documenting tool).

I'm going to work with Powerdesigner in the following days, I'll let you know how it works out.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1007740
Posted Wednesday, October 20, 2010 7:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 26, 2011 3:46 PM
Points: 78, Visits: 33
I read up on this product after you post from yesterday and was unclear on the role it would play. Does it have the ability to read existing packages or is it a replacement for SSIS?
Post #1007742
Posted Wednesday, October 20, 2010 7:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 13,522, Visits: 11,312
No. Powerdesigner is more a data modeling tool for databases. You can document ETL (and thus generate mapping information), but it can certainly not read SSIS packages or replace them. You have to input everything yourself (unfortunately).

I think it is more worth to check out BI Documentor.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1007745
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse