0

I'm developing and testing an SSIS package in SQL 2012 to import the rows from a text file into a staging table(that is truncated before each execution) using a Script Task. Then an Execute SQL task is used to run a stored procedure, which then queries the staging table and formats the data for insertion into a final table.

Presently I am only testing with two different files. These files are separate emails received daily, which I have configured in Outlook to save as text files. Each file is renamed like YYYYDDMM_HHMMSS_[filename].txt. My SSIS package uses a For Each loop to assign this filename to a variable, @AckFileName, which I pass into my stored procedure and parse the file datetime from it.

So the first line of my stored procedure is: 

SELECT DISTINCT CONVERT(datetime,left(@AckFileName,8)+' '+substring(@AckFileName,10,2)+':'+substring(@AckFileName,12,2)+':'+substring(@AckFileName,14,2))

When I run the stored procedure in SSMS it works correctly:

Exec [dbo].[ParseAckFile] @AckFileName

However, even though I have the same identical code for my SQL Statement in my Execute SQL task, I receive the following error: 

"Exec [dbo].[ParseAckFile]..." failed with the following error: "Conversion failed when converting date and/or time from a character string."

Ok, so this seems simple/obvious enough...except the query in my stored procedure is converting the string to datetime, so it should also return an error when run manually, correct? I've tested this with each file, and the results are the same - the proc runs fine manually with either file, but the Execute SQL task fails.

Additionally: I've tried setting Delay Validation = True within the Execute SQL task, using either an OLEDB or ADO.NET connection to my final table, and using an expression within the Execute SQL task to build my query. None of these attempts made any difference to the error message I am receiving.

Any other suggestions I can try?

Pete Gossett
  • 21
  • 5
  • Have you tried watching a profiler trace to verify that your SSIS package is sending the parameter value you think it is? – Tab Alleman Sep 23 '16 at 19:19
  • Yes @TabAlleman, the filename in the parameter is the one I am expecting. I just discovered something though - for testing, I hard-coded the filename within the stored proc, then removed the parameter from my Execute SQL command. It worked correctly. Now I'm wondering if even though my parameter is Varchar, if I might need to encapsulate it within single-quotes inside the proc? I'll need to do more testing... – Pete Gossett Sep 23 '16 at 20:16

1 Answers1

0

After spending all afternoon testing, I finally figured out what the issue was... Since my For Each loop was retrieving the filename that I was eventually passing to this stored procedure, it contained the full UNC path/name for the file - which I knew. However, when viewing the variable value in SSIS it showed the pathname in the format used in my Script Task - \\[server\[folder\[subfolder]\[filename] - so I mistakenly used that exact syntax in my REPLACE command within the stored proc to update the @AckFileName variable before passing it to my insert query. Once I figured out my error and corrected it, the package runs fine!

Pete Gossett
  • 21
  • 5