asked
Execute SQL Task fails in SSIS package but runs successfully outside package
Hi, I created an execute sql task to save the filenames encountered in a Foreach Loop task into a database table, this works fine when I run the the individual task and it writes the filename to the database table (see below) ![alt text][1] However when i run the complete SSIS package, this task fails (see below) ![alt text][2] The error message I am getting is Error: String or binary data would be truncated. [Execute SQL Task] Error: Executing the query "insert into filename_staging_table (FileName) Val..." failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Also, this works perfectly fine in my test environment (windows 7, sql server 2008 r2) The issue only arises in my production environment (windows server 2003, sql server 2008 r2) Any guidance with this greatly appreciated....I am completely stuck!!!! Thanks [1]: /storage/temp/558-execute+sql+1.jpg [2]: /storage/temp/559-execute+sql+2.jpg
Are you deriving the file name from a variable? If so, is the variable the entire path or just a subset of the path (ie: the file name)? Also, how big is the column in which the file name is landing? Is the table in Production the exact same schema as what is in the test environment?
Yes, it is derived from a variable. The variable returns the entire path. The column is nvarchar (100). Yes it is the exact same schema, i used a script created from the test environment. Using the "Script Table As" option.
As a side note, you could put a watch on the variable to see what the value is when it errors out. Reference:
http://www.bidn.com/blogs/ShawnHarrison/ssis/2408/ssis-tips-watch-your-variables
Is there any possibility that one of the variables is longer than 100 characters? Can you expand the column to a larger value, say 250?
Hi JohnM and Valentino Vranken, Thanks for all your inputs, much appreciated... the issue is resolved, I overlooked the fact that my test environment is taking files from a local drive, where as my production environment is taking files from a networked drive with a much longer filepath. So JohnM, your comment is the solution in the end ***> Is there any possibility that one of > the variables is longer than 100 > characters? Can you expand the column > to a larger value, say 250?*** I was looking for a far more complicated issue than actually existed.....thanks for your inputs, it really helps to put these questions out there, i spent the whole day yesterday trying to resolve this one!!
Put a breakpoint on the *ESQLT - Save File Name* task. This can be done by selecting it and then hitting F9. Then run the package. It will stop before the task with the breakpoint. Now you should be able to see the content of the package variables from the **Locals** window (Debug > Windows > Locals). This allows you to check if your variables content fits into the database column.
As others have pointed out and you resolved it the error message > Error: String or binary data would be truncated. is telling you that the database is not able to accept the value being passed to it.
12 People are following this question.
