Custom Task w/ runtime user interaction

I've been playing around with building custom components for SSIS. I've been doing workflow for years (using Java and Oracle). The company I worked for had a framework for publishing data that allowed for user interaction. That's something I'd love to be able to do in SSIS.

Is it possible to create a custom task that interacts with the user at runtime? So, the user starts the SSIS package. At some point, the process pops up a dialog (Windows Form) that asks the user to set a date using a calendar control.

Any thoughts?

Is it possible? Yes. Is it recommended? No. SSIS is really designed for batch, non-interactive applications. It would be much better to write a custom app that gathers all the user input in the beginning, and then launches the SSIS package, setting any necessary variables based on the user input at that point.|||

That sounds like a great plan...only I cannot figure out a way to do that which does not require writing an entire program, figuring out a way to store the variable in a file, or somehow PASS it to the SQL SSIS Package, and I have NO IDEA how to do that.

I have the EXACT same need as the person who asked you the question, and I'd be happy with manually entering the date one character at a time. The SSIS Package I am writing pulls special information from a database and places it in a text file with fixed widths. I have everythign on this working perfectly. The problem is that Every three Months - I have to write it again from scratch because the starting date changes.

i just want the simplest way to enter a NEW Starting Date (even if that means storing it somewhere on a file server), and run the Agent using the SSIS Package to make it work.

My Perfect Solution would be a THREE STEP Agent, with Step One getting the variable and passing it to step 2, Step 2 being the query I have now importing the date it got from step one, and step three being the notify portion when it is done.

I can find NO USEDUL INFORMATION on how to do this...or maybe I just don't get it.

|||

For that, I would define a variable in the Package to hold the start date. Then use DTEXEC with the /SET switch, which allows you to set a variable's value from the command line. Agent can run DTEXEC, passing it the /SET value.

Another option would be to store the date in a table on your database, and read it into the package using an Execute SQL task.

Since you are running this from Agent, you really don't want a UI, since no user will be there to respond to it.

Custom Task w/ runtime user interaction

I've been playing around with building custom components for SSIS. I've been doing workflow for years (using Java and Oracle). The company I worked for had a framework for publishing data that allowed for user interaction. That's something I'd love to be able to do in SSIS.

Is it possible to create a custom task that interacts with the user at runtime? So, the user starts the SSIS package. At some point, the process pops up a dialog (Windows Form) that asks the user to set a date using a calendar control.

Any thoughts?

Is it possible? Yes. Is it recommended? No. SSIS is really designed for batch, non-interactive applications. It would be much better to write a custom app that gathers all the user input in the beginning, and then launches the SSIS package, setting any necessary variables based on the user input at that point.|||

That sounds like a great plan...only I cannot figure out a way to do that which does not require writing an entire program, figuring out a way to store the variable in a file, or somehow PASS it to the SQL SSIS Package, and I have NO IDEA how to do that.

I have the EXACT same need as the person who asked you the question, and I'd be happy with manually entering the date one character at a time. The SSIS Package I am writing pulls special information from a database and places it in a text file with fixed widths. I have everythign on this working perfectly. The problem is that Every three Months - I have to write it again from scratch because the starting date changes.

i just want the simplest way to enter a NEW Starting Date (even if that means storing it somewhere on a file server), and run the Agent using the SSIS Package to make it work.

My Perfect Solution would be a THREE STEP Agent, with Step One getting the variable and passing it to step 2, Step 2 being the query I have now importing the date it got from step one, and step three being the notify portion when it is done.

I can find NO USEDUL INFORMATION on how to do this...or maybe I just don't get it.

|||

For that, I would define a variable in the Package to hold the start date. Then use DTEXEC with the /SET switch, which allows you to set a variable's value from the command line. Agent can run DTEXEC, passing it the /SET value.

Another option would be to store the date in a table on your database, and read it into the package using an Execute SQL task.

Since you are running this from Agent, you really don't want a UI, since no user will be there to respond to it.

Custom SSIS Task Deployment

I've written several custom Control Flow and Data Flow components for SSIS. I'm trying to deploy a new Control Flow Task, however it will not show up in the "Choose Toolbox Items" window.

I have my dll's all in the right place. Because of other painful issues we have not used the GAC, and instead have put our DLL's that are referenced in the following directory: C:\program files\Microsoft SQL Server\90\DTS\Binn. This has worked fine for other custom tasks, but not for this one.

The only things that are different about this transform are the following:

1. Not only does the task inherit from Task, it also implements two interfaces that I wrote.

2. One of the assembly references is actualy an executable instead of a dll. This is unusual, but I've done it with other .NET projects and not had any trouble.

I could move the code I need from the EXE out to a DLL. If somebody knows what could be causing this please respond?

Is your task in C:\Program Files\Microsoft SQL Server\90\DTS\Tasks ? That is where the Choose Toolbox Items looks for task, or just Reset Toolbox and it will add/remove according to the valid tasks it finds in that folder.|||My task dll is in the C:\Program Files\Microsoft SQL Server\90\DTS\Tasks folder. The difference is the dll's it references are usually placed in the DTS\Binn folder. However for some reason when I added this particular task it didn't look in the Binn folder instead it looked in the Tasks folder for it's supporting dll's. When I added the supporting dll's to the Tasks folder it showed up fine in the choose toolbox items list. I'm not sure what is different from my other tasks and this one that would require the referenced dll's to be in the same folder?|||In turns out the problem didn't have to do with either the EXE or the interface implementation. When we referenced code in our other custom tasks it was only locals inside Task methods. However this time since I was implementing an interface as part of a task it needed the referenced dll's at the time Visual Studio retrieves the list of tasks. So in addition to having the referenced dll's in the DTS/Binn folder I needed them in the DTS/Tasks folder as well.

Custom SSIS Task Deployment

I've written several custom Control Flow and Data Flow components for SSIS. I'm trying to deploy a new Control Flow Task, however it will not show up in the "Choose Toolbox Items" window.

I have my dll's all in the right place. Because of other painful issues we have not used the GAC, and instead have put our DLL's that are referenced in the following directory: C:\program files\Microsoft SQL Server\90\DTS\Binn. This has worked fine for other custom tasks, but not for this one.

The only things that are different about this transform are the following:

1. Not only does the task inherit from Task, it also implements two interfaces that I wrote.

2. One of the assembly references is actualy an executable instead of a dll. This is unusual, but I've done it with other .NET projects and not had any trouble.

I could move the code I need from the EXE out to a DLL. If somebody knows what could be causing this please respond?

Is your task in C:\Program Files\Microsoft SQL Server\90\DTS\Tasks ? That is where the Choose Toolbox Items looks for task, or just Reset Toolbox and it will add/remove according to the valid tasks it finds in that folder.|||My task dll is in the C:\Program Files\Microsoft SQL Server\90\DTS\Tasks folder. The difference is the dll's it references are usually placed in the DTS\Binn folder. However for some reason when I added this particular task it didn't look in the Binn folder instead it looked in the Tasks folder for it's supporting dll's. When I added the supporting dll's to the Tasks folder it showed up fine in the choose toolbox items list. I'm not sure what is different from my other tasks and this one that would require the referenced dll's to be in the same folder?|||In turns out the problem didn't have to do with either the EXE or the interface implementation. When we referenced code in our other custom tasks it was only locals inside Task methods. However this time since I was implementing an interface as part of a task it needed the referenced dll's at the time Visual Studio retrieves the list of tasks. So in addition to having the referenced dll's in the DTS/Binn folder I needed them in the DTS/Tasks folder as well.sql

Custom Data Flow Components, DllImport, and third party dlls

I've been having an issue with the integration of a third-party DLL into a custom data flow component.

The company sent me a C# project that generates a simple console application. The project includes a class that calls their DLL with DllImport. The console application runs fine.

I created a stand-alone class using the C# class they sent to expose the methods of their DLL. In my custom component, I'm referencing this class to pass data to and from their DLL.

The first method from that stand-alone class that my component encounters simply gets their installation path from the registry and does not use DllImport. That path retrieval works fine. The next method calls a function that is declared with DllImport. Each time the call fails with "System.DllNotFoundException = {"Unable to load DLL AMZip.dll': Exception from HRESULT: 0xE06D7363"}".

I've copied this DLL to countless locations (e.g., the PipelineComponents directory, the project/solution bin directory) and included these paths in all manner of path variables.

What am I missing here? Their DLL is not strong named (does this matter since I'm using DllImport?), my stand-alone class is, and of course, the custom component itself is. I appreciate the help.

No bites, eh? OK, is there a standard directory for C++ dlls used in custom components?|||I think the question on everyone's mind is, what does this have to do with SSIS?|||Strictly speaking, your question might be better served in a C# forum or something. While we do custom development here, this isn't a programming forum.|||

Phil Brammer wrote:

I think the question on everyone's mind is, what does this have to do with SSIS?

Thanks for giving me the opportunity to explain why I posted this question in the SSIS forum!

Like I said, the class in my sample console application hums like a sewing machine. When I try to integrate it into an SSIS component it fails by an error for which I can find no explanation and the finest .NET programmer I know can find no explanation, having not dealt much with SSIS internals. There aren't a whole lot of people on Earth who program SSIS components, but one place they can be sure to be found is on this forum.

I guess the upshot is, I'm hoping to get a suggested nugget from someone who may have been down this road before, as opposed to a cocksure question.

|||If your application is built on the .net 2.0 framework, then this shouldn't be that hard. The 1.1 framework won't work in SSIS, I believe.

Getting your DLLs registered in the GAC might also help.|||

The third-party component in question is a C DLL. It has dependencies to msvcm80.dll, msvcp80.dll, msvcr80.dll, and Microsoft.VC80.CRT.manifest. I have a wrapper class for the DLL with DllIImport statements that makes its functions available for .NET.

The question is... does SSIS look for non-.NET dependencies in the system path? They are obviously not in the GAC since they're not .NET.

I am getting an error that leads me to believe the dependencies are not found; however, they are in one of the directories in the system path. What else can I do to get SSIS to find them?

|||

I would expect SSIS to behave much like any other program. Really the SSIS package will be hosted, but I would expect system32 to be a good location. Similarly your dependencies for the C++ DLL would need to be there. You could look at the SSIS install bin folder, not the project, but is a bit unreliable as may not always use a MS host that is in that bin folder. Getting system32 to work I think would be better in the same way that we say always put .Net dependencies in the GAC to ensure the runtime can find what it needs.

So if you try or have tried system32 and it still fails, what is missing? Use Filemon to look for file not found or access denied events and see where it is really looking. http://www.microsoft.com/technet/sysinternals/default.mspx

|||Are you using Vista? Ever since I brought my applications into Vista, I get that error when I try to link any DLL other than windows API. The same applications worked fine on XP. I copied the file all over the place and used the full path when linking and it still says that. I get this problem with all languages, not just C#.|||

Solution -1

1.Copy DLLS to your application\ bin folder(Virtual Dir)

2. Then do the following in code.........before you do a pInvoke

StringBuilder LocalFolder = new StringBuilder(AppDomain.CurrentDomain.BaseDirectory);

LocalFolder.Append("bin");

Directory.SetCurrentDirectory(LocalFolder.ToString());

3. Then give security permission "modify" for the "bin" folder for the following

a. ASP.Net Machine accounts..

b. Network services

c. youself!!!!!!!!!!(ur login)..

4. Try ur luck..

This happens when u are in a comapany with strict access

DISCLAIMER...

I tested it out..works for me..may not work for u..I am not responsible for the damage caused due to this post

Soultion -2

The Administrator need to grant you access to the ASP.NET temporary directory (somewhere in the WINNT ).

DISCLAIMER...

I didn't test it out...got a clue from net..which lead to my solution..I am posting it bcos it may help other poor souls who has this problem..I am not responsible for the damage caused due to this post.

Pls exuce my weak knowledge of .net tehnologies.. I am a C++ /Windows programmer forced to do .Net deployment

Custom Data Flow Components, DllImport, and third party dlls

I've been having an issue with the integration of a third-party DLL into a custom data flow component.

The company sent me a C# project that generates a simple console application. The project includes a class that calls their DLL with DllImport. The console application runs fine.

I created a stand-alone class using the C# class they sent to expose the methods of their DLL. In my custom component, I'm referencing this class to pass data to and from their DLL.

The first method from that stand-alone class that my component encounters simply gets their installation path from the registry and does not use DllImport. That path retrieval works fine. The next method calls a function that is declared with DllImport. Each time the call fails with "System.DllNotFoundException = {"Unable to load DLL AMZip.dll': Exception from HRESULT: 0xE06D7363"}".

I've copied this DLL to countless locations (e.g., the PipelineComponents directory, the project/solution bin directory) and included these paths in all manner of path variables.

What am I missing here? Their DLL is not strong named (does this matter since I'm using DllImport?), my stand-alone class is, and of course, the custom component itself is. I appreciate the help.

No bites, eh? OK, is there a standard directory for C++ dlls used in custom components?|||I think the question on everyone's mind is, what does this have to do with SSIS?|||Strictly speaking, your question might be better served in a C# forum or something. While we do custom development here, this isn't a programming forum.|||

Phil Brammer wrote:

I think the question on everyone's mind is, what does this have to do with SSIS?

Thanks for giving me the opportunity to explain why I posted this question in the SSIS forum!

Like I said, the class in my sample console application hums like a sewing machine. When I try to integrate it into an SSIS component it fails by an error for which I can find no explanation and the finest .NET programmer I know can find no explanation, having not dealt much with SSIS internals. There aren't a whole lot of people on Earth who program SSIS components, but one place they can be sure to be found is on this forum.

I guess the upshot is, I'm hoping to get a suggested nugget from someone who may have been down this road before, as opposed to a cocksure question.

|||If your application is built on the .net 2.0 framework, then this shouldn't be that hard. The 1.1 framework won't work in SSIS, I believe.

Getting your DLLs registered in the GAC might also help.|||

The third-party component in question is a C DLL. It has dependencies to msvcm80.dll, msvcp80.dll, msvcr80.dll, and Microsoft.VC80.CRT.manifest. I have a wrapper class for the DLL with DllIImport statements that makes its functions available for .NET.

The question is... does SSIS look for non-.NET dependencies in the system path? They are obviously not in the GAC since they're not .NET.

I am getting an error that leads me to believe the dependencies are not found; however, they are in one of the directories in the system path. What else can I do to get SSIS to find them?

|||

I would expect SSIS to behave much like any other program. Really the SSIS package will be hosted, but I would expect system32 to be a good location. Similarly your dependencies for the C++ DLL would need to be there. You could look at the SSIS install bin folder, not the project, but is a bit unreliable as may not always use a MS host that is in that bin folder. Getting system32 to work I think would be better in the same way that we say always put .Net dependencies in the GAC to ensure the runtime can find what it needs.

So if you try or have tried system32 and it still fails, what is missing? Use Filemon to look for file not found or access denied events and see where it is really looking. http://www.microsoft.com/technet/sysinternals/default.mspx

|||Are you using Vista? Ever since I brought my applications into Vista, I get that error when I try to link any DLL other than windows API. The same applications worked fine on XP. I copied the file all over the place and used the full path when linking and it still says that. I get this problem with all languages, not just C#.|||

Solution -1

1.Copy DLLS to your application\ bin folder(Virtual Dir)

2. Then do the following in code.........before you do a pInvoke

StringBuilder LocalFolder = new StringBuilder(AppDomain.CurrentDomain.BaseDirectory);

LocalFolder.Append("bin");

Directory.SetCurrentDirectory(LocalFolder.ToString());

3. Then give security permission "modify" for the "bin" folder for the following

a. ASP.Net Machine accounts..

b. Network services

c. youself!!!!!!!!!!(ur login)..

4. Try ur luck..

This happens when u are in a comapany with strict access

DISCLAIMER...

I tested it out..works for me..may not work for u..I am not responsible for the damage caused due to this post

Soultion -2

The Administrator need to grant you access to the ASP.NET temporary directory (somewhere in the WINNT ).

DISCLAIMER...

I didn't test it out...got a clue from net..which lead to my solution..I am posting it bcos it may help other poor souls who has this problem..I am not responsible for the damage caused due to this post.

Pls exuce my weak knowledge of .net tehnologies.. I am a C++ /Windows programmer forced to do .Net deployment

Custom Data Flow Components, DllImport, and third party dlls

I've been having an issue with the integration of a third-party DLL into a custom data flow component.

The company sent me a C# project that generates a simple console application. The project includes a class that calls their DLL with DllImport. The console application runs fine.

I created a stand-alone class using the C# class they sent to expose the methods of their DLL. In my custom component, I'm referencing this class to pass data to and from their DLL.

The first method from that stand-alone class that my component encounters simply gets their installation path from the registry and does not use DllImport. That path retrieval works fine. The next method calls a function that is declared with DllImport. Each time the call fails with "System.DllNotFoundException = {"Unable to load DLL AMZip.dll': Exception from HRESULT: 0xE06D7363"}".

I've copied this DLL to countless locations (e.g., the PipelineComponents directory, the project/solution bin directory) and included these paths in all manner of path variables.

What am I missing here? Their DLL is not strong named (does this matter since I'm using DllImport?), my stand-alone class is, and of course, the custom component itself is. I appreciate the help.

No bites, eh? OK, is there a standard directory for C++ dlls used in custom components?|||I think the question on everyone's mind is, what does this have to do with SSIS?|||Strictly speaking, your question might be better served in a C# forum or something. While we do custom development here, this isn't a programming forum.|||

Phil Brammer wrote:

I think the question on everyone's mind is, what does this have to do with SSIS?

Thanks for giving me the opportunity to explain why I posted this question in the SSIS forum!

Like I said, the class in my sample console application hums like a sewing machine. When I try to integrate it into an SSIS component it fails by an error for which I can find no explanation and the finest .NET programmer I know can find no explanation, having not dealt much with SSIS internals. There aren't a whole lot of people on Earth who program SSIS components, but one place they can be sure to be found is on this forum.

I guess the upshot is, I'm hoping to get a suggested nugget from someone who may have been down this road before, as opposed to a cocksure question.

|||If your application is built on the .net 2.0 framework, then this shouldn't be that hard. The 1.1 framework won't work in SSIS, I believe.

Getting your DLLs registered in the GAC might also help.|||

The third-party component in question is a C DLL. It has dependencies to msvcm80.dll, msvcp80.dll, msvcr80.dll, and Microsoft.VC80.CRT.manifest. I have a wrapper class for the DLL with DllIImport statements that makes its functions available for .NET.

The question is... does SSIS look for non-.NET dependencies in the system path? They are obviously not in the GAC since they're not .NET.

I am getting an error that leads me to believe the dependencies are not found; however, they are in one of the directories in the system path. What else can I do to get SSIS to find them?

|||

I would expect SSIS to behave much like any other program. Really the SSIS package will be hosted, but I would expect system32 to be a good location. Similarly your dependencies for the C++ DLL would need to be there. You could look at the SSIS install bin folder, not the project, but is a bit unreliable as may not always use a MS host that is in that bin folder. Getting system32 to work I think would be better in the same way that we say always put .Net dependencies in the GAC to ensure the runtime can find what it needs.

So if you try or have tried system32 and it still fails, what is missing? Use Filemon to look for file not found or access denied events and see where it is really looking. http://www.microsoft.com/technet/sysinternals/default.mspx

|||Are you using Vista? Ever since I brought my applications into Vista, I get that error when I try to link any DLL other than windows API. The same applications worked fine on XP. I copied the file all over the place and used the full path when linking and it still says that. I get this problem with all languages, not just C#.|||

Solution -1

1.Copy DLLS to your application\ bin folder(Virtual Dir)

2. Then do the following in code.........before you do a pInvoke

StringBuilder LocalFolder = new StringBuilder(AppDomain.CurrentDomain.BaseDirectory);

LocalFolder.Append("bin");

Directory.SetCurrentDirectory(LocalFolder.ToString());

3. Then give security permission "modify" for the "bin" folder for the following

a. ASP.Net Machine accounts..

b. Network services

c. youself!!!!!!!!!!(ur login)..

4. Try ur luck..

This happens when u are in a comapany with strict access

DISCLAIMER...

I tested it out..works for me..may not work for u..I am not responsible for the damage caused due to this post

Soultion -2

The Administrator need to grant you access to the ASP.NET temporary directory (somewhere in the WINNT ).

DISCLAIMER...

I didn't test it out...got a clue from net..which lead to my solution..I am posting it bcos it may help other poor souls who has this problem..I am not responsible for the damage caused due to this post.

Pls exuce my weak knowledge of .net tehnologies.. I am a C++ /Windows programmer forced to do .Net deployment

Custom Component development questions

I have looked at everything I could find on custom components, and I'm still confused about what has to be implemented in ReinitializeMetadata. The first custom component I'm trying to build is very similar to the Audit component, a derived column synchronous transformation that should be able to create any non-empty subset of four types of output columns.

I haven't found anything that does much with the inputs, the samples all seem to rely on RemoveInvalidInputColumns. I haven't found any substantial explanation of what this method actually does, or whether there are cases it doesn't handle. If it really handles all problems on the input side I don't need to know the details, but I'd like to hear some confirmation that this is the case.

For outputs, most of the sample projects seem to just call the base method. Some projects throw out all the output columns and create new ones to match the input columns one-to-one. Project Real throws away all the output columns and external metadata columns and creates a standard set of output columns with external metadata. The RegEx sample project uses information in a component custom property to tell it what output columns to expect, then adds missing columns and deletes unwanted columns. All of these approaches are using some reference information to inform it which outputs belong and which don't. What do you do when there is no other reference, just the output column and external metadata column collections?

The description for this method is "fix the errors found in Validate", but these projects just delete everything that wasn't perfect and recreate anything missing. In cases where the number and types of output columns require some user configuration, maybe a few custom properties, it seems a little high-handed to throw it all away and make them start from scratch. Or do you assume that this method isn't called unless the component state is really screwed up? It seems to me that simply adding or deleting input and output columns in the Advanced Editor would trigger ReinitializeMetadata because the external metadata would be out of sync. If it reacted by immediately re-adding an optional default column you deleted, or deleting an unconfigured column you just added, it would be pretty annoying.

Assuming that I wanted ReinitializeMetadata to actually fix things, I have some specific questions:

1 If an output column is not mapped to an external metadata column (or mapped to a nonexistent emc), should the output column be deleted or should a matching external column be created?

2 Same question reversed, if an external column is not mapped to any output column, should it be deleted or a matching output column created?

3 Would it make sense to look for compatible unmapped output and external columns and remap them?

4 If the user adds or deletes an output column in the advanced editor, was that an output column or an external metadata column?

And another thing, is there a definative list of which methods should be marked as not cls-compliant?

If your component is similar to Audit you do not need to worry about external columns, or input columns or ReinitializeMetadata.

All you need to do is to allow inserting output columns (implement InsertOutputColumn) and probably add a custom property to identify them. They can be added using the advanced editor.

You can look at the metadata of Audit transform in the advanced editor to get an idea.

|||

I asked for enlightenment, and a reply of "you do not need to worry about it" doesn't really help. If my next project is a more complicated component I'll need to worry about it, so I'm still trying to get a more fundamental understanding if the design-time behavior. I'm not optimistic enough to expect my first component project to work the first time, and to debug it I think I need to know more about what the base class does, what behavior I need to add to it, and what I should avoid doing to it.

My basic problem is that I don't feel the published documentation or available sample code give me a good general idea of what a fully-featured and robust implementation of a custom component should include.

I have looked at the Audit transform in the advanced editor, and it is very close to what I need (at least for outputs). One question that it answered was that changing the ouput column datatype as a side effect of a custom property change is apparently legal (I assume this is happening in SetOutputColumnProperty). It appears to have a custom editor UI for the custom property, but knowing that one exists doesn't help me build my own.

The Calendar sample project showed me everything I needed to know about creating a TypeConverter for my custom properties. I think a lot of my current questions would be answered if I could get sample code for the design-time behavior of the Audit component. Is there any chance of that happening?

|||

It is good to get feedback on specific gaps in the documentation - the docs team watch this forum very careful to plan their future content. So, while you won't see code for our stock components being shared, you may well see these issues addressed in future samples. Our documentation in 2005 is progressive and is being constantly refreshed.

Meanwhile, let's see if we can get you some answers to those questions - but do bear in mind that Bob was trying to help you with your specific needs to help you over a bump in developing your component, rather than giving a detailed advanced tutorial. Forums are great for the former, not so good for the latter.

Donald

|||

Unless you understand why you would need to use external metadata, and for normal transforms you do not, then these answers may not make sense, and certainly don't apply to an Audit style transform.

1 If an output column is not mapped to an external metadata column (or mapped to a nonexistent emc), should the output column be deleted or should a matching external column be created?

Delete the output column. This is an invalid state, and with correct coding of the component it should never happen. The best defense is to override all the input/output and column methods on the PipelineComponent class. Throw errors if people try and do something silly. Then only way left for someone to mess it up is via code as your UI and the Advanced Ui should both use the managed wrapper when manipulating columns etc.

2 Same question reversed, if an external column is not mapped to any output column, should it be deleted or a matching output column created?

It should be left alone really, although that depends on the component design. Normally you create the output column and map to "select" it. Not all external columns have to be selected, in most components at least, hence component design.

3 Would it make sense to look for compatible unmapped output and external columns and remap them?

You should look for invalid mappings and silly stuff in Validate, and return the needs new metadata status. If you then fix them in RMD, well that is up to you. What is you component supposed to do. Simple fixes I'd say go for it, but think how it may impact the user, and could it give new meaning to what they wanted originally? Saying that see answers for 1 and 2 first.

4 If the user adds or deletes an output column in the advanced editor, was that an output column or an external metadata column?

An output column.

And another thing, is there a definative list of which methods should be marked as not cls-compliant?

The compiler will warn you. Set true at the assembly level, then any methods which are non-compliant and not attributed as such will generate a warning, so easy enough to pick up and fix.