SQL 2008 - Create Batch XML and fire email with SSIS


  • Hello, 

    I'm trying to do the following:

    from a very simple SQL select (I need only two fields: Datetime and HRCode ) that returns a large number of records 

    I need to:

    - extract set of 10 records.
    - with this 10 records, output an XML fixed format :

          <CCcodcomunicazione1 />
          <DTdatafine1 />
          <CCcodcomunicazione2 />
          <CCcodcomunicazione3 />
          <DTdatafine3 />
          <CCcodcomunicazione4 />
          <CCcodcomunicazione5 />
          <DTdatafine5 />
          <CFlavoratore6 />
          <CCcodcomunicazione6 />
          <DTdatainizio6 />
          <DTdatafine6 />
          <CFlavoratore7 />
          <CCcodcomunicazione7 />
          <DTdatainizio7 />
          <DTdatafine7 />
          <CFlavoratore8 />
          <CCcodcomunicazione8 />
          <DTdatainizio8 />
          <DTdatafine8 />
          <CFlavoratore9 />
          <CCcodcomunicazione9 />
          <DTdatainizio9 />
          <DTdatafine9 />
          <CFlavoratore10 />
          <CCcodcomunicazione10 />
          <DTdatainizio10 />
          <DTdatafine10 />

    as you can see, the xml is fixed and offers a subset of fields that repeats itself 10 times(adding the number in each node).
    I need to take every 10 records from my table, "fill in the blanks" in this xml structure, create an xml file and fire it up as an attachment by email.

    After some research, I think the better way to do all of this is using SSIS; but I've no idea how break and create an xml every ten records, keeping in mind that every node has a number.

    Any advice?

    Thanks in advance for any suggestion


    • Moved by Kalman Toth Tuesday, July 16, 2013 8:46 PM SSIS task
    Tuesday, July 09, 2013 2:08 PM


  • You'll be doing most of this in an SSIS For Loop; you may want to ask about the specifics there.

    Some tips: To know how many times you'll need to loop, copy the record count of the source table to a variable, then take the Ceiling of that divided by 10.

    If you're more comfortable doing that as a SQL task it would be:

    Select Ceiling(<rowcountvar>/10.0)

    Map the result set to your total number of iterations; 102 records will yield 11 iterations, for example.

    Now you can loop through with:

    Select Top 10 from <tble> where RowNo >= <iteration>

    Where <iteration> is the current iteration - 1 through the total number of iterations, incrementing by 10 after each loop

    Map the results to an XML file and a send message task. The For Loop will continue until the current iteration is greater than the total number of iterations.

    Do note that you may have to copy your data to a temporary table that has an identity field to stand in for RowNo or you can create it by including

    row_number() over( order by Datetime )  Row
    in your select statement

    Good Luck,

    Tim Mills-Groninger

    Tuesday, July 09, 2013 4:40 PM