July 9, 2014 at 2:12 am
Hi All,
I am new to SSIS, I have a 39 tables in source and same tables in destination, The source and destination both are in SQL Server 2008 databases. So i have taken one sequence container in side container i have set to 39 data flow tasks. In data flow tasks i have put OLE DB Source and OLE DB destination, Based on created data daily i have moved records from source to destination.
Now on this package i am ready to deploy the job so once package run successfully or failure case and how many records inserted and updated in destination tables, those information i need it on a mail. The information should be in table format like given below.
Table Name Compare ColumnSource DB CountDestination DB CountUpdation Status
abcd CreatedDt -- -- No data difference
abcd_info CreatedDt -- -- No data difference
com_info UpdatedDt -- -- Moved sucessfully
daily_stats CreatedDt 1936 1936 Moved sucessfully
abc_data CreatedDt 3872 3872 Moved sucessfully
abcd_history CreatedDt -- -- Error
Thanks & Regards,
Babu
July 9, 2014 at 2:49 am
You'll need to use Rowcount transformations in each dataflow to store the count of extracted/inserted rows in a number of user-defined variables. You can then reference those variables in your Send Mail task.
As you are new to SSIS, I recommend taking a look at Andy Leonard's 'Stairway to Integration Services' series of articles on this very website: http://www.sqlservercentral.com/stairway/72494/
Regards
Lempster
July 9, 2014 at 4:31 am
Hi Lempster,
Rowcount transformations i can used between source and destination. where can i mention user-defined variables and where can i refer those variables in Send Mail task. Please let me know with screen shots if it passably. I am SQL DBA admin i don't know much in SSIS.
Thanks,
Babu
July 9, 2014 at 5:05 am
annam.dba (7/9/2014)
Hi Lempster,Rowcount transformations i can used between source and destination. where can i mention user-defined variables and where can i refer those variables in Send Mail task. Please let me know with screen shots if it passably. I am SQL DBA admin i don't know much in SSIS.
Thanks,
Babu
When you use a Rowcount transformation, you have to store the result (i.e. the row count) in a user-defined variable so set up a variable first (you may need to go to the View menu in BIDS and slect 'Variables' in order to be able to see/add variables) and then reference that variable when you use your Rowcount transformation.
To use the same variable in a Send Mail task, you need to set the contents of the email message using an Expression.
Sorry, I don't have the time to provide a detailed explanation with screenshots - have a look at the link I provided in my earlier post.
Regards
Lempster
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply