SSIS package not parsing statement with exec (SP) with resultset

  • I have a package that loops through a bunch of SQL Servers  and executes a stored procedure that uses temp tables.

    It was build in 2008 and I am now moving it to 2016 but having issues getting it to work

    in 2008 the OLE DB sql was simply  "uspSystem_GetDatabaseInfo"

    That was giving me grief in 2016 and after some research someone suggested to use  the below syntax.

    EXEC ('[dbo].[uspSystem_GetDatabaseInfo]')

    WITH RESULT SETS

    (

    (

    ServerName NVARCHAR(25) NOT NULL,

    dbName NVARCHAR(25) NOT NULL,

    dbStatus VARCHAR(25) NOT NULL,

    CompatibilityLevel INT NOT NULL,

    dbSizeMB INT NOT NULL,

    SpaceUsedMB INT NOT NULL,

    LogSizeMB DECIMAL(18,2) NULL,

    LogSpaceUsedPerentage DECIMAL(18,2) NULL,

    LastBackup DATETIME NULL,

    SampleDateTime DATETIME NULL

    )

    )

    This works fine in my newer databases but does not work in 2 legacy databases 2008.

    I have tried every combination I think of for settings but it works only for my newer databases..the moment the loop hits a 2008 database it does not like it

     

  • I think that you might have to split things up, such that one of the two different versions of the T-SQL is executed, depending on SQL Server version.

    If you're not sure how to do that, feel free to post back.

    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.

  • I have had situations where I simply couldn't make a execute using WITH RESULT SETS work.

    I solved the issue by changing the sp to specify a dummy table variable with the same format as the resultset that the sp returned at the start of the sp code. Apparently that makes it possible for the executing component to fetch the correct layout. Go figure!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply