October 19, 2010 at 12:05 pm
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.
October 19, 2010 at 12:20 pm
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 20, 2010 at 7:18 am
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?
October 20, 2010 at 7:23 am
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
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 20, 2010 at 7:25 am
I'd still like the OP to answer. Assumptions about this sort of thing are dangerous.
October 20, 2010 at 7:27 am
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".
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 20, 2010 at 7:34 am
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.
October 20, 2010 at 7:53 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 20, 2010 at 7:55 am
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?
October 20, 2010 at 7:58 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 21, 2010 at 6:13 am
I think Computer Associates might have a tool too, but I don't know the name of it off the top of my head. Might look at Pragmatic Works. I don't know if they just do solutions or if they've developed documenting tools.
May 2, 2011 at 12:01 pm
Have you looked at BIDocumenter Tool to document ssis packages it documents SQL Server, SSAS, SSRS and SSIS.
BIDocumenter documents SSIS Mappings very nicely so you know from where your data is coming.
May 3, 2011 at 2:41 am
We've jsut bought BI Documentor and personally I'm very happy with it. It gives you details of the SSIS package from where the field comes from how its transposed and where it lands up again. For me personally its almost to much information in this tool, but rather too much than too little. Have a look at this tool, you can download the eval version, I think it would be worthwhile to check out.
May 3, 2011 at 4:17 am
Chris,
Just an FYI: SSIS Guy is posting spam to every old BI thread on this forum. I've reported him as such since he seems to not be paying attention to what's on the thread at all (or how old the thread is).
May 3, 2011 at 4:34 am
Hi Brandie, ok, just saw that now, guess he must be earning commision on the sales? 🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply