November 29, 2019 at 8:16 am
I am new to SSIS. I am calling a stored procedure from Execute SQL Task with the syntax:
EXEC usp_AddUpdate ?, ?, ? OUTPUT, ? OUTPUT
The stored procedure has 4 parameters, i.e. 2 input & 2 output parameters
@QuaterID bigint=null
,@UserName nvarchar(256)=null
,@Success bit output
,@OutMessage varchar(512) output
In SSIS, I created new variables in Parameter Mapping tab of the Execute SQL Task. It is as follows:
Variable name Direction Data Type Param. Name Param. Size
User::QuaterID Input NUMERIC 0 -1
User::UserName Input NVARCHAR 1 -1
User::Success Output BYTE 2 -1
User::OutMessage Output LONG 3 -1
a) I just need to know whether it is correct & will it work there?
b) Also, should I specify anywhere the Default value NULL of stored procedure Input Parameters.
Thanks in advance
November 29, 2019 at 12:39 pm
Looks close. You should add the correct param size for the NVARCHAR column.
Quarter has an 'r' in it.
I do not understand your question (b). If you plan to call the proc without including certain params, you need specify the NULL defaults in the proc definition. If you will always include the params in your proc calls, don't specify defaults.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 29, 2019 at 12:40 pm
Also, the 0, 1, 2 ... parameter naming is valid for OLEDB connections. If you are using an ADO connection, you should include the actual parameter names.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 12, 2019 at 1:58 pm
Thanks, I am using the OLEDB connection.
I want to pass the following query resultset value as input parameter value for @QuaterID.
SELECT QuaterID FROM Nodes WHERE NodeID = (SELECT MAX(NodeID) FROM Nodes)
For this, I have created a Execute SQL Task with the above query. This task will execute ahead of the task which execute the stored procedure.
How to set the value return by the query as the value of the input parameter @QuaterID.?
December 12, 2019 at 2:14 pm
'Quater' ... do you mean 'Quarter', by any chance?
Not sure I understand: are you trying to set the value of an SSIS package variable to the 'QuaterID' which the above query returns?
Your query might run faster in this form ... worth a test anyway:
SELECT TOP (1)
QuaterID
FROM Nodes
ORDER BY NodeID DESC;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 12, 2019 at 2:40 pm
Yes, this query returns a value 4. This value 4 needs to be passed as input parameter value for input parameter @QuaterID.
December 12, 2019 at 2:41 pm
How can it be done? Please explain the steps.
December 12, 2019 at 6:49 pm
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 23, 2019 at 6:48 am
Thank You Phil..It worked like charm. Both query tweak & SSIS tip worked great for me.
January 23, 2020 at 12:46 pm
When the above query in the ExecSQL task returns 0 rows, the task is getting failed due to 'Single Row' property.
What can be done to avoid this error & keep the task running?
January 23, 2020 at 1:51 pm
You need to make sure that your SQL returns a row (and exactly one row), even if there is no underlying data. You could do this with a UNION ALL and an appropriate ORDER BY to ensure that the 'dummy row' is selected only if there is no 'proper' data.
With ExpandedDataset as (
select col1, col2
from table
union all
select col1 = -9999, col2 = -1
)
select top (1) *
from ExpandedDataset
order by col1 desc
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 24, 2020 at 11:42 am
Thanks for the reply. I changed my above query accordingly i.e. the way you have suggested. I hope it will work.
With ExpandedDataset as (
select NodeID, QuaterID
from Nodes
union all
select NodeID = -9999, QuaterID = -1
)
select top (1) QuaterID
from ExpandedDataset
order by NodeID desc;
Also, I am am giving the table schema of the table, Nodes. Please advice me whether using the present datatype used, these columns will hold the value like -9999 & -1.
CREATE TABLE [dbo].[Nodes](
[NodeID] [bigint] NOT NULL,
[QuaterID] [tinyint] NULL,
CONSTRAINT [PK_Nodes] PRIMARY KEY CLUSTERED
(
[NodeID] ASC
)
) ON [PRIMARY]
GO
January 24, 2020 at 1:36 pm
Looks good to me ... that should work (assuming you have no values for NodeId which are less than -9999 ... which you should confirm).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 24, 2020 at 2:38 pm
Thanks Phil !! Both the columns NodeID & QuarterID are having records starting from 1. Hence it won't be a issue.
January 27, 2020 at 1:39 pm
What if I have a column CreatedBy which is having datatype NVarchar(256) NULL instaed of QuarterID. I have changed my query like the one below. Will it be ok?
With ExpandedDataset as (
select NodeID, CreatedBy
from Nodes
union all
select NodeID = -9999, CreatedBy = NULL
)
select top (1) CreatedBy
from ExpandedDataset
order by NodeID desc;
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply