none
Unable to build connection string using Package Level variables from the AcquireConnection fonction within "Script Component"

    Question

  • Hi all!

    In SSIS 2008, within a script component trying to refer to a package level variables that was added to the ReadOnlyVariables collection of the component, the following fails within the AcquireConnnection function and I get this error:

    Error 1 Validation error. Test1: Test1: Microsoft.SqlServer.Dts.Pipeline.ReadOnlyVariablesNotAvailableException: The collection of variables locked for read access is not available at this point.     à Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)     à Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction)     à Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)

     

        string connectionString = "DSN=" + this.Variables.MYDSN;
        Conn = new OdbcConnection(connectionString);
        Conn.Open();

     

     

    If I move the previous code in the PreExecute function, it runs fine though.

     

    Then, if I move the connectionString variable to the class definition, and populate it from within the PreExecute function and leave the last 2 lines in the AcquireConnection function, I get the following error:

    Error 1 Validation error. Test1: Test1: System.InvalidOperationException: The ConnectionString property was not initialized.     à Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)     à Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction)     à Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)

    Does anyone have an idea how I can circonvent this problem so I can conform to MS best practice when it comes to building a db connection in the AcquireConnection section of the class ?

    Many thanks in advance for your time guys :-)
    Monday, September 13, 2010 7:11 PM

Answers

  • Will check those out as soon as possible.

    Thanks again and have a great week end :-)

    • Marked as answer by Champignol Sunday, February 3, 2013 12:02 AM
    Friday, September 17, 2010 12:48 AM

All replies

  • My guess is if you want to set it up it has to be writable (why readonly?).
    Arthur My Blog
    By: TwitterButtons.com
    Monday, September 13, 2010 7:18 PM
    Moderator
  • Simply because I only want to read the variables value not write to it. Please note that the code acually works in the PreExecute section of the class.

     

    Thanks anyway :-)

    Monday, September 13, 2010 8:08 PM
  • First, I'd have to ask why you're attempting to build a connection inside a script component, rather than using a Connection Manager to accomplish that task.  But that doesn't answer your question.

    AcquireConnections comes before PreExecute (see Run-time Methods of a Data Flow Component) which is why moving your variable reading to PreExecute means the internal class variable is empty when you try to use it in AcquireConnections.

    Variables placed in the ReadOnlyVariables property are locked in the PreExecute phase, just before your PreExecute code is called.

    Therefore, it's not possible - using the ReadOnlyVariables property - to extract information from SSIS variables to be used in the AcquireConnections method.  First, this is a great indicator that what you're attempting to do probably isn't the way that Microsoft intended you to do it - hence my initial question at the top of this response ;)

    You CAN work around this by manually locking and unlocking the variable yourself in code.  But again, I don't recommend that unless it's absolutely necessary.

    PS: ReadWriteVariables won't behave any better.


    Todd McDermid's Blog Talk to me now on
    Monday, September 13, 2010 8:12 PM
    Moderator
  • Sorry, I do not see the complete picture, e.g. where you use the package variable. Also the font you used is small I can hardly read it.
    Arthur My Blog
    By: TwitterButtons.com
    Monday, September 13, 2010 8:13 PM
    Moderator
  • First, you are totally right, I SHOULD use the connection manager like I usually do for all my other projects.

    But you may have noticed that in SSIS 2008, there is no ODBC DataFlow and any ODBC ConnectionManager connection I create won't show as a data flow object. So I'm stuck using the Script Component you see :-)

    Thanks alot for your link! If this works, at least I'll be able to conform somewhat the code to MS recommendations by relocating the whole connection process in the proper section (AcquireConnection)

     

    ;-)

     

    Wednesday, September 15, 2010 9:37 PM
  • No, there's no ODBC Connection Manager, but there is an ADO.Net Connection Manager that will hit ODBC sources.  You're not stuck using a script - and you should seriously look at changing that now that you know!
    Todd McDermid's Blog Talk to me now on
    Wednesday, September 15, 2010 10:27 PM
    Moderator
  • You are right again! But you may have noticed that unlike the Oledb Dataflow Source, the ADO.NET DataFlow Source will not let me use a SQL Statement from a Variable. So anytime i need to build a dynamic sql command, i'm stuck!!! Worst, the ODBC dsn cannot be used within the OleDB DataFlow Souce! So now you know why I'm using the Script Component :-)

    Thanks again for your time :-)

    Wednesday, September 15, 2010 10:38 PM
  • The ADO.NET Source does allow you to set an expression on the Sql Statement property, and that expression can obviously be direct from a variable.

    Another perfectly valid option is to use the ADO.NET Connection manager with the Script Component. When you call the AcquireConnection method of the connection manager you get a perfectly valid .NET object such as a System.Net.Data.OdbcConnection object (namespace may be missing a bit).


    http://www.sqlis.com | http://www.konesans.com
    Thursday, September 16, 2010 7:14 AM
    Moderator
  • I just like words like "obviously" :-)

    I tried this approach and it does not work. Please be kind and provide me with a simple example of a dynamically constructed SQL Statement for the ADO.NET DataFlow Task that will work with some non MS database. For instance, we use Acomba accounting system dans I need to construct the following dynamically and it alwys fails using your recommended path.

     

    SELECT * FROM Invoicing WHERE InTimeModified > { d '2009-10-01'}

     

    Thanks in advance for lighning my bulb :-)

    Thursday, September 16, 2010 2:01 PM
  • Obviously when you know how it will be easy... and I've not made a mistake or mis-understood the requiremnt :-)

    If the SQL you quoted works when you apply it directly to the property then, you can set that same SQL via an expression. Perhaps try that first just to ensure the expression approach works, nothing fancy just the static value. I want to make sure we are on the same track.

    You can then expand the expression to do something more dynamic. I'm not sure what you need the expression to do, perhaps it is the date? Here is an example that builds the same SQL, but uses today's date.

    "SELECT * FROM Invoicing WHERE InTimeModified > { d '" 
    + (DT_WSTR,4)YEAR(GETDATE()) + "-" + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-" 
    + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + "'}"
    

    If the static SQL works, but the expression fails, it suggests to me that the expression is not evaluating to the same as the static, so the expression itself is wrong.

     


    http://www.sqlis.com | http://www.konesans.com
    Thursday, September 16, 2010 2:13 PM
    Moderator
  • Hi again!

    I tried the same approach before and it failed. But just to make sure, I copied/pasted your simple example in the "SQL Command text" property of the ADO.NET source editor for my Acomba ODBC DNS Thru ADO.NET Source Data FLow Task and when I click on "Preview" it fails with the usual error:

     

    TITLE: Microsoft Visual Studio
    ------------------------------
    
    There was an error displaying the preview.
    
    ------------------------------
    ADDITIONAL INFORMATION:
    
    ERROR [42000] [Acomba ODBC Driver]Expected lexical element not found: <keyword> (ACOODBC32.DLL)
    
    ------------------------------
    BUTTONS:
    
    OK
    ------------------------------
    

     

    BTW I really appreciate the time you're putting on my problems :-)

     

    Thursday, September 16, 2010 2:52 PM
  • I think you've got confused the SSIS expression functionality. What I posted was an in the SSIS expression language, so is not valid directly,

    Select the Data Flow component and select F4 to get the properties grid open. In the grid click "Expression", then select the property for your source, e.g. "[My ADO.NET].[SqlCommand] and enter it in there.

    Perhaps have a quick read around the topic too - http://msdn.microsoft.com/en-us/library/ms141214.aspx or http://www.simple-talk.com/sql/ssis/working-with-property-expressions-in-sql-server-integration-services/


    http://www.sqlis.com | http://www.konesans.com
    Thursday, September 16, 2010 4:26 PM
    Moderator
  • Uh! I'm confused!

    There is NO such properties for the SSIS 2008 ADO.NET Source Data Flow Task

    I'd be glad to send you a screen capture.

    Thursday, September 16, 2010 4:41 PM
  • Moreover, I've already read those articles and they only work for the OLEDB Data Flow task and the ExecuteSQL Task NOT the ADO.NET Data Flow task unfortunately.

    I really can't get my head around the fact that the product misses that key feature!!!

    Thursday, September 16, 2010 4:46 PM
  • http://msdn.microsoft.com/en-us/library/ms136104.aspx - According to the documentation, and my experience, the ADO.NET Source's SqlCommand property does support expressions.

    I wasn't clear, expressions are set at the Data Flow Task level, not the ADO.NET Source, it's parent is the Data Flow task.

    Select the Data Flow task and press F4 to get the properties grid open. In the grid click "Expressions", then select the property for your source, e.g. "[My ADO.NET].[SqlCommand] and enter it in there.

    If you are in the Data Flow designer, inside a sepecific Data Flow task, then click the backrgound to select the task. The task has Expressions as an expandable property.


    http://www.sqlis.com | http://www.konesans.com
    Thursday, September 16, 2010 4:55 PM
    Moderator
  • The ado.net property Darren is talking about is exposed through the data flow task expression list.


    Please mark answered posts. Thanks for your time.
    Thursday, September 16, 2010 5:00 PM
    Moderator
  • Wow! Thanks! But that's far from obvious if you ask me! :-) It's in fact very convoluted! The fact that the ADO.NET Data Flow Taks has not been fitted with the very simple and intuitive OleDB "SQL Command From Variable" baffles me!

    Now, is there a quick way to modify the expresssion at runtime from a userspace variable?

    Thanks for your patience for my bad english guys! :-)

    Thursday, September 16, 2010 5:17 PM
  • Yep, it is hidden quite well.

    You can write an expression that uses variables e.g.

    "My expression + " @[User::MyVariable]    e.g. My expression has a variable

    or you can just enter the variable name, no need for anything else, assuming the value of which is the entire SQL statement. Play around in the expression editor, using the Evaluate option to test the result.

     

    BIDS Helper has some nice features for working with expressions such as enhanced editor, see Expression List and SSIS Variables Window Extensions. There is also a standalone SSIS Expression Tester which is also handy for developing expressions. (Bias alert! I wrote the later and have made some small contributions to the former.)

     

     


    http://www.sqlis.com | http://www.konesans.com
    Thursday, September 16, 2010 5:33 PM
    Moderator
  • Will check those out as soon as possible.

    Thanks again and have a great week end :-)

    • Marked as answer by Champignol Sunday, February 3, 2013 12:02 AM
    Friday, September 17, 2010 12:48 AM