Tag Archives: Report Server

Abonnements pilotés par les données (ou Data Driven Subscriptions)

Bonjour

Voici un article sur les Abonnements pilotés par les données (ou Data Driven Subscriptions) dans la suite SQL Server 2008.

Utilisation du Report Server

Les abonnements pilotés par les données permettent le passage de paramètre dans la planification de diffusion de rapport Reporting Services.

Une fois que vous avez réalisé votre rapport sous Reporting Services, vous pouvez le déployer sur le portail Report Server. Le rapport est alors accessible sur votre navigateur à l’url de votre portail. Si le rapport est paramétré, lors de son exécution, vous pourrez choisir ou taper une valeur correspondant à votre paramètre. Le rapport s’affiche en tenant compte de votre choix. C’est l’utilisation classique du Report Server.

Les abonnements

Vous pouvez utiliser le Report Server d’une manière plus avancée pour répondre à la demande de votre client ou votre employeur : « Je voudrais recevoir automatiquement le rapport X par mail quotidiennement »

Sur le portail, lorsque vous exécutez un rapport, vous avez 4 onglets :

  • Affichage
  • Propriétés
  • Historique
  • Abonnements

C’est l’onglet Abonnements qui va nous intéresser. Il contient la liste des abonnements qui sont définit sur un rapport et offre la possibilité d’en ajouter.

Par défaut, en cliquant sur Nouvel abonnement, vous devriez pouvoir planifier une remise des rapports par Partage Windows. Pour planifier une remise de rapports par mail, vous devez configurer les paramètres d’envoi de mail dans le Gestionnaire de configuration de Reporting Services.

Attention, vous ne pourrez pas utiliser un serveur SMTP nécessitant l’authentification nativement. (voir l’article Envoi de mail avec un serveur SMTP non local)

Les abonnements pilotés par les données

Dans la version enterprise de SQL Server 2008 (et visiblement 2005), lors de la conception de votre abonnement, vous avez la possibilité de définir la valeur d’un parametre à partir d’une requête SQL. Ainsi vous pourrez automatiser l’envoi d’un rapport à une adresse email retournée par la requete.

Dans la version standard, la fonction n’est pas prévue :

Les abonnements pilotés par les données ne sont pas pris en charge dans l’ édition Standard ni dans SQL Server Express Edition with Advanced Services.” FAQ Microsoft (http://msdn.microsoft.com/fr-fr/library/ms159150.aspx)

Si vous ne pouvez pas migrer, il faut trouver un solution de contournement.

La solution consiste à modifier les tables internes de ReportServer pour ajouter manuellement les valeurs des paramètres.
La procédure stockée qui permet de le faire a été diffusée par Jason Selburg sur www.codeproject.com puis améliorée par Greg Low sur le sqlblog.com.

Pour utiliser la procédure, il faut :

– Sur le Report Server, compléter les champs dans l’abonnement comme ci-dessous.

To Address: |TO|
Carbon Copy Address: |CC|
Blind Copy Address: |BC|
Reply To Address: |RT|
Comment / Body: |BD|

Source : http://www.codeproject.com/KB/database/DataDrivenSubscriptions.aspx

– Avant de valider l’abonnement, ouvrir un SQL Server Management Studio >> Agent SQL Server >> Travaux

– Valider l’abonnement sur le Report Server (Cette manipulation permet d’identifier l’abonnement créé)

– Localiser l’identifiant de l’abonnement (code alphanumérique peu explicite) (c’est celui qui apparait dans Travaux)

– Ensuite, il faut placer la procédure stockée suivante dans la liste des procédures stockées de la base ReportServer.

Source : http://sqlblog.com/blogs/greg_low/archive/2008/08/13/data-driven-subscriptions-in-sql-server-2005-standard-edition.aspx

– Enfin, dans une nouvelle requête, compléter les paramètres pour un premier test :

EXEC dbo.ExecuteSubscribedReport

@ScheduleID = ‘4CE38C83-6A03-4780-895A-92FD6F8FD5B0’, (identifiant de l’abonnement récupéré dans Travaux)

@EmailTo = ‘to@mail.com‘, (mettez votre mail pour le recevoir)

@EmailCC = ‘cc@mail.com‘,

@EmailBCC = ‘bcc@mail.com‘,

@EmailReplyTo = ‘rt@mail.com‘,

@EmailBody = ‘Salut to’,

@ParameterList = ‘ ‘;

Le plus difficile est fait. Vous pouvez intégrer cet appel dans une procédure stockée pour automatiser un mailing pour vos clients :

Boucle avec un curseur
– On récupere les adresses email et le code du client avec un select
– On execute la procédure ExecuteSubscribedReport avec l’email pour destinataire et le code du client pour la ParameterList.

N’hésitez pas à m’indiquer en commentaire si certaines parties ne sont pas assez détaillées ou illustrées 😉

A bientôt,

Guillaume

Envoyer des mails avec un relais SMTP / Send Mail using a SMTP Relay

Bonjour,

Une des “lacunes” (nous verrons que c’est une contrainte judicieuse) de SQL Server depuis plusieurs versions est l’impossibilité d’envoyer simplement des mails par un serveur SMTP qui requiert une authentification (login/mdp). Cette lacune est problématique pour l’utilisation des abonnements dans le Report Server ou pour l’utilisation de la tache Send Mail dans SSIS.

La demande d’ajout de la fonctionnalité a été faite sur le site de Microsoft (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=332979) et pour le moment, 2 actions de contournements sont proposées.

Voici un descriptif de celle qui consiste à mettre en place un Relais SMTP sur le serveur. La mise en place se fait en 2 étapes, installation puis configuration.

Installation

L’installation est assez simple. Elle consiste à ajouter le service SMTP Server à partir du Server Manager.
Automatiquement, l’installation d’autres services et fonctionnalités vous sera proposée.

Install SMTP

Install SMTP

Voici le récapitulatif des éléments juste avant l’installation.

Install SMTP 2

Install SMTP 2

Configuration

Une fois que tout est bien installé. il faut configurer votre serveur SMTP en tant que relais. Dans notre cas, nous allons relayer les mails vers le SMTP Gmail. L’applicatif interne enverra donc un mail sans authentification à notre relais et ce dernier le renverra via le SMTP Gmail à son destinataire.

1. Aller dans Internet Information Services IIS 6.0 Manager dans les outils d’administration

2. Aller dans les propriétés de votre SMTP Virtual Server.

Config SMTP Virtual  Server

Config SMTP Virtual Server

3. Dans l’onglet General, Aller dans Advanced puis ajouter le port d’ecoute local (25 par défaut).

Config SMTP Virtual  Server Advanced

Config SMTP Virtual Server Advanced

4. Dans l’onglet Access, Aller dans Authentification et choisissez Anonymous access.

Config Authentification

Config Authentification

5. Dans l’onglet Delivery, Aller dans Outbound Security et renseigner vos identifiants gmail valide. Valider TLS encryption.

Config Outbound

Config Outbound

6. Dans Outbound Connections, Paramétrer le port du serveur SMTP distant.

Config Outbound Connections

Config Outbound Connections

7. Dans Advanced, Parametrer le Smart host avec smtp.gmail.com et Renseigner le FQDN de votre machine.

Config Advanced Delivery

Config Advanced Delivery

8. Valider avec OK puis Cliquer sur Domains

9. Ajouter un domaine distant.

10. Accepter que les mails entrant soient relayés vers ce domaine et Forwarder tous les mails vers le smart host.

Config Domain

Config Domain

11. Renseigner de nouveau l’authentification au SMTP Gmail.

Config Domain Outbound Security

Config Domain Outbound Security

12. Valider tout.

Pour tester :

13. Lancer SSIS et ajouter un composant Send Mail.

14. Paramétrer le SMTP a utiliser avec le FQDN.

SMTP Connection Manager Editor SSIS

SMTP Connection Manager Editor SSIS

15. Renseigner le composant Send Mail et Exécuter la tache.

Attention :

Une fois que votre relais est fonctionnel, il est très important d’en limiter l’usage. Pour cela, vous devez paramétrer les Controles de connexion et les Restrictions de relais dans l’onglet Accès.

Cette solution simplifie l’administration en cas de changement du serveur SMTP. En effet, il suffit de modifier le serveur SMTP dans le IIS Manager pour que la modification soit prise en compte pour tous les outils utilisant le relais. Cette obligation d’usage d’un relais pour envoyer des mails authentifiés me parait donc justifiée pour la pérennité du SID.

Sources :

http://www.itsolutionskb.com/2008/11/installing-and-configuring-windows-server-2008-smtp-server/