April 19, 2017 at 7:24 am
I have a DTSX file that contains a query that calls an SQL CLR function. That function changed to add a new parameter, so as expected, execution started failing with the message that "An insufficient number of arguments were supplied for the procedure or function ...". However, updating the query in the file does not correct the error.
I edited the query in the file to include the new parameter, which was simply a BIT, but the package continues to fail with the same message. I can copy the query text out into SSMS and it executes fine and if I remove the newly added parameter from the query I get the original error message about the number of parameters from SSMS, so I know that's the issue. I drag the file from the folder into an editor to make the changes, save it and see the file date change, and execute it in the folder manually by double-clicking and executing it in the SQL SSIS utility, so I know it's the same file. I even close the SSIS utility between attempts to ensure it's loading the file each time rather than just executing it again. It's almost like the package is cached, but I can't find anything that indicates that's something SSIS does. I have even logged off the server and logged back in to see if that clears some sort of cache, but again, no joy.
Any help you could offer would be greatly appreciated.
April 19, 2017 at 7:31 am
Can I assume that you're editing the dtsx file directly on the file directory (probably located on your SQL server), and you're not using the SSISDB deployment method? (If so, why?)
If not, sounds like you're not deploying the project to the server.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 19, 2017 at 7:35 am
We are using local files on the server. That's what I inherited and there has been no compelling reason to change it.
April 19, 2017 at 7:41 am
Personally, I would suggest moving to the SSISDB deployment method. It's a lot more secure (no storing passwords in plain text in a file directory) and easier to manage.
As for caching, SQL server shouldn't cache a local copy of the file, no. You don't mention it, but the package does run ok in Visual studio as well, correct? How is the package being run by SQL server, agent? have you tried editting the task by reselecting the package in the file browser?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 19, 2017 at 7:58 am
The package is normally run by a Powershell script and I've never used VS to edit the file. I just used Notepad++ since the changes have been so minor. The job has worked fine for years, and I know that this single change it what caused the failure. I just don't understand why the edit is not reflected at runtime. I may have to work around it by editing the function to remove the new parameter, but the idea was to keep the SQL function exactly as-is since .NET apps use the same function, so as to avoid any unintended differences.
April 19, 2017 at 8:12 am
bsisson - Wednesday, April 19, 2017 7:58 AMThe package is normally run by a Powershell script and I've never used VS to edit the file. I just used Notepad++ since the changes have been so minor. The job has worked fine for years, and I know that this single change it what caused the failure. I just don't understand why the edit is not reflected at runtime. I may have to work around it by editing the function to remove the new parameter, but the idea was to keep the SQL function exactly as-is since .NET apps use the same function, so as to avoid any unintended differences.
Notepad++ really isn't the editting tool of choice for SSIS package. I would strongly suggest running the file in Visual Studio, you'll be able to properly debug it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 19, 2017 at 8:38 am
Since this was an emergency, I ended up altering the SQL CLR function to remove the new parameter, and that fixed the issue. I still don't know why SSIS was no happy about the new parameter, but at least it's working again.
April 19, 2017 at 1:56 pm
bsisson - Wednesday, April 19, 2017 8:38 AMSince this was an emergency, I ended up altering the SQL CLR function to remove the new parameter, and that fixed the issue. I still don't know why SSIS was no happy about the new parameter, but at least it's working again.
It's really simple, actually. Changing a query parameter is not something you can get away with doing in Notepad++. I'm surprised the package still passed validation. You'll need to use VS of a high enough version (2013 or greater, I believe) that has SSDT installed with it to perform the edit.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply