locked
Populating OLEDB connectionstring with variable value RRS feed

  • Question

  • I've been having trouble with this for the last couple of days, i'm using a script task to read the registry values from the local machine so that when the ssis package goes from machine to machine or environment to environment it can read the local registry on the machine and populate the values as needed, which is what i want. But when I take the variable value and try to pass it to the connection manager as an expression value for the connectionstring value i get the error below.

    The value in the registry that i'm grabbing is this (Provider=SQLOLEDB;Server=local;Database=Context;UId=sa;Password=blah;Trusted_Connection=no;Max Pool Size=200)

    Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.

    An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80040E14  Description: "Statement(s) could not be prepared.".
    An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80040E14  Description: "Invalid object name 'dbo.table'.".

    Basically that is telling me that the connection could not be resolved, i have delay validation on everything in the package, so i know its not that. Am i missing something when it comes to passing the variable value correctly? Any help would be greatly appreciated :)


    Friday, August 3, 2012 2:24 PM

Answers

  • I've answered my own question, I was doing everything right all along, the issue was that i was not passing the correct value to the variable. I was passing the connection value for ContextDBConnectionString where that table didn't exist, I was passing the value for another connection string to that variable. The issue was in the script task before the validation of the connection.
    Friday, August 3, 2012 3:30 PM