January 11, 2016 at 3:42 pm
I have a package that just started failing after a couple of years due to an invalid variable value. It runs fine in BIDS but fails when run as a sql agent job. It is a foreach loop running over a folder with a bunch of csv files in it with a date at the end (see below). It is looking for a date older than a cutoff date and will remove greater than 30 days old. The date is being used in a precedence constraint which is where it must be failing. @ArchiveFileNameDate <= @ArchiveCutOffDate
How can I identify the value of the variable or csv file it is failing on when it's running via sql agent? I have wasted so much time on this and need to resolve it.
This is the error.
Description: Casting expression "(SUBSTRING(@[User::ArchiveCurrentFile],FINDSTRING(@[User::ArchiveCurrentFile],"_",1) + 1,4) + "-" + SUBSTRING(@[User::ArchiveCurrentFile],FINDSTRING(@[User::ArchiveCurrentFile],"_",1) + 6,2) + "-" + SUBSTRING(@[User::ArchiveCurrentFile],FINDSTRING(@[User::ArchiveCurrentFile],"_",1) + 9,2))" from data type "DT_WSTR" to data type "DT_DBDATE" failed with error code 0xC00470C2.
This is the variable
(DT_DATE) (DT_DBDATE) (Substring(@[User::ArchiveCurrentFile],FINDSTRING(@[User::ArchiveCurrentFile], "_", 1 )+1,4)+ "-"+ Substring( @[User::ArchiveCurrentFile],FINDSTRING(@[User::ArchiveCurrentFile], "_", 1 )+6,2)+"-"+substring(@[User::ArchiveCurrentFile],FINDSTRING(@[User::ArchiveCurrentFile], "_", 1 )+9,2))
This is what the format of the file it's reading is. There must be a file that isn't following this format but I can't find out which one.
AccountAustraliaLoadErrors_2015-12-13.csv
thanks in advance for your help. Much appreciated.
January 11, 2016 at 4:08 pm
If it works fine in BIDS but not as a SQL Agent job, then it's likely a rights issue or an issue with a parameter being passed at execution time.
You could used an event handler to either email you variable/parameters values, or write then to a file or table, when an error occurs.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 11, 2016 at 7:33 pm
I've never used event handlers until this and I can't seem to get them to work. I tried to write the variables to a SQL table but the table was empty. Tried logging but couldn't get to work. I checked the authority between my authority and the authority of the SQL agent job and they seem to be the same. This is why I want to see the variables.
Any help with that would be greatly appreciated.
January 11, 2016 at 11:48 pm
Dave C. (1/11/2016)
How can I identify the value of the variable or csv file it is failing on when it's running via sql agent?
This is a basic logging requirement and there is a learning curve on SSIS Logging and on Event Handlers so easiest way would be to, in your Foreach Loop Container as the very first thing add an Execute SQL Task that logs the value of the file name offered from the Foreach Loop into a table. When the Package fails the last row inserted is the one causing the issue.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply