Dynamic SQL in SSIS Data Flow task

The Data Flow Source in the Data Flow tab extracts the metadata from the source to populate the column information. In the case of a stored procedure that uses dynamic SQL within, this can pose a problem in getting the metadata. Depending on the situation here is one way to get around the issue. Below is sample stored procedure that works with the pubs database. Comment the mentioned block in the stored procedure to see the difference in the SSIS package.

With the block commented, the Data Flow Source does not have any columns to extract and map. With the dummy block in place the Data Flow Source extract the required metadata to display columns. Note that the code block will not execute at runtime but it allows you to call a stored procedure with dynamic SQL in it from the Data Flow source in a SSIS package.


    CREATE PROCEDURE [dbo].[GetAuthorsByState]
        -- Add the parameters for the stored procedure here
        @States varchar(10)
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        DECLARE @strSQL nvarchar(1000)

        -- Start of the block to enable the Data Flow source to work
        -- Comment this block to see the effect on the Data Flow source in the SSIS package
        IF 1 =2
        BEGIN
            SELECT [au_id]
                ,[au_lname]
                ,[au_fname]
                ,[phone]
                ,[address]
                ,[city]
                ,[state]
                ,[zip]
                ,[contract]
                ,[DateAdded]
            FROM [dbo].[tblAuthors]
        END
        -- End of the block to enable the Data Flow source to work

        SET @strSQL = '
            SELECT [au_id]
                  ,[au_lname]
                  ,[au_fname]
                  ,[phone]
                  ,[address]
                  ,[city]
                  ,[state]
                  ,[zip]
                  ,[contract]
                  ,[DateAdded]
              FROM [dbo].[tblAuthors]
            where state = ''' + @States + ''''

        EXEC dbo.sp_executesql @strSQL

    END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s