none
SSIS - cannot connect to database after putting tasks in sequence container

    Question

  • I had a package that worked perfectly until i decided to put some of its tasks inside a sequence container (More on why I wanted to do that - How to make a SSIS transaction in my case?).

    Now, i keep on getting an error -

    [Execute SQL Task] Error: Failed to acquire connection "MyDatabase". Connection may not be configured correctly or you may not have the right permissions on this connection.

    Why could this be happening and how do I fix it ?

    Friday, November 08, 2013 10:00 PM

Answers

All replies

  • My be you run the tasks in parallel?

    Show what you did if I did not guess.


    Arthur My Blog

    Friday, November 08, 2013 10:08 PM
  • My be you run the tasks in parallel?

    Show what you did if I did not guess.


    Arthur My Blog

    No parallel tasks. This is the logical structure - 

    For-each loop {

    sequence 1{

    }

    sequence 2{

    //just created

    //"transactionOption" is set to enabled/required on the sequence container.

    }

    sequence 3{

    }

    }//for each loop

    1,2,3 are connected in that order. I can't show a screenshot though.



    • Edited by blastoSO Saturday, November 09, 2013 6:32 PM
    Friday, November 08, 2013 10:15 PM
  • Seriously, WTH is wrong with this SSIS ? This connection password is a perennial problem in SSIS. I have tried Jaspersoft, SAP Data Services etc. and I have never faced this. Why does this even happen ? 



    • Edited by blastoSO Monday, November 11, 2013 2:54 AM
    Friday, November 08, 2013 10:31 PM
  • I tried to use config files and they don't work too. The connection strings, usernames and passwords in the files are correct. Why is this happening ?

    Friday, November 08, 2013 11:53 PM
  • Have you tried to disable the new Sequence2 container task to isolate the issue? Does the package work well for the other containers?

    Could you try to recreate the OLEDB connection and assign it to the Execute SQL task?


    - Aalam | (Blog)

    Saturday, November 09, 2013 12:53 AM
  • Have you tried to disable the new Sequence2 container task to isolate the issue? Does the package work well for the other containers?

    Could you try to recreate the OLEDB connection and assign it to the Execute SQL task?


    - Aalam | (Blog)

    Thanks. Actually, the same DB connection worked in other sequence containers (not shown in above "code"). Then, someone else tried working on the package. Now even the old connection does not work. Great ! I forgot to mention that "transactionOption" is set to enabled/required on the sequence container. 

    Recreating the connections did not help either. also, I am now using config files with correct user names and passwords etc embedded in them. That fails too.

    Now I am really curious to know why SSIS behaves this way. Its so mysterious in this regard. I'll try my best to drive this SSIS out of my environment and get something more reliable, even if it is not free.


    • Edited by blastoSO Saturday, November 09, 2013 6:32 PM
    Saturday, November 09, 2013 6:31 PM
  • The dtsx file is just an XML file. Can you browse through the xml to see what password is saved in the connection string.

    The packages get corrupted sometimes (yes they do) and maybe that has happened here too.

    Sunday, November 10, 2013 10:03 AM
  • Recreating the connections did not help either. also, I am now using config files with correct user names and passwords etc embedded in them. That fails too.

    Now I am really curious to know why SSIS behaves this way. Its so mysterious in this regard. I'll try my best to drive this SSIS out of my environment and get something more reliable, even if it is not free.

    I'm amazed, staring at your chutzpah . I have been tracking your posts which started to appear recently. It seems your knowledge about SSIS is really low and you blame your misunderstanding and lack of experience on the platform. I also read somewhere you claim to be developer. However some of your statements and babble show your developer skills are very low as well. Perhaps if you are a bit humble you might be able to learn the platform. This is a friendly tip.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Sunday, November 10, 2013 11:58 PM
  • The dtsx file is just an XML file. Can you browse through the xml to see what password is saved in the connection string.

    The packages get corrupted sometimes (yes they do) and maybe that has happened here too.

    As far as I know, the package will not save passwords because of the don't save sensitive option and because I am picking the connection information from a config file, right ? 

    Monday, November 11, 2013 2:56 AM
  • Recreating the connections did not help either. also, I am now using config files with correct user names and passwords etc embedded in them. That fails too.

    Now I am really curious to know why SSIS behaves this way. Its so mysterious in this regard. I'll try my best to drive this SSIS out of my environment and get something more reliable, even if it is not free.

    I'm amazed, staring at your chutzpah . I have been tracking your posts which started to appear recently. It seems your knowledge about SSIS is really low and you blame your misunderstanding and lack of experience on the platform. I also read somewhere you claim to be developer. However some of your statements and babble show your developer skills are very low as well. Perhaps if you are a bit humble you might be able to learn the platform. This is a friendly tip.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Yes, I am a rookie developer to be precise. I'll admit that I have seen far more books and videos on SSIS than any other ETL tool. But, they don't really cover these important real world problems. I don't know where to go for solving such problems. I would prefer to fix my problem myself by reading a book or manual, but I don't think that there is enough material. How does one become an expert in SSIS ?

    For now, I want to solve this problem. Can you help me to do so ?

    Thanks.


    • Edited by blastoSO Monday, November 11, 2013 3:05 AM
    Monday, November 11, 2013 3:01 AM
  • Blasto,

    you need a weekend crash course on SSIS basics.


    Arthur My Blog

    Monday, November 11, 2013 4:03 PM
  • Blasto,

    you need a weekend crash course on SSIS basics.


    Arthur My Blog

    Yes, I had already done that. Its not much help. Its like a super basic course. It does not even cover the concepts of ETL which I guess are somewhat universal, regardless of the tool you use for it (eg. Delta Loading)

    Monday, November 11, 2013 9:31 PM
  • The answer is here - http://microsoft-ssis.blogspot.com/2011/09/ssis-transactions-with-tsql.html

    Dont use SSIS transactions. They are complicated. Use TSQL transactions embedded in SSIS instead.

    • Marked as answer by blastoSO Monday, November 11, 2013 9:37 PM
    Monday, November 11, 2013 9:37 PM
  • Not sure how are "transactions" related to your originally reported issue related to acquiring connection. But glad that you took the time to return here and marked your thread as answered.

    - Aalam | (Blog)

    Tuesday, November 12, 2013 3:22 AM
  • The answer is here - http://microsoft-ssis.blogspot.com/2011/09/ssis-transactions-with-tsql.html

    Dont use SSIS transactions. They are complicated. Use TSQL transactions embedded in SSIS instead.

    Tuesday, November 12, 2013 6:16 AM