Post reply

Connection Manager Expression set my Connection to Work Offline

  • December 14, 2014 at 6:16 pm

    #296030

    Hi,

    I was wondering if someone can help me. I am new to SSIS. I am going through these exercises in a book and I am getting a weird error. I am trying to set a dynamic connection for a connection manager through expressions. I am going by the book. In essence, The book has you create a connection and then set the ServerName and Initial Catalog to the Database name. Once I do that there is like a slight pause in VS and the connection manager turned to "Work Offline" so I could never get the OLEDB Source that works with the connection to work. If I try to bring the connection back online and run the package I get an error stating that a Connection can't be Acquired because the connection is offline. It's like an endless loop. Now I know the exercise works because I tried it on a different laptop and it works. The only difference is that on the laptop where it doesn't work I have SQL Server BI and on the one it works I have the Enterprise Edition. This is the error I am getting...

    Error at Data Flow Task [OLE DB Source [56]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "RAFAEL-PC\RCSQLSERVER.tempdb" failed with error code 0xC0014041. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    Error at Data Flow Task [SSIS.Pipeline]: OLE DB Source failed validation and returned error code 0xC020801C.

    Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

    Can anyone help this is driving me crazy.

    RC

  • December 14, 2014 at 11:30 pm

    #1764647

    Hi,

    I had same issue. But it was solved when I tried the default value to the connection string.

    You must be using some variable to assign conn string dynamically(through expression). I would suggest first give the valid string (to your variable) by default, anyways while running it will take the expression value.

    It will surely solve the issue.

    Thanks & Regards,

    Pratiksha

  • December 15, 2014 at 7:32 am

    #1764726

    Thanks that worked. Funny I had tried that before. It must have been more than one thing and that had to be done as well. Another thing that is funny is that on my other laptop I didn't have default values and it worked ... although it gave me a Can not acquire connection error once the package finished executing... with out out results mind you.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply