SSIS Help Urgent !!!
Hi , I am trying to automate a manual process which we do every month by using SSIS. During this process we are running SQL Query on prod and Dev environments and compare the results and if the difference is over 100 then we want to fail the package and send a email notification. Step1: Sample Query : Select Sum(col1), Sum(col2), Sum(Col3),Sum(Col4),Sum(Col5),Sum(Col6) from Xtable (we run this query on prod n dev) then Step2: paste the results in excel sheets and find the difference., it looks somthing like this: HEADING Period : date here Dev server name Prod server name Difference Col1 0.00 Col2 0.00 Col3 0.00 Col4 0.00 Col5 0.00 Col6 0.00 If the difference value is >= 100 the package should fail and send email notification If the difference value is <=100 then I want the above data as it is in Excel (i want it exactly the same way it looks like above) Is that even possible in SSIS ., please help me its an urgent task and I want to finish it by weekend.Please do answer me in a briefly manner since I am new to SSIS. Thank you in Advance.
December 5th, 2011 6:05pm

Hi, Why do you need SSIS for this? You may run the query on the Database Engine and use Database mail to send email in case of your predefined conditions. I hope it helps. J.There are 10 type of people. Those who understand binary and those who do not. My Blog
Free Windows Admin Tool Kit Click here and download it now
December 5th, 2011 7:16pm

Its the client requirement and also they want the data in the excel sheet if the difference is Less than 100. I have mentioned the format in which the file supposed to look like.
December 5th, 2011 7:55pm

I would agree that it would be more optimal to use a SQL Agent job if this is the only evaulation you are doing, however, if this is part of a larger SSIS package that is performing other work as well you would use Expressions in the Constraints to create a conditional statement Constraint Expression 1 : @value > 100 - send email and stop Constraint Expression 2 : @value <= 100 - go to next stepDWM
Free Windows Admin Tool Kit Click here and download it now
December 5th, 2011 7:57pm

Well as far as now I have the following steps done : - Created a DFT - have two OLDEBD sources linked to prod n devand has the query in it - since I will have only one row of result for the query , i took two row sampling transformations - used union all to combine the results to gether - and I have created derrived columns which calculate the dofference of the values at this point I have 15 columns and I am not sure how to put a condition on the derrived column which calculates the difference so that I can fail the packg. And then if the difference is <100 then I want to load the data into spread sheet in the given format.
December 5th, 2011 8:04pm

well I have another set of query for which I have to do the same stuff
Free Windows Admin Tool Kit Click here and download it now
December 5th, 2011 8:07pm

Use below kind of query in both the source oledb tasks so that you can join the two (via Merge Join transformation) on that constant column (which would have similar constant values of col1, col2, col3, etc in both sources) and take the difference between the corresponding totals. Using the query in this way would help in generating the data into excel file as different rows [similar to what you indicated above]. SELECT 'Col1' AS Col1, SUM(Col1) AS Total FROM XTable UNION ALL SELECT 'Col2' AS Col2, SUM(Col2) FROM XTable UNION ALL SELECT 'Col3' AS Col3, SUM(Col3) FROM XTable UNION ALL SELECT 'Col4' AS Col4, SUM(Col4) FROM XTable UNION ALL SELECT 'Col5' AS Col5, SUM(Col5) FROM XTable UNION ALL SELECT 'Col6' AS Col6, SUM(Col6) FROM XTable After joining both sources via common constant and taking differences among corresponding totals , you could use ConditionalSplit to divert the rows having <= 100 and >100 differences. Hope this helps. Thanks!
December 5th, 2011 10:30pm

Hi Deepak, I have done everything as you said ., so far i got until finding the diff b/w cols and i have conditional split with two cases ., can you please help me with setting up the script component for failing the package based on the case of my condtnl split (diff >=100) and also send an email on failure. And also I am trying to load the data into Excel sheet and send it as a email attachment for case 2 (diff<100), can you say how to do that also please. Thanks in advance
Free Windows Admin Tool Kit Click here and download it now
December 7th, 2011 9:02am

Hi, See if the below steps work: 1. Create a flag variable (with package scope) and set some default value to it. 2. Add a script component task on the path of "diff >= 100" and change the value of flag variable to something else. 3. In control flow, connect send mail task to the data flow task with precedence constraint as "expression" and set the expression such that the flag variable is compared against the value set by script component task in step 2. This way the send mail would only execute if the value of the flag variable equals to it. 4. However, you might still get excel generated irrespective of difference you had between the values and you might think of attaching this excel while sending the email so that the actual difference (>100) would be known. I have not practically done this, but see if this idea works. Thanks!
December 8th, 2011 4:14pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics