locked
SSIS: Pumping data from on premises to SQL Azure RRS feed

  • Question

  • This seems to happen no matter what I try.

    How do I correct this? Thanks!

    Description: An exception has occurred during data insertion, the message returned from the provider is: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    Monday, March 21, 2011 8:46 PM

Answers

All replies

  • Are you trying to send a large amount of data to SQL Azure?  If so, perhaps SQL Azure is timing out and you might want to try "chunking" up the data into smaller pieces?
    • Proposed as answer by Voclare Monday, March 21, 2011 9:46 PM
    Monday, March 21, 2011 9:46 PM
  • Hi kevin,

    I've actually blogged about this very issue. Please read Migrating Large Databases to from On-Premise to SQL Azure, I'm sure you'll find exactly what you're looking for.

    hope this helps.

    ~Cory()


    Cory Fowler Windows Azure MVP http://blog.syntaxc4.net
    • Proposed as answer by SyntaxC4Editor Monday, March 21, 2011 10:06 PM
    • Marked as answer by Kevin Mcc Tuesday, March 22, 2011 7:18 PM
    Monday, March 21, 2011 10:05 PM
    Answerer
  • As per the error has the following lines "the message returned from the provider is" ; - are you able to connect to SQL Azure from local machine (say via SSMS)?


    - Paras Doshi



    • Edited by Paras Doshi Wednesday, July 4, 2012 1:20 AM edited signature
    Tuesday, March 22, 2011 12:25 AM
  • Thanks a lot, Cory! It was perfect.

    *But*, is there anything I can do to make it faster?
    It seems like SELECT from sql azure is 100 times faster than INSERT into SQL Azure.

    Tuesday, March 22, 2011 7:19 PM
  • How does one accomplish this chunking in SSIS?

    Thanks a lot!

    Tuesday, March 22, 2011 7:20 PM
  • Hi kevin,

    I've actually blogged about this very issue. Please read Migrating Large Databases to from On-Premise to SQL Azure, I'm sure you'll find exactly what you're looking for.

    hope this helps.

    ~Cory()


    Cory Fowler Windows Azure MVP http://blog.syntaxc4.net


    Hi Cory,

    Joe here, I am working with Kevin.  First off, very nice article!  It did help us out, for a few days.  Now suddenly, the timeouts are back, in two separate packages, with the same error during data insertion.  Now, neither will work at all, both timing out very quickly.  It's as if something has changed on the server side.

    In your article you said this: " there are two key settings that should be changed from their default values, Asynchronous Processing (set to true) and Connection Timeout (increase to 1500)."

    So, I have three unrelated questions:

    1. Is there something innately unstable inherent to SQL Azure servers that provoke this error, in unpredictable ways?  Could that explain why the packages worked fine, and then with no warning suddenly stopped working and would never work again?
    2. Setting the timeout to a very large value (e.g. 1500) or to 0 (wait indefinitely?) worked for a time and then began to timeout.  This timeout occurs well short of 1500 seconds, leading me to believe it's a command timeout, not a connection timeout.  We are generating our packages in the SSMS export data wizard and saving them to the server and so we don't have access to the command timeout.  Would exporting the packages to Visual Studio and editing the command timeout have any realistic hope of succeeding?
    3. Parenthetically, I'm wondering whether the SQL Azure community at large is experiencing similar problems and whether the Azure team is aware of it, and whether we can elevate this issue, by opening a ticket perhaps, and try to get a resolution?  As Kevin pointed out above, importing data from the cloud is very fast, but exporting data to the cloud is problematic, if not impossible, even in relatively small batches (< 1mm rows). 

    Many thanks!

    Joe

    Thursday, March 24, 2011 4:16 PM
  • Hi Joe,

    You'll be happy to hear that the SQL Azure Team has announced something that might make you feel better about the Import/Export Story.

    http://blogs.msdn.com/b/sqlazure/archive/2011/03/24/10145578.aspx

    To be able to understand some more about SQL Azure you will want to keep your eye on a few resources:

    1. Windows Azure Service Dashboard
    2. Connections & SQL Azure <-- Answers #1 (#3 - Yes the team is aware of it)

    If you're working with SQL Azure on a regular basis I would suggest keeping up with the SQL Azure Team Blog, you will find many people on there in which you can reach out to for support at the Product Team level.

    ~Cory()


    Cory Fowler Windows Azure MVP http://blog.syntaxc4.net
    Friday, March 25, 2011 6:11 PM
    Answerer