Converting Stored Proc to Inline Query
Hello,

I have a stored proc that is being executed from an OLE DB Source component in my Data Flow. Takes one input parm, has several variables declared within, a derived table which is used in a join and all within a try catch block with transaction handling. No updates, just returning data, works great, except now I have been asked to replace these stored procs with inline queries.
ALTER PROCEDURE [dbo].[usp_Get_Test]
(
	@numberOfMonths				int
)
AS
BEGIN
	SET NOCOUNT ON				
	set transaction isolation level read uncommitted	
	
declare @TESTY table (TEST_ID char(12), TEST_SEQ_NO smallint, TEST_ID_ADJ_FROM char(12), TEST_IND char(1))

declare @BeginDate datetime
        ,@EndDate datetime
        ,@TestKey bigint
        ,@TestNumber varchar(255)
        
select   @BeginDate = dateadd(MONTH, @numberOfMonths, GETDATE())	
select   @EndDate = GETDATE()

BEGIN TRANSACTION

BEGIN TRY

insert into @TESTY
        ( TEST_ID ,
          TEST_SEQ_NO ,
          TEST_ID_ADJ_FROM ,
          TEST_IND
        )
select  tl.TEST_ID
,       cl.TEST_SEQ_NO
,       tl.TEST_ID_ADJ_FROM
,       case
            when cl.TEST_ID is null then 'N'
            else 'Y'
        end as TEST_IND
from    TESTL tl								
join    TESTCL cl on tl.TEST_ID = cl.TEST_ID	
where   tl.TEST_DT > @BeginDate
        and tl.TEST_DT < @EndDate 	                             		
			
select	td.TestNumber as [TEST_ID]				

from    TestDetail td

left 
join    @TESTY TY on td.TESTDataSource = 'TESTY' and td.TestNumber = TY.TEST_ID
        and TY.TEST_SEQ_NO = td.TestLineNumber

where   td.TestMonth > @BeginDate
        and td.TestMonth < @EndDate    
        and td.TESTStatus in('tested','retested')			

END TRY 
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
END CATCH	

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION
END
The problems I have run into so far are...

SQL command text in OLE DB Source Editor does not like:
- TRY/CATCH block
- Will not let me use my input parm (@numberOfMonths int)
- When I hard code in my input parm (select   @BeginDate = dateadd(MONTH, -1, GETDATE())) I can parse query and run the step but no results are returned. So I am let to assume that it does not like the @TESTY derived table.

The query here as a sample has had pivots removed as well, but research suggests this should be an issue in the SQL command text.

Also, I know not even to try the Build Query... cause it will complain about any variable 
declarations (i.e., declare @BeginDate datetime).

So, if someone could confirm some things for me I would really appreciate it. For instance, can I pull this off with an Execute SQL Task? The problem is I don't see this available in the toolbox for the Data Flow. 

Also, would my error handling be done in the Event Handlers tab now and if so, is there a good example of this?

Thanks,
May 29th, 2015 8:19pm

UPDATE: 

I created a duplicate .dtsx for testing the inline query theory. I got all of the steps working with inline queries (variable declarations, error/transaction handling), the errors bubbling up to the SSIS layer (for instance, I had to suppress a null aggregate warning that bubbles up):

http://stackoverflow.com/questions/13157508/why-does-a-null-aggregate-warning-in-an-sql-query-causes-the-ssis-package-to-fail
(Suppressed with SET ANSI_WARNINGS OFF;)

The only thing I dont have working is the input parm, I had to hard code this for now:

declare   @numberOfMonths int
select     @numberOfMonths = -2

declare   @BeginDate datetime
             ,@EndDate datetime
             ,@ClaimHeaderKey bigint
             ,@ClaimNumber varchar(255)

select     @BeginDate = dateadd(MONTH, @numberOfMonths, GETDATE())
select     @EndDate = GETDATE()

I have to admit, these queries run fairly quick for 60-days worth of data in SSIS (Data Flow / OLE DB Source).

Any suggestions or corrections are welcome. Am I making an assumption that ALL errors will bubble to the surface? I assume if a warning bubbles up then certainly an actual error will bubble up.

Thanks,

Buster

Free Windows Admin Tool Kit Click here and download it now
May 29th, 2015 9:56pm

Hi Buster,

If you dont want to hard code the input parameter, we can use ? to replace the parameter value in the query, then map it to an Int32 data type variable in the package. And then we can control the variable value to change the input parameter value.

The following SQL Command is for your reference:

Reference:
Map Query Parameters to Variables in a Data Flow Component

If there are any other questions, please feel free to ask.

Thanks,
Katherine Xiong

June 1st, 2015 11:18pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics