locked
How to create an SSIS package with a Script Component RRS feed

  • Question

  • I am using Microsoft Business Intelligence Development Studio (BIDS) to create an SSIS package. Take into account that is my first attempt with SSIS.

    I want to take data from a Source database elaborate them and put in a Destination database. In the elaboration task I need also data from the Destination database.

    In the Data Flow I have two "objects" a Source and a Destination. In the Source I created a query that takes data from the Source database.

    Then I added a Script Component between the two.

    The problems that I encounter are two:

        1) How can I call from my script the
        result
    of the query?

       
    2) How can I also get the data from
        the destination
    (it allows me to
        connect up
    to 1 arrow to the script)

       
    3) How can I write the elaborated data in the destination?

    I hope you can help me, because I am quite lost.

    Tuesday, July 5, 2011 8:19 AM

Answers

  • This also can be done by Lookup Task, Derived Column etc..

    create a Lookup between the source and the Destination.

    The Source in Lookup ist a join between Person-table and Department-table from Destination DB.

    direct the green arrow (person found)  to a Union all, direct the red (Person not found) to a derived Column and do what ever you have to, if the person is not found

    direct the green arrow of the Derived Column to the Union All.

    place a Aggregate Task after the Union All and agregate the data as you need

    direct the green arrow of the Aggregate Task to your Destination

    That's the way, I would do this requirement

    • Marked as answer by Trinakriae Wednesday, July 6, 2011 4:13 PM
    Tuesday, July 5, 2011 11:15 AM

All replies

  • You have to declare the input data and the output data for the component you need for your elobaration.
    The data from destination you need also as Source, join them together with your original source.

    How will you elaborate the data? Maybe it's not nessasary to use a Component. Mostly the inbuild tasks achieve the goal.

    Tuesday, July 5, 2011 9:09 AM
  • Dear Christa,

     

    thanks for your answer. I just discovered that instead of the Script Component, the Script Task mght do the job. However, the Script Task is a Control Flow component whilst the Data Source and Data Destination are Data Flow components. How can I link those?

    The elaboration of the data in short:

     

    1) I select with a query (SELECT and SUM) two columns (Name, Quantity) from Source Database.

    2) For each row I check whether the value of Name exists in the table Person (PersonId, Name, SupervisorID) of the Destination Database.

    3) If it does I need to make a join between the table Person and Department (DeptId, Name, SupervisorID) of the Destination Database on SupervisorID (in order to check to which department the employee belongs) and store the DeptId.

    After all the rows have been processed I have a table/view with DeptId (from Department) and Quantity (from Source Database).

    4) Groupby the table/view for DeptId and SUM the Quantity.

    5) Add every row of the table/view in an existing table of the Destination Database

    The sequence of tasks is quite trivial but I do not have any clue on how structure them in SSIS. If you are wondering why I use SSIS:

    1) This sequence has to be automatically executed once a month

    2) I cannot create a Console Application and add it to the task scheduler because I was explicitly asked not to do so

     

    I hope you can give me some hint (I am not asking you to solve my problem). I have been playing around with BIDS for quite a while but the tool has too many resources to try all the combinations! Thanks

     

    Tuesday, July 5, 2011 10:13 AM
  • This also can be done by Lookup Task, Derived Column etc..

    create a Lookup between the source and the Destination.

    The Source in Lookup ist a join between Person-table and Department-table from Destination DB.

    direct the green arrow (person found)  to a Union all, direct the red (Person not found) to a derived Column and do what ever you have to, if the person is not found

    direct the green arrow of the Derived Column to the Union All.

    place a Aggregate Task after the Union All and agregate the data as you need

    direct the green arrow of the Aggregate Task to your Destination

    That's the way, I would do this requirement

    • Marked as answer by Trinakriae Wednesday, July 6, 2011 4:13 PM
    Tuesday, July 5, 2011 11:15 AM
  • Thank you very much! You really solved my problem. I didn't know all this tasks and I was doing them personalized through script.

    One last question. Suppose that in all the rows to insert in the destination table there is a column which has the same value...how can I input that value accroding to your proposed solution? Thanks

    Tuesday, July 5, 2011 2:52 PM
  • Sorry, I didn't follow.

    Where is the same value?

    Tuesday, July 5, 2011 5:53 PM
  • Ok sorry I didn't express myself very clear. Let's say that in the Destination Table I have one column called "Details" and for automated task executed by a task scheduler this field has to contain the value 16.

    That means for every row generated by the SSIS package this field must contain 16. How can fulfill that? Thanks

    Wednesday, July 6, 2011 7:49 AM
  • You can fill a variable with this value and add it in a Derived Column to your Dataflow.

    Or add the value in the source with ea select statement:

    select ..., 16 as Detail from MyTable

     

    I suspect it is more difficult to determine when the relevant values ​​are set as how they come to the destination.
    Wednesday, July 6, 2011 7:17 PM