Clicky

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)  

asked 05/16/2011 06:20

harry88's gravatar image

harry88 ♦♦


7 Answers:
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. ;-)

link

answered

8080_Diver's gravatar image

8080_Diver

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

answered 2011-05-16 at 14:45:19

harry88's gravatar image

harry88

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?
link

answered 2011-05-16 at 15:52:36

8080_Diver's gravatar image

8080_Diver

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.
link

answered 2011-05-16 at 15:55:59

harry88's gravatar image

harry88

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?
link

answered 2011-05-16 at 16:19:37

acperkins's gravatar image

acperkins

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.

link

answered 2011-05-16 at 21:06:24

reza_rad's gravatar image

reza_rad

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

link

answered 2011-05-16 at 21:47:36

PedroCGD's gravatar image

PedroCGD

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×2
×1
×1
×25
×1
×16

Asked: 05/16/2011 06:20

Seen: 327 times

Last updated: 12/14/2011 05:15