Parameter value is empty in execute sql task

Question

Hi all, I created a package to dynamically create a create table script. I have a list of tables. The package gets the table name from a configuration table. Then it loops through. Inside the for each loop, execute sql task is used to execute a stored proc.
These are the settings of execute sql task. Result set is set to single row. SQL command is shown below. When I run the sp, it produces only one row and one column.

EXEC	 [dbo].[usp_GetCreateTableScript]
		?  OUTPUT,  ?



GO

There are two parameters. One is output and the other one is input.

However, when I run this query, I get the following error. [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow". When I set the result set to none, it works fine. However, then the CreateTableQuery parameter is empty. When I debug the SSIS package, I can not see any value for CreateTable Query parameter. Can anyone tell me how to fix this please? This is the stored procedure which I used in execute sql task. When I run this in management studio, It runs fine. It creates a create table script. But It does not work in SSIS.

- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usp_GetCreateTableScript]
	-- Add the parameters for the stored procedure here
	@Result varchar(8000) OUTPUT,
	@SourceTable varchar(8000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE
      @object_name SYSNAME
    , @object_id INT
    , @SQL VARCHAR(8000)
	-----, @SourceTable varchar(8000)= '[dbo].[ConfigTable]'

SELECT
      @object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'
    , @object_id = [object_id]
FROM (SELECT [object_id] = OBJECT_ID(''+@SourceTable+'', 'U')) o

SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
    SELECT CHAR(13) + '    , [' + c.name + '] ' +
        CASE WHEN c.is_computed = 1
            THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)
            ELSE
                CASE WHEN c.system_type_id != c.user_type_id
                    THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']'
                    ELSE '[' + UPPER(tp.name) + ']'
                END  +
                CASE
                    WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')
                        THEN '(' + CASE WHEN c.max_length = -1
                                        THEN 'MAX'
                                        ELSE CAST(c.max_length AS VARCHAR(5))
                                    END + ')'
                    WHEN tp.name IN ('nvarchar', 'nchar')
                        THEN '(' + CASE WHEN c.max_length = -1
                                        THEN 'MAX'
                                        ELSE CAST(c.max_length / 2 AS VARCHAR(5))
                                    END + ')'
                    WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')
                        THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
                    WHEN tp.name = 'decimal'
                        THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
                    ELSE ''
                END +
                CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id
                    THEN ' COLLATE ' + c.collation_name
                    ELSE ''
                END +
                CASE WHEN c.is_nullable = 1
                    THEN ' NULL'
                    ELSE ' NOT NULL'
                END +
                CASE WHEN c.default_object_id != 0
                    THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +
                         ' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)
                    ELSE ''
                END +
                CASE WHEN cc.[object_id] IS NOT NULL
                    THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]
                    ELSE ''
                END +
                CASE WHEN c.is_identity = 1
                    THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' +
                                    CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')'
                    ELSE ''
                END
        END
    FROM sys.columns c WITH(NOLOCK)
    JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id
    LEFT JOIN sys.check_constraints cc WITH(NOLOCK)
         ON c.[object_id] = cc.parent_object_id
        AND cc.parent_column_id = c.column_id
    WHERE c.[object_id] = @object_id
    ORDER BY c.column_id
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, '      ') +
    ISNULL((SELECT '
    , CONSTRAINT [' + i.name + '] PRIMARY KEY ' +
    CASE WHEN i.index_id = 1
        THEN 'CLUSTERED'
        ELSE 'NONCLUSTERED'
    END +' (' + (
    SELECT STUFF(CAST((
        SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' +
                CASE WHEN ic.is_descending_key = 1
                    THEN ' DESC'
                    ELSE ''
                END
        FROM sys.index_columns ic WITH(NOLOCK)
        WHERE i.[object_id] = ic.[object_id]
            AND i.index_id = ic.index_id
        FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'
    FROM sys.indexes i WITH(NOLOCK)
    WHERE i.[object_id] = @object_id
        AND i.is_primary_key = 1), '') + CHAR(13) + ');'

SET @Result =  @SQL
PRINT @Result

END

Thanks

Answer 1

Hi @Nishan Ratna ,

Please have a double check that I could get the value from the excute sql task.

I set the resultset to none and set like shown below.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.