March 1, 2011 at 5:18 am
Hi folks,
I've been scouring the forums but haven't quite found the answer I need.
This is my second week of SSIS so apologies to all for the likely ineptness of my question.
1. I have a file watcher set up that calls a dataflow that will process , load and then move files on arrival in a source directory. - All works great and was easy to implement.
2. I have added a Exec SQL Task that I want to write a record to a load_log to record the status of the received file.
3. I have a number of SSIS variables defined (5) which hold the information that I wish to write off to my load_log table.
4. I have tried both direct sql and calling a stored procedure in the Exec SQL task but am getting a fairly common error message suggesting that I am not passing data in my parameters.
5. Each of my variables has been created as a parameter with a corresponding type
5.1 Although there seems to be conflicting advise on the net as to whether I should give the parameters names or leave them set to 0 also as to whether I should give the parameters lengths or set them to -1
6. If we deal with the stored proc example as that is probably the route I want to go down
My Connection Type is ADO
The SQL Source Type is DirectInput
The SQL Statement is Exec mis.dbo.update_loadlog ?,?,?,?,?
IsQueryStoredProcedure is True
Is there anything obvious that I have missed in the description above.
TIA
Steve
March 1, 2011 at 5:53 am
This article gives a good overview:
http://blogs.msdn.com/b/mattm/archive/2006/11/08/stored-procedures-with-output-parameters.aspx
and so does this one:
http://www.sqlis.com/sqlis/post/The-Execute-SQL-Task.aspx
I personally use the OLE DB connection manager, where parameters are replaced with a question mark. Important is that the ordinal position is important, meaning that the parameter with name 0 will replace the first question mark, parameter 1 the second one and so on...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 1, 2011 at 5:56 am
I prefer to use OLEDB Destination, so I will show an example in that.
The SQL Statement is:
Exec mis.dbo.update_loadlog ?,?,?,?,?
In the 'Parameter Mapping', set the first 'Parameter Name' to 0, the next to 1. This corresponds left-to-right, the '? you have. In your case you would have 0-4.
Of note, I always pass by name, not position.
exec mis.dbo.update_loadlog @paramA=?,@paramB=?,@paramC=?,@paramD=?,@paramE=?
Josef Richberg
2009 Exceptional DBA
http://www.josef-richberg.squarespace.com
http://twitter.com/sqlrunner
March 1, 2011 at 6:00 am
Thanks folks,
The problem was indeed down to the fact that different connection types using different naming and referencing for parameters.
I've changed the connection to ADO.NET so that the naming and referencing is consistant. ie
@variable
Regards
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply