Maximum size of SQL variable in SSIS ?

  • I have a string variable which stores a big set of sql statements, ie insert statements. The insert statement intends to inserts about 200K rows with 10 columns (Lots !)

    I think that my package always fails when the SQL string is this big. So, I was wondering if there is any limit on the size of the sql string ? It shows no errors or warnings.

  • I have a string variable which stores a big set of sql statements, ie insert statements. The insert statement intends to inserts about 200K rows with 10 columns (Lots !)

    I think that my package always fails when the SQL string is this big. So, I was wondering if there is any limit on the size of the sql string ? It shows no errors or warnings.

    Yes there's a limit. I searched Google and found it in two minutes. Have you even tried?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • blasto_max (11/17/2013)


    I have a string variable which stores a big set of sql statements, ie insert statements. The insert statement intends to inserts about 200K rows with 10 columns (Lots !)

    I think that my package always fails when the SQL string is this big. So, I was wondering if there is any limit on the size of the sql string ? It shows no errors or warnings.

    How does your package fail? How do you know it fails if there are no errors or warnings.

    Regarding the limit: try using a stored procedure instead.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Phil Parkin (11/17/2013)


    I have a string variable which stores a big set of sql statements, ie insert statements. The insert statement intends to inserts about 200K rows with 10 columns (Lots !)

    I think that my package always fails when the SQL string is this big. So, I was wondering if there is any limit on the size of the sql string ? It shows no errors or warnings.

    Yes there's a limit. I searched Google and found it in two minutes. Have you even tried?

    Yes, but may be my words were not correct. What words did you use ?

  • Have a look at this page. All sorts of useful info on limits there, including max. batch size.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin (11/17/2013)


    Have a look at this page. All sorts of useful info on limits there, including max. batch size.

    Maximum Capacity Specifications for SQL Server....I'll keep that in mind and bookmarks. Thanks.

    Which part of the link tells me how to find the max size of SQL string ?

  • Batch size.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin (11/18/2013)


    Batch size.

    I think the limit in SSIS is smaller.

    I've had issues with very large SQL statements in Execute SQL Tasks, but they were less than 64K characters if I remember correctly.

    If the string is stored in an SSIS variable configured with an expression, prior to SQL Server 2012 the limit was 4000 characters.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (11/18/2013)


    Phil Parkin (11/18/2013)


    Batch size.

    I think the limit in SSIS is smaller.

    I've had issues with very large SQL statements in Execute SQL Tasks, but they were less than 64K characters if I remember correctly.

    If the string is stored in an SSIS variable configured with an expression, prior to SQL Server 2012 the limit was 4000 characters.

    Interesting. My ExecuteSQL statements are usually very short, so I have not come up against any limit.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin (11/18/2013)


    Koen Verbeeck (11/18/2013)


    Phil Parkin (11/18/2013)


    Batch size.

    I think the limit in SSIS is smaller.

    I've had issues with very large SQL statements in Execute SQL Tasks, but they were less than 64K characters if I remember correctly.

    If the string is stored in an SSIS variable configured with an expression, prior to SQL Server 2012 the limit was 4000 characters.

    Interesting. My ExecuteSQL statements are usually very short, so I have not come up against any limit.

    I have. 😀

    A lot of views with a lot of columns strung together with UNION ALL.

    When using expressions to create dynamic SQL, you can hit the limit of 4k characters pretty fast.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 4K chars ? Thats like a full stop for me. How about 500K chars plus ? 🙂

  • blasto_max (11/18/2013)


    4K chars ? Thats like a full stop for me. How about 500K chars plus ? 🙂

    Use a stored procedure, like I mentioned earlier.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 12 posts - 1 through 11 (of 11 total)

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