Conversion of stored procedure to ssis package

harry88
harry88 used Ask the Experts™
on
I need to convert below stored procedure to SSIS package.I am kind of new to SSIS. can anyone help me with package. need help. If any one can get me started with package, like where to start and how to approach. or else similar examples or if can do a ssis package for below code.


INSERT INTO simplifiedproductiontracking
            (ordernumber,
             serialnumber,
             catalognumber,
             workcenter,
             status,
             startdate,
             finishdate,
             employees,
             stationid,
             finishshiftid,
             timeelapsedseconds,
             idstart,
             idend)
(SELECT sn.orderid,
        a.serialnumber,
        sa.catalog_number,
        --S.WorkCenter,
        smwc.workcenter,
        b.status,
        a.dateregistered,
        b.dateregistered,
        a.employee + Coalesce('', '' + Nullif(b.employee, a.employee), '''') AS employees,
        a.stationid,
        b.shiftid,
        Datediff(ss, a.dateregistered, b.dateregistered)  AS actualtime,
        a.producttrackid,
        b.producttrackid
FROM   productiontracking AS a WITH (nolock)
        INNER JOIN productiontracking AS b WITH (nolock)
          ON a.serialnumber = b.serialnumber
             AND a.stationid = b.stationid
        INNER JOIN serialnumbers sn WITH (nolock)
          ON a.serialnumber = sn.serialnumber
        INNER JOIN saporders sa WITH (nolock)
          ON sn.orderid = sa.order_number
        INNER JOIN station s WITH (nolock)
          ON a.stationid = s.stationid
        INNER JOIN idealcycletimes ict WITH (nolock)
          ON ict.order_number = sn.orderid
        INNER JOIN sapmultipleworkcenters smwc WITH (nolock)
          ON smwc.stationid = s.stationid
             AND smwc.workcenter = ict.work_center
 WHERE  a.simplified IS NULL
        AND b.simplified IS NULL
        AND ( a.status = 1 AND b.status = 3 )
         OR ( a.status = 4 AND b.status = 6 )
         OR ( a.status = 8 AND b.status = 7 ))

UPDATE productiontracking
SET    simplified = 1
WHERE  simplified IS NULL
       AND EXISTS(SELECT idend
                  FROM   simplifiedproductiontracking
                  WHERE  productiontracking.producttrackid =simplifiedproductiontracking.idend AND
                          productiontracking.serialnumber =  simplifiedproductiontracking.serialnumber)

UPDATE productiontracking
SET    simplified = 1
WHERE  simplified IS NULL
       AND EXISTS(SELECT idstart
                  FROM   simplifiedproductiontracking
                  WHERE  productiontracking.producttrackid = simplifiedproductiontracking.idstart AND
                          productiontracking.serialnumber =simplifiedproductiontracking.serialnumber)  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Do you know how to get started at all or are you needing step by step instructions starting with, "Open the Business INtelligence Development Studio (aka BIDS)."

Essentially, You can accomplish this with 3 Execute SQL Tasks. ;-)

Author

Commented:
I need to do this using SSIS Components...NOT by using SQL EXECUTE TASK. Can any one let me know other ways around...!
Dude,

There is an SSIS component called EXECUTE SQL TASK.  I was NOT telling you to execute 3 SQl statements but, rather to put 3 SSIS Execute SQL Task components in your project.

Have you ever evenlooked at an SSIS package in BIDS?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I mean to say...I don't want to get tis done by using EXECUTE SQL TASK component of SSIS. BUT rather use SSIS components like Merge join,merge and look up to make joins between the tables. May be i did not explain clearly what i wanted. And i know BIDS upto some extent though not extensively. Let me know if this can be done in any other ways rather than using Execute Sql Task component of SSIS.
Top Expert 2012

Commented:
Is there any reason other than SSIS is cool?  I mean what is wrong with what you are currently doing?  Are you trying to make it run slower and be more difficult to maintain?
Consultant, Trainer
Commented:
I am agree with Acperkins,
as your source and destination is ms sql server, this is highly recommended to do data transfer with t-sql scripts.
Maybe you want to do it in ssis for better readability or support, but as I see in your stored procedure this is simple to understand and doesn't need this.

by the way , if you are insist to do it with SSIS:
you can run whole sp in an execute sql task ( but this is not SSIS way ! )
or you should use SSIS way:
use data flow with source and destination to do Insert part of your script
then use two separate execute sql task for updates.

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial