locked
Need help!!!! (SP or SSIS Task) RRS feed

  • Question

  • I have written the Dynamic TSQL S-Proc. Below is what i wanted to implement in SSIS using foreach loop container as a cursor. But i am little doubtful whether I can achieve the dynamics to this level. I know everything is possible but is it advisable to go for this simple Sproc or SSIS tasks.

     

    I have some 15 tables being populated using this SPROC.

     

    Here is some helpful description

    ENTITYNAME gives me the table i need to work

    FIELDNAME gives me the field i have to work on

    CHANGEDVALUE gives me the value changed in that field

    ( This three  i get from source table which is about 9000 rows and containing 15 possible ENTITY to be work on and 100's of their respective FIELD )

     

    while in Cursors i need to get using these above variables other variables like

    FLAG

    KeyName

     

    Thrugh SQL1 I get the KeyValue

     

    then using this KeyValue check if the data exist update else insert new data.

     

    QUESTION: IS THIS ADVISABLE to go for SSIS task or just carry with SPROC?

     

    /*******************************************************************************************************/

     

     

     

    DECLARE Table_Cursor CURSOR
     FOR SELECT ENTITYNAME,FIELDNAME, KEYID, CHANGEDVALUE, UPDATEUSER, UPDATEDATE 
        FROM dbo.ChangedDimensionStage

    OPEN Table_cursor


    FETCH NEXT FROM Table_cursor INTO @ENTITY, @FIELD, @KEYID, @VALUE, @USER, @DATE


    WHILE @@FETCH_STATUS = 0

     

    BEGIN

    DECLARE @FLAG NVARCHAR(50);
    SET @FLAG = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'LastUpdateFlag';

    DECLARE @KeyName NVARCHAR(50);
    SET @KeyName = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'


    DECLARE @KeyValue NVARCHAR(50)

    DECLARE @SQL1 NVARCHAR (1000)

    SET @SQL1 = N'Select @KeyValueOUT  = '+ @KeyName + ' FROM DW_Integration.dbo.MangFact WHERE ClaKey = ' + @KEYID +  ' GROUP BY ' + @KeyName + ' HAVING SUM(TotalClaCount) > 0 OR SUM(IncidentOnlyClaCount) > 0 '

    EXECUTE sp_executesql @SQL1, N'@KeyValueOUT INT OUTPUT', @KeyValue OUTPUT;


    DECLARE @WC_TABLE NVARCHAR(100)
    SET @WC_TABLE = 'WorkingCopy' + @ENTITY

    DECLARE @SQL2  nvarchar (1000);
    SET @SQL2 = 'IF EXISTS (SELECT  '+ @KeyName +'  FROM  ' + @WC_TABLE + '  WHERE  ' + @KeyName + ' = ' + @KeyValue + ' )' +
    '  BEGIN  UPDATE  ' + @WC_TABLE + '  SET  '+  @FIELD + ' = '''+ @VALUE + '''  WHERE  ' + @KeyName + ' = ' + @KeyValue +';    END' +
    '  ELSE   BEGIN
    INSERT INTO '+  @WC_TABLE + '  SELECT  *  FROM   DW_Integration.dbo.' + @ENTITY +  '  WHERE  ' +  @Flag + ' = ' + '''Y''' + ' AND  '+ @KeyName + ' = ' + @KeyValue + ';'  +
     'UPDATE  ' + @WC_TABLE + '  SET  '+  @FIELD + ' = '''+ @VALUE + '''  WHERE  ' + @KeyName + ' = ' + @KeyValue +';    END'

    EXECUTE sp_executesql @SQL2


    FETCH NEXT FROM Table_cursor INTO @ENTITY, @FIELD, @KEYID, @VALUE, @USER, @DATE

    END


    CLOSE Table_cursor
    DEALLOCATE Table_cursor

    Thursday, September 6, 2007 9:23 PM

Answers

  • This is not a striaghtforward question, and there is no straightforward answer either.

     

    The stored proc you have provided is dreadfully inefficient, and could be done much more efficiently. But that may not be an issue with the volumes of data you are working with.

     

    An SSIS package is another approach, but do you need to re-develop this work? What is the justification for doing so? Do you need to be able to easily update the procedure? Perhaps using external configuration? In this case, an SSIS reimplementation may be justified. Do you need to hand it over to someone else to maintain? Then again, SSIS may be useful, especially if the maintainers have little experience with T-SQL.

     

    Nobody here is going to say to you to do it one way or another way. I would ask the person who is paying for the job what it is they are after.

     

    Friday, September 7, 2007 12:37 AM