Executing procedure in SSIS Visual Studio with result sets

Question

I am running one of the scripts which uses procedure to fetch some columns. Data keeps changing sometimes there are Location_code column values 1, 2 and 3, sometimes 4.

emp_id	Home_location	Location_1	Location_2	Location_3	Location_4
Cell 1	Cell 2
Cell 3	Cell 4

Based on the positions, if a employee has two positions, Location_3 and Location_4 will be blank. If someone has 4 positions for that emp_id, it would go up to 4 columns for others null. It changes as it uses MaxTally function parameter. N*N and partitions @CRLF and Row number.

Coming back to the main thing when I run this script in SQL command ole db. Source editor as:

EXEC ('exec Procedure name;')
WITH RESULT SETS
    ((EMP_ID VARCHAR (20),
      HOME_LOCATION VARCHAR (20),
      location_1 VARCHAR (10),
      location_2 VARCHAR (10),
      location_3 VARCHAR (10),
      location_4 VARCHAR (10)
    ));

The problem is sometimes location column is only until location_3 no location_4 column, in that case I get error.

I removed Location code 4 parsed the query, and it was good.

I am expecting if there is a way like Count +1 here in Ole Db Source editor to determine automatically, so I don't need to add or remove parameter to solve error.

Answer 1

If the procedure returns varying amounts of columns, and if it can not be altered, and if no input can direct it's behaviour, then only a trial-and-error approach may work, I believe. It seems a sequence of queries within try-and-catch-blocks could get you there. I assume you work in an environment where you can execute code like the following.

DECLARE @success_f BIT = 0
DECLARE @r TABLE(story NVARCHAR(4000))

DROP TABLE IF EXISTS #t
CREATE TABLE #t(
  EMP_ID VARCHAR (20),
  HOME_LOCATION VARCHAR (20),
  lOCtion_1 VARCHAR (10),
  location_2 VARCHAR (10),
  location_3 VARCHAR (10),
  location_4 VARCHAR (10),
  location_5 VARCHAR (10)
)


IF @success_f = 0
BEGIN TRY
BEGIN TRAN  
  INSERT INTO #t(
    EMP_ID
    ,HOME_LOCATION
    ,lOCtion_1
    ,location_2
    ,location_3
    )
  EXEC ('EXEC Procedure name;')  
  SET @success_f = 1
  insert into @r select 'first succeeded'
COMMIT TRAN
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0 ROLLBACK TRAN
  insert into @r select 'first failed'
END CATCH
else insert into @r select 'first not even tried'


IF @success_f = 0
BEGIN TRY
BEGIN TRAN  
  INSERT INTO #t(
    EMP_ID
    ,HOME_LOCATION
    ,lOCtion_1
    ,location_2
    ,location_3
    ,location_4
    )
  EXEC ('EXEC Procedure name;')  
  SET @success_f = 1
  insert into @r select 'second succeeded'
COMMIT TRAN
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0 ROLLBACK TRAN
  insert into @r select 'second failed'
END CATCH
else insert into @r select 'second not even tried'


IF @success_f = 0
BEGIN TRY
BEGIN TRAN  
  INSERT INTO #t(
    EMP_ID
    ,HOME_LOCATION
    ,lOCtion_1
    ,location_2
    ,location_3
    ,location_4
    ,location_5
    )
  EXEC ('EXEC Procedure name;')  
  SET @success_f = 1
  insert into @r select 'third succeeded'
COMMIT TRAN
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0 ROLLBACK TRAN
  insert into @r select 'third failed'
END CATCH
else insert into @r select 'third not even tried'


SELECT "@success_f after" = IIF( @success_f = 1
                                 ,'procedure run without error'
                                 ,'procedure NOT run without error' )

SELECT story FROM @r

SELECT 
  EMP_ID
  ,HOME_LOCATION
  ,lOCtion_1
  ,location_2
  ,location_3
  ,location_4
  ,location_5
FROM #t

There are three IF-blocks, each trying a specific number of columns. The first one tries up to location_3, the second up to location_4, the third up to location_5. If there a are other sets of columns that may be returned you need to create an IF-block for them. This code will try each one and if one of them succeeds it will load the data into temp table #t. It also collects some info on what happend.

At the end it returns all columns of #t with the final select, leaving unused columns filled with NULL.