0

I've developed a simple SSIS package that will open a csv file, select a number of column, add two derived columns and then copy the results to a file.

This works within visual studio (even if it takes ages) where I constructed it. The problem is when I attempt to deploy it to our sql server it fails.

It keeps say that:

"The specified parameter value string is too long. The string must be no more than 8000 characters." 

But I have no idea what it is talking about as none of my columns is over 1000 characters.

There isn't much info on this error but is it possible that the number of rows (400,000+) is the source of the problem?

  • From your question and comments, it seems like there is no actual reason to use SSIS for this. You'll have more luck with Powershell or C# – Nick.McDermaid Aug 14 '18 at 7:05
0

This isn't a problem with your data - it is a problem with your package. Usually, this is a problem with the size of your query.

It appears that internally, SSIS uses a varchar(8000) value somewhere that could actually use a varchar(max) value. When you run up against this limit, you get this ambiguous error.

There are several ways you can approach this. You can shorten your query (by removing excessive whitespace, replacing spaces with tabs, compacting indents, etc).

You can also split your query out into multiple variables or parameters, then concatenate the pieces back together with an expression task before executing the query.

Depending on the query, you could also convert it into one or more stored procedures or views, and call those within your package.

But if you keep the query inside the package, you need to reduce its length (or its piece's lengths) to less than 8000 characters. Have fun.

Have fun - nothing is more fun than refactoring a query.

  • Well given that moving from text stream to string seems to have vastly improved the query speed I think you are onto something – Exostrike Jul 30 '18 at 9:25
  • Afraid that isn't the issue, I'm not actually running any sql queries I'm only working with files and then producing a new version. I've gone through the entire package xml file and I can't even find anything that large. – Exostrike Jul 30 '18 at 12:28
  • 1
    turns out the connection string for the files will only accept 4000 characters, not the 8000 the error was suggesting. Really nice it doesn't tell you that. – Exostrike Aug 20 '18 at 8:56
  • Add this as a solution, not just a comment, and more people will see it. – Laughing Vergil Aug 20 '18 at 18:18
0

If you are on SQL Server 2008 or newer you can use VARCHAR(MAX)

DECLARE @sql VARCHAR(MAX)

varchar(max) should work just fine, the following is SQL:

declare @cmd varchar(max)
set @cmd = 'print /*' + replicate ('-', 7990);
set @cmd = @cmd + replicate ('-', 7990) + '*/ getdate()';
exec (@cmd)
print datalength (@cmd)
  • That doesn't really solve me problem, I'm not actually doing any SQL queries, just working with files. Thats why this is so weird. – Exostrike Jul 30 '18 at 12:43
0

I had a exactly same error, also no long sql string, so I assume the resolution could be similar if not exactly the same. I went by elimination removing stuff from my package until I figured out what control is giving me issues. In my case the reason was a very long and odd string on a property called FilterObjectState value of a custom TF connection (I usually copy and paste controls having those connections, probably not a good idea). The string was over 4000 characters. As soon as I got rid of that the projects deployed and the 8000 characters error stopped. Connection properties are also saved with the project file, so looking to the project file in Notepad for long strings could be another (maybe easier) start on debugging this issue...

0

So I discovered what the problem was.

Turns out the source of the problem was the length of the file location string for the multiple files I was processing. SSIS couldn't handle this if the string was longer than 4000 characters, rather than the 8000 characters the error message suggested.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.