External documentation (SQL Documenter)

I know that the BI studio can build a nice flow document that is associated with the package. Is there an easy way to build a series fo documents that can be shared with other users without having them go into Visual Studio?

Mark E. JohnsonI have an upcoming presentation and was wondering if there were any methods/features that could display all the SSIS packages, tasks, dataflows, relationships, and such in a functional diagram? Similar to what is seen in a ERD diagram. If not, are there any functions or tools that can help collage these SSIS displays (outside of screen shots)?|||We are building a tool that will traverse IS packages, SQL Server Databases, and AS to get metadata reports. It should be available around the time we ship.
It's called the "SQL Documenter"...
Thanks,
K|||

Has a timeframe been defined yet?

Thanks Kirk.

|||

Yes, around the time that Yukon ships.
K

|||

KirkHaselden wrote:

We are building a tool that will traverse IS packages, SQL Server Databases, and AS to get metadata reports. It should be available around the time we ship.
It's called the "SQL Documenter"...
Thanks,
K

Now that SQL Server 2005 has been released is there any news on this tool? I have been looking around for it but with no luck.

-Sune|||

The documenter is a "sample" application, not a released product, so there is no firm release schedule for it.

It's not quite ready yet. It should be quite soon, but I don't have a better estimate just now.

Donald

|||Thanks Donald. I'm looking forward.

If you need an external beta tester just let me know :o)

- Sune|||Has there been any new progress made on releasing this tool yet?|||

Did you find anything about your request?

I am looking for a SSIS Documenter too!

|||The DTS documenter was there for a while:
http://www.softrus.org/dts/

And I got a note that SSIS Documenter is coming from these guys as well.|||

Any updates on this?

|||

I found the following free tool.

http://www.bidocumenter.com/Public/Default.aspx

sql

External documentation

I know that the BI studio can build a nice flow document that is associated with the package. Is there an easy way to build a series fo documents that can be shared with other users without having them go into Visual Studio?

Mark E. JohnsonI have an upcoming presentation and was wondering if there were any methods/features that could display all the SSIS packages, tasks, dataflows, relationships, and such in a functional diagram? Similar to what is seen in a ERD diagram. If not, are there any functions or tools that can help collage these SSIS displays (outside of screen shots)?|||We are building a tool that will traverse IS packages, SQL Server Databases, and AS to get metadata reports. It should be available around the time we ship.
It's called the "SQL Documenter"...
Thanks,
K|||

Has a timeframe been defined yet?

Thanks Kirk.

|||

Yes, around the time that Yukon ships.
K

|||

KirkHaselden wrote:

We are building a tool that will traverse IS packages, SQL Server Databases, and AS to get metadata reports. It should be available around the time we ship.
It's called the "SQL Documenter"...
Thanks,
K

Now that SQL Server 2005 has been released is there any news on this tool? I have been looking around for it but with no luck.

-Sune|||

The documenter is a "sample" application, not a released product, so there is no firm release schedule for it.

It's not quite ready yet. It should be quite soon, but I don't have a better estimate just now.

Donald

|||Thanks Donald. I'm looking forward.

If you need an external beta tester just let me know :o)

- Sune|||Has there been any new progress made on releasing this tool yet?|||

Did you find anything about your request?

I am looking for a SSIS Documenter too!

|||The DTS documenter was there for a while:
http://www.softrus.org/dts/

And I got a note that SSIS Documenter is coming from these guys as well.|||

Any updates on this?

|||

I found the following free tool.

http://www.bidocumenter.com/Public/Default.aspx

External documentation

I know that the BI studio can build a nice flow document that is associated with the package. Is there an easy way to build a series fo documents that can be shared with other users without having them go into Visual Studio?

Mark E. JohnsonI have an upcoming presentation and was wondering if there were any methods/features that could display all the SSIS packages, tasks, dataflows, relationships, and such in a functional diagram? Similar to what is seen in a ERD diagram. If not, are there any functions or tools that can help collage these SSIS displays (outside of screen shots)?|||We are building a tool that will traverse IS packages, SQL Server Databases, and AS to get metadata reports. It should be available around the time we ship.
It's called the "SQL Documenter"...
Thanks,
K|||

Has a timeframe been defined yet?

Thanks Kirk.

|||

Yes, around the time that Yukon ships.
K

|||

KirkHaselden wrote:

We are building a tool that will traverse IS packages, SQL Server Databases, and AS to get metadata reports. It should be available around the time we ship.
It's called the "SQL Documenter"...
Thanks,
K

Now that SQL Server 2005 has been released is there any news on this tool? I have been looking around for it but with no luck.

-Sune|||

The documenter is a "sample" application, not a released product, so there is no firm release schedule for it.

It's not quite ready yet. It should be quite soon, but I don't have a better estimate just now.

Donald

|||Thanks Donald. I'm looking forward.

If you need an external beta tester just let me know :o)

- Sune|||Has there been any new progress made on releasing this tool yet?|||

Did you find anything about your request?

I am looking for a SSIS Documenter too!

|||The DTS documenter was there for a while:
http://www.softrus.org/dts/

And I got a note that SSIS Documenter is coming from these guys as well.|||

Any updates on this?

|||

I found the following free tool.

http://www.bidocumenter.com/Public/Default.aspx

External documentation

I know that the BI studio can build a nice flow document that is associated with the package. Is there an easy way to build a series fo documents that can be shared with other users without having them go into Visual Studio?

Mark E. JohnsonI have an upcoming presentation and was wondering if there were any methods/features that could display all the SSIS packages, tasks, dataflows, relationships, and such in a functional diagram? Similar to what is seen in a ERD diagram. If not, are there any functions or tools that can help collage these SSIS displays (outside of screen shots)?|||We are building a tool that will traverse IS packages, SQL Server Databases, and AS to get metadata reports. It should be available around the time we ship.
It's called the "SQL Documenter"...
Thanks,
K|||

Has a timeframe been defined yet?

Thanks Kirk.

|||

Yes, around the time that Yukon ships.
K

|||

KirkHaselden wrote:

We are building a tool that will traverse IS packages, SQL Server Databases, and AS to get metadata reports. It should be available around the time we ship.
It's called the "SQL Documenter"...
Thanks,
K

Now that SQL Server 2005 has been released is there any news on this tool? I have been looking around for it but with no luck.

-Sune|||

The documenter is a "sample" application, not a released product, so there is no firm release schedule for it.

It's not quite ready yet. It should be quite soon, but I don't have a better estimate just now.

Donald

|||Thanks Donald. I'm looking forward.

If you need an external beta tester just let me know :o)

- Sune|||Has there been any new progress made on releasing this tool yet?|||

Did you find anything about your request?

I am looking for a SSIS Documenter too!

|||The DTS documenter was there for a while:
http://www.softrus.org/dts/

And I got a note that SSIS Documenter is coming from these guys as well.|||

Any updates on this?

|||

I found the following free tool.

http://www.bidocumenter.com/Public/Default.aspx

External documentation

I know that the BI studio can build a nice flow document that is associated with the package. Is there an easy way to build a series fo documents that can be shared with other users without having them go into Visual Studio?

Mark E. JohnsonI have an upcoming presentation and was wondering if there were any methods/features that could display all the SSIS packages, tasks, dataflows, relationships, and such in a functional diagram? Similar to what is seen in a ERD diagram. If not, are there any functions or tools that can help collage these SSIS displays (outside of screen shots)?|||We are building a tool that will traverse IS packages, SQL Server Databases, and AS to get metadata reports. It should be available around the time we ship.
It's called the "SQL Documenter"...
Thanks,
K|||

Has a timeframe been defined yet?

Thanks Kirk.

|||

Yes, around the time that Yukon ships.
K

|||

KirkHaselden wrote:

We are building a tool that will traverse IS packages, SQL Server Databases, and AS to get metadata reports. It should be available around the time we ship.
It's called the "SQL Documenter"...
Thanks,
K

Now that SQL Server 2005 has been released is there any news on this tool? I have been looking around for it but with no luck.

-Sune|||

The documenter is a "sample" application, not a released product, so there is no firm release schedule for it.

It's not quite ready yet. It should be quite soon, but I don't have a better estimate just now.

Donald

|||Thanks Donald. I'm looking forward.

If you need an external beta tester just let me know :o)

- Sune|||Has there been any new progress made on releasing this tool yet?|||

Did you find anything about your request?

I am looking for a SSIS Documenter too!

|||The DTS documenter was there for a while:
http://www.softrus.org/dts/

And I got a note that SSIS Documenter is coming from these guys as well.|||

Any updates on this?

|||

I found the following free tool.

http://www.bidocumenter.com/Public/Default.aspx

External documentation

I know that the BI studio can build a nice flow document that is associated with the package. Is there an easy way to build a series fo documents that can be shared with other users without having them go into Visual Studio?

Mark E. JohnsonI have an upcoming presentation and was wondering if there were any methods/features that could display all the SSIS packages, tasks, dataflows, relationships, and such in a functional diagram? Similar to what is seen in a ERD diagram. If not, are there any functions or tools that can help collage these SSIS displays (outside of screen shots)?|||We are building a tool that will traverse IS packages, SQL Server Databases, and AS to get metadata reports. It should be available around the time we ship.
It's called the "SQL Documenter"...
Thanks,
K|||

Has a timeframe been defined yet?

Thanks Kirk.

|||

Yes, around the time that Yukon ships.
K

|||

KirkHaselden wrote:

We are building a tool that will traverse IS packages, SQL Server Databases, and AS to get metadata reports. It should be available around the time we ship.
It's called the "SQL Documenter"...
Thanks,
K

Now that SQL Server 2005 has been released is there any news on this tool? I have been looking around for it but with no luck.

-Sune|||

The documenter is a "sample" application, not a released product, so there is no firm release schedule for it.

It's not quite ready yet. It should be quite soon, but I don't have a better estimate just now.

Donald

|||Thanks Donald. I'm looking forward.

If you need an external beta tester just let me know :o)

- Sune|||Has there been any new progress made on releasing this tool yet?|||

Did you find anything about your request?

I am looking for a SSIS Documenter too!

|||The DTS documenter was there for a while:
http://www.softrus.org/dts/

And I got a note that SSIS Documenter is coming from these guys as well.|||

Any updates on this?

|||

I found the following free tool.

http://www.bidocumenter.com/Public/Default.aspx

sql

extended connection managers for data flow destinations?

It looks like you can only extended connection managers for data flow sources.

Is there anyway to develop a custom connection manager for the SQL destination in the data flow destinations?

I can’t use hardcoded connection strings.I can’t use configurations because they are not encrypted.I already have managed code that will give the corrected connection string.I already have a custom connection manager that I use from the data flow sources.I just need one for data flow destinations but I can’t see a way to extend into the OLE DB?

The OLEDB Destination is just like the OLEDB source and can use the same connection managers. However, you are using the SQL destination and that is a special destination and needs the specific connection manager that it supports. If you want to use your custom connection manager then switch to using the OLEDB destination instead and it should just work.

HTH,

Matt

|||

oledb destinations only support the special destinations as well. there is no way I can see to use a custom connection manger in any of the destinations.

except for the script component and in that you can do whatever you want.

|||

Actually they support anything they see as an OLEDB connection. I can create different types of OLEDB connections and the OLEDB destination will support them (e.g. excel, sql (sqlncli), sql (sqloledb), etc). It does indeed have to be an OLEDB type connection though.

Matt

|||

ok so I think you might have answered my question.

If I develop a custom connection mgr derived from connectionmanagerbase I can't use that custom connection mgr with any of the stock data flow destinations.

because only OLEDB connections can be used and there is no way to create a custom OLEDB connection.

so there is no way to do what I wanted to do.

except the script componet can do what I want but I have to override the correct portions....

|||

Well there are ways to create custom OLEDB connections but you would have to write your own OLEDB provider, which not many would want to do so I guess in your case a script would probably be the best alternative.

Matt

Expression issue with Custom Data Flow Component and Custom property

Hi,

I'm trying to enable Expression for a custom property in my custom data flow component.

Here is the code I wrote to declare the custom property:

public override void ProvideComponentProperties()

{

ComponentMetaData.RuntimeConnectionCollection.RemoveAll();

RemoveAllInputsOutputsAndCustomProperties();

IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();

prop.Name = "MyProperty";

prop.Description = "My property description";

prop.Value = string.Empty;

prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

...

}

In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime

Here is my expression (a file name based on a date contained in a user variable):

"DB" + (DT_WSTR, 4)YEAR( @.[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @.[User::varCurrentDate] ), 2 ) + "\\" + (DT_WSTR, 4)YEAR( @.[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @.[User::varCurrentDate] ), 2 ) + ".VER"

@.[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time

So the expression is evaluated as: "DB189912\189912.VER".

My package contains 2 data flow.

At runtime,

The first one is responsible to set a valid date in @.[User::varCurrentDate] variable. (the date is 2007-01-15)

The second one contains my custom data flow component with my custom property that was set to an expression at design time

When my component get executed, my custom property value is still "DB189912\189912.VER" and I expected "DB200701\200701.VER"

Any idea ?

Are you certain that the data flows are being executed in the order you expect?

What is the scope of your variable?

I tried a test case with 2 data flows connected to each other in the control flow. In the first, I had a row count transform populate a variable (of package scope). In the second, I set a dataflow property expression to use the variable, and confirmed that it did use the value set by the Row Count, so it seems your scenario should work...

Mark

|||

Sorry,

You're right.

I was confused because even in debug mode (when execution is interupted with a DataViewer), the property value was "DB189912\189912.VER"

So I have added a ComponentMetaData.FireInformation with the property value, and the value was "DB200701\200701.VER"

Next time, I will start with the ComponentMetaData.FireInformation... ;-)

Sorry again

Thanks a lot

|||No problem, glad to hear you have it working!

Expression editor on Custom Properties on Custom Data Flow Component

Hi,

I've created a Custom Data Flow Component and added some Custom Properties.

I want the user to set the contents using an expression. I did some research and come up with the folowing:

Code Snippet

IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";

But it doesn't work, if I enter @.[System:ackageName] in the field. It comes out "@.[System:ackageName]" instead of the actual package name.

I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.

Any help would be greatly appreciated!

Thank you

The expression for a component's property is held at the task level. If a property is marked as CPET_NOTIFY, it notifies the task (the data flow which parent's the component), which tells it to generate a new property on which can be set an expression. So to see the expression in the designer look at the properties grid for the data flow task, not the component.

|||Hello Darren,

Thank you for the quick response. But the expression editor doesn't show in the propertiesgrid either.

Could I be missing anything?|||

Are you looking at the properties grid of the DATA FLOW component or at your custom data flow component? Also, did you set the flag darren mentioned above so that the data flow component will know to include this property in it's properties expression list?

|||Ah, yes, I see it now. But it is not what I'm looking for.

Is there now way to set the expresison builder on the component itself? When the expression builder is used on the parent task, I cannot access the incomming rows.

Thank you kindly|||

Why do you think your component will have features over and above that available to Microsoft themselves?

Property expressions are ONLY available at the task level, because they are provided in the task framework.

You have created a property expression, so your mention of incoming rows does not make sense. Property expressions are just that, for the property, but not the value itself, the result will override the value. Just look how they work in the rest of SSIS.

A property that holds a text string that could be parsed as an expression is something different and maybe what you want? Perhaps the Derived Column transform may be easier?

Expression Constraint and Precedence

I have a flow like this:

Container A
|
|
++
| |
| Container B
| |
| |
Container C That Both Connect To
I want to conditionally execute Container B based on a variable. I did this with a Precedence constraint on the line from A to B.

In this case however if it doesn't execute Container C doesn't execute. This is bad since Container C should always execute.

I changed the precedence constraint of B to C to be a logical Or and now Container C starts executing while Container B is executing! Not what I am looking for.

How do I do this? I thought about disabling Container B using an expression on the container but that seemed ugly.

Thanks,

ChrisHi Chris,
Wow, this is a weird one! I've had some fun trying to work this out

1) Put B into a sequence container. Lets call our new sequence container "D".
2) Put an OnSuccess precedence constraint from A to D
3) Put an OnSuccess precedence constraint from A to C
4) Place a script task into D. It doesn't need any code in it because its not going to do anything
5) Put an expression precedence constraint from the script task to B.

B will execute depending on whatever you have in the expression on the precedence constraint (which is what you want) but C will always execute regardless!

You can download a demo of this solution from here: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20050727PrecedenceConstraintanomoly.zip
-Jamie

P.S. The link to your blog (www.vergentsoftware.com/blog/ckinsman) doesnt work. Well it didn't 30 seconds ago anyway.|||Put A and B into a sequence.
Put a precedence constraint between the sequence and C|||Thanks for the blog tip. I left out a letter.

Thanks also for the solution!

expression builder switch()

Via the Expression Builder, I'm using the switch() program flow function to handle my data. I can't seem to figure out how to do the "else" or "default" part of it. Anyone have a clue? Ex:

Switch(
Fields!test.value = 1, "One",
Fields!test.value = 2, "Two",
Fields!test.value = 3, "Three",
)

But what if Fields!test.value is not either 1,2, or 3, how would you set the "else" or "default" part within the switch statement?

Add a condition at the end which always evaluates to true.

Switch(
Fields!test.value = 1, "One",
Fields!test.value = 2, "Two",
Fields!test.value = 3, "Three",
1=1, "Default"
)