none
Column name changes do not refresh in SSIS package RRS feed

  • Question

  • I'm doing maintenance on a complex SSIS package.  Part of the package has an OLE DB Source component which feeds to an OLE DB Destination component (both embedded inside a ForEach Loop component).

     

    Now, regarding the maintenance I need to do: In the underlying table of the OLE DB Source component, some column names have changed.  However, the package editor doesn't refresh properly to reflect these column name changes and, if I try to change them manually, the update fails and the Available Input Columns continues to show the old column names.

     

    Ben

     

    Friday, August 8, 2008 9:50 PM

Answers

  • Open the Variables window (SSIS..Variables), and scroll through the list until you see SRV_Conn. If you don't see it, click the Show All Variable button at the top of the tool window (4 button from the left) and it should show up. Then, in the Value column for that variable, put in your default server name.

     

    Wednesday, August 20, 2008 4:05 PM
    Moderator

All replies

  • If an underlying table column names have changed, the SSIS package should show a warning similar to this (here I changed column AddressID to AddressID1):

     

    Warning 4 Validation warning. Data Flow Task: {AD6F2697-165C-4011-83A4-391EC61FDB93}: The external columns for component "OLE DB Source" (1) are out of synchronization with the data source columns. The column "AddressID1" needs to be added to the external columns.  The external column "AddressID" (16) needs to be removed from the external columns.   Package10.dtsx 0 0 

     

    And when you open the oledb source editor, it should give you an option to correct it.

    When you say, when you try to change them manually, the update fails, what do you mean. What error do you get?

    Monday, August 11, 2008 8:49 PM
    Moderator
  •  

    OK, here are some more details about this package:

     

    The package was originally built based on the cover page article in SQL Server Magazine February 2008 and has worked fine in its published format.

     

    It has several ForEach loops which collect various data from a list of target servers.  Therefore, in the OLEDB Source, there’s no server name (since the server name is provided dynamically during run time via a SELECT query which populates some variables).

     

    In the process of enhancing this package, however, the package editor doesn’t allow updates in certain areas.  For example, if you try to change the Connection manager or the resulting columns, it gives you this error: The AcquireConnection method call to the connection manager “MultiServer” failed with error code 0xC0202009.

     

    Ben

    Wednesday, August 13, 2008 10:32 PM
  • Ok, that makes sense. Since server name is dynamically set, at design time, it can't validate that columns are out of sync. I think OLEDB Source must be set to DelayValidation = true, you can try changing it to "False", and set variables such that it now points to the server you want, and then see if OLEDB source can now connect to the server at design time, and validate columns.

     

    Thursday, August 14, 2008 12:45 AM
    Moderator
  • DelayValidation was already False (which didn't work) and I changed it to True, but that didn't work either.

    Thursday, August 14, 2008 8:47 PM
  •  Ben Aminnia wrote:

    DelayValidation was already False (which didn't work) and I changed it to True, but that didn't work either.

     

    did you try deleting and recreating the ole db source?

     

    hth

    Friday, August 15, 2008 7:18 AM
    Moderator
  • No I haven't yet.  I have exchanged some emails with the original author of that package and I'm awaiting his reply before trying any further changes.

     

    Ben

    Monday, August 18, 2008 9:03 PM
  • You might try setting the ValidateExternalMetadata property of the OLE DB Source to TRUE.

     

    Tuesday, August 19, 2008 2:32 AM
    Moderator
  • Try this...

     

    In the OLE DB Source Editor Columns, try unchecking and checking again all Available External Columns.  After this mouse over the External Column and Output Column below and check if it changes.

     

    Another option is to right click on the OLE DB Source and the click Show Advanced Editor.  In the Advanced Editor for OLE DB Source go to Input and Output Properties tab and manually edit each item under the OLE DB Source Output - Output Columns.

     

    hope this helps.

    Tuesday, August 19, 2008 7:21 AM
  •  

    ValidateExternalMetadata is already set to TRUE.

     

    Ben

    Tuesday, August 19, 2008 2:59 PM
  •  

    More specifically, once I open OLE DB Source Editor and select my connection manager in the drop down combo box, it doesn't let me click Columns or click OK.  Both of those give me a verbose error: DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER ... error code 0xC0202009.

     

    Ben

    Tuesday, August 19, 2008 4:25 PM
  • Make sure that the connection manager has a valid server name in it at design time. Since this package is designed to loop through servers, the connection manager probably uses an expression to set the connection string or server name. There should be a variable that holds the server name, which will have an initial value in the IDE. Make sure that the initial value for the variable is valid in your environment.

     

    Tuesday, August 19, 2008 9:22 PM
    Moderator
  • I put a valid server name in the connection manager click [Test Connection] which works fine and click OK to close it.  After that, I go to the OLE DB task to point to the above connection manager, but it fails to accept it (with the long error I mentioned earlier).  Finally, I go back and reopen the connection manager, but the server name I just put in there is gone.

     

    Ben 

    Wednesday, August 20, 2008 3:41 PM
  • That is the expected behavior if there is an expression that controls the server name property. Select the connection manager, and hit F4 to bring up the Properties window (or it may be already docked in the VS IDE). Scroll through properties until you see Expressions. Select it, then click the ellipsis (...) button to the right to see the Properties Expressions dialog. You should an item in this dialog that shows either the ServerName property or the ConnectionString property. Select the ellipsis button in the expression column, and you should see the Expression Builder dialog. Somewhere in the expression will be a reference to a variable in the package (often with an @ in front of it, or surrounded by brackets []). That's the variable that you need to set to a valid default value,
    Wednesday, August 20, 2008 3:49 PM
    Moderator
  • Yes I can see all of this: Property is ServerName and Expression is @[User:Tongue TiedRV_Conn].  However, I don't see where I can specifiy the default value for the variable.

     

    Ben

    Wednesday, August 20, 2008 4:02 PM
  • Open the Variables window (SSIS..Variables), and scroll through the list until you see SRV_Conn. If you don't see it, click the Show All Variable button at the top of the tool window (4 button from the left) and it should show up. Then, in the Value column for that variable, put in your default server name.

     

    Wednesday, August 20, 2008 4:05 PM
    Moderator
  • Never mind.  Variable default value is set in the Package Explorer.

     

    Ben

    Wednesday, August 20, 2008 4:45 PM
  • Variable default solved the problem.  Thanks,

     

    Ben

     

    Wednesday, August 20, 2008 4:51 PM
  •  Ben Aminnia wrote:

    Never mind.  Variable default value is set in the Package Explorer.

     

    Ben

     

    fyi, the same variables can be seen in the variables window.

     

    hth

    Friday, August 22, 2008 6:17 AM
    Moderator