Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Maximum size of SQL variable in SSIS ? Expand / Collapse
Author
Message
Posted Sunday, November 17, 2013 1:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:51 PM
Points: 183, Visits: 480
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.
Post #1514956
Posted Sunday, November 17, 2013 2:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,180, Visits: 12,033
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1514959
Posted Sunday, November 17, 2013 12:49 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 13,295, Visits: 11,086
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1515015
Posted Sunday, November 17, 2013 4:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:51 PM
Points: 183, Visits: 480
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 ?
Post #1515033
Posted Sunday, November 17, 2013 11:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,180, Visits: 12,033
Have a look at this page. All sorts of useful info on limits there, including max. batch size.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1515088
Posted Monday, November 18, 2013 12:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:51 PM
Points: 183, Visits: 480
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 ?
Post #1515096
Posted Monday, November 18, 2013 12:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,180, Visits: 12,033
Batch size.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1515103
Posted Monday, November 18, 2013 12:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 13,295, Visits: 11,086
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1515112
Posted Monday, November 18, 2013 1:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,180, Visits: 12,033
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1515119
Posted Monday, November 18, 2013 1:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 13,295, Visits: 11,086
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1515123
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse