Hi All, I have a requirement in which whenever a record is inserted into the destination table, a mail should be generated. To send the mail we don't have email addresses in the database. Instead we just have the ID(integer values) corresponding to each user. Now we want that using those IDs we should be able to pull the email addresses through Web service OR Active Directory. Is this possible? If yes, can you please help me achieve that. Regards, Mayank Jain

In SSIS there is a possibility to use webservices. Check this example how to do it: http://www.codeproject.com/Articles/24999/Calling-a-Web-Service-from-a-SQL-Integration-Servi You should pass the ID as parameter and get the email. Then use it to execute query to update the records in the database.

Can you please explain it a little more.. I couldn't understand the example. When i am doing what is mentioned in the example it throws me some error and i am not able to find the ".wsdl" file in my project solution of webservice

For the WSDL file of the web service. After you create a HTTp Connection, specify an file for the .wsdl (you have to write it by hand and not use browse [...], ex: C:\myservice.wsdl). Then click on the Download WSDL button.

I'm afraid that example isn't going to help much since it is not set up to work on a record by record basis. You would need to use a script transform in your dataflow to call the webservice once per record. Take a look at this thread for some sample code http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/8f24dd3b-5b1e-4d80-93bd-284f6ad1737a Chuck Pedretti | Magenic North Region | magenic.com

Hey Chuck, I am not able to create the wrapper class from command prompt. Please help. Actually i have never worked on this webservie and active directory before. :( :(

Are you getting an error or you just can't find wsdl.exe on your machine?Chuck Pedretti | Magenic North Region | magenic.com