send records as weblinks through send mail task in SSIS?

Question

Hi All,

i have a table with two columns and two records as follows.

TypeOfReport Links

InvalidRecords http://ReportInvalid

MissingRecords http://ReportMissing

---

I have a package with execute sql task selecting * from the table above, full result set selected and an object variable created, it works good. but now when i connect it with send mail task, i wanna send email like

Subject : Reports On InvalidRecords and MissingRecords

MessageText : Links for the Reports : http://ReportInvalid

: http://ReportMissing

Can somebone help me with it.

NOTE : i tried creating user and object variable but its good if i have only one record in table. i tried creating 2 user and 2 object variables as well but didnt work.

Thanks

Answer 1

Your Execute SQL Task is returning multiple rows and you're right to have the results go to a variable of object type. Now, it can be a challenging getting values from multiple rows of data into variable values in SSIS, but it's not impossible. However, for your situation, you may want to alter your SQL statement to return one row.

Start by creating two variables, one for the invalid-records link and one for the missing-records link. Edit your Execute SQL Task and replace your SQL statement with the following:

SELECT MAX(CASE TypeOfReport WHEN 'InvalidRecords'
               THEN links ELSE NULL END) AS InvalidRecords,
       MAX(CASE TypeOfReport WHEN 'MissingRecords'
               THEN links ELSE NULL END) AS MissingRecords
FROM mytable

This query will return one row with two columns, one for each link. This is known as a pivot (you could use the PIVOT clause to produce the same results.) The big advantage for you is that there's one row.

Also, change the ResultSet property to Single row.

Select the Result Set tab and add two items to the list. The first row should have 0 for Result Name and your InvalidRecords variable for Variable Name. The second row should have 1 for the Result Name and your MissingRecords variable for Variable Name. That should get your link values into variables and ready for your email task.