Dataflow tranformation with selective column inputs

[rofltrain]

I'd like to set up a dataflow script transformation that users can
select columns, and column types.

Example 1 - ToString format
I'd like to convert from various types (e.g. date, int32, float) to
strings, and apply standard formatting.

A user should be able to select multiple columns, and for each column,
define a format string and an output column. The script task would
then call the standard Date.ToString (or Int or whatever), and store
the value in the associated output column.

Is there a way to implement this easily? To allow a column list, and
two values associated with each column (one value from a fixed list of
types)

[Ivan Peev]

Hi,

I'm wondering why do you want to implement that type of
transformation? You already have the standard 'Data Conversion'
transformation, which provides the functionality you describe.

[rofltrain]

The standard component doesn't doesn't let you specify a format
string. e.g. if I wanted to convert a date field to a string in
YYYYMMDD format, or a decimal number to 3 decimal places.

[Ivan Peev]

This does make sense. I'm guessing you want to know how this could be
implemented using the Script Component Plus. The user interface is the
tricky part. I can suggest one way, which will work for multiple input
columns. Here is the idea:

1. Setup a property named InputColumns. Apply this CozyRoc attribute:

<InputVirtualColumn("", False)>

This will make property for selection of multiple input columns. This
is the property where you pick the input columns you want to format to
a string.

2, Setup another propery named OutputFormat. Apply this CozyRoc
attribute:

<TextDialog("Enter format specification")>

This is the property, which will contain the formatting and output
column for each input column. Each formatting will be specified on a
separate line and the line will start with the output column name. The
specification will look like this:

OutputColumn1=Format1
OutputColumn2=Format2
...

That should be the user interface for the script. What do you think?

p.s.
This sounds like a nice script to implement.

[rofltrain]

Yeah, definitely doable, but a little bit clunky.

I was hoping it was possible (without too much difficulty) to
implement a custom editor of some description.

Basically it would present grid of 3 columns. First column would be
the "input column", second would be the "format string", third would
be "output column"

Users would select the input column from a dropdown, type in the
format string, then select (or add??) an output column.

I get the feeling that this is probably a bit too difficult for the
script task, and would require a custom component. Any ideas on a
component or example out there I could use as a base to learn how to
implement something like this?

[Ivan Peev]

Well, the best solution would be the hardest to implement (would
require custom logic to handle). And btw you can implement any type of
user interface for the SSIS script task. You may check the following
script for reference:

http://www.cozyroc.com/script/get-file-list-task

Take a look at the FileFolder property. It has an editor defined in
the script called FolderEditor. Right now this editor calls the
standard FolderBrowserDialog. However you can easily setup your own
class descending from the standard Form class and implement your own
custom dialog with your exact requirements.

p.s.
The solution I have described is indeed clunky. However it is a notch
better compared to modifying a script. We are thinking about
implementing this script. Would you be interested to give it a try?

[rofltrain]

Unfortunately my programming experience has mainly been data focused,
not UI. So i'm a bit rusty on the winforms controls.

If I could get an example of how to call a custom windows form as a
property editor, I could certainly do it and release it as a sample
script. Can you just open the script task in the IDE, click "Add
Form", design your form and logic, and then call it somehow as a
property editor?

or do you need to create/declare the form programatically at runtime?

If you can create the form via form designer, I could easily set this
up, and it could be saved with the STX file (so no dependencies). It'd
just be a simple 3 column grid, populate one based on the input
columns available, one based on the output, and one as a free text
field.

[Ivan Peev]

Check the link with the sample I have sent in my previous message. It
contains answer to the exact question you are asking on how to open
property editor with custom Form.

[rofltrain]

Yeah, I have read the link however that utilizes an existing .Net
UITypeEditor.

I haven't developed a usercontrol in a while, let along a
UITypeEditor. It'll take me a while to figure out how to use / call it
with a custom form.

[Ivan Peev]

It doesn't use existing UITypeEditor. FolderEditor is implemented in
the script. Check the top of the script.

[rofltrain]

yeah, I'll give it a go. Don't expect to hear from me for a few months
though, as there's a fair few other (non-ui) scripts to write.

but if I do succeed, I'll post it in the shared samples.