Knowledgebase

If you want to use PowerShell to deploy a datasource in SQL Server Reporting Services use the following script:

PowerShell script

"Set execution policy to [Unrestricted]"
Set-ExecutionPolicy Unrestricted

"Load assembly"
[System.Reflection.Assembly]::LoadFrom("C:\Temp\Ada.Cdf.dll")

$datasource = New-Object Ada.Cdf.Deployment.SSRS.DataSource
$datasource.SSRSWebServiceUrl = "http://localhost/ReportServer/ReportService2005.asmx"
$datasource.ImpersonateUser = $false
$datasource.WindowsIntegratedSecurity = $true
$datasource.UserAccountName = "MyDomain\saAsaWeb"
$datasource.UserAccountPassword = "MyPassword"
$datasource.ConnectionString = "Data Source=.;Initial Catalog=AdaSales;Integrated Security=SSPI;"
$datasource.FileSystemPath = "C:\Temp\Reports\AdaSales.rds"
$datasource.SSRSFolder = "ADA Sales Reports"
$datasource.Create()

C# code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Ada.Cdf.ReportService2005;

namespace Ada.Cdf.Deployment.SSRS
{
    public class DataSource
    {
        public bool ImpersonateUser { get; set; }
        public bool WindowsIntegratedSecurity { get; set; }
        public string UserAccountName { get; set; }
        public string UserAccountPassword { get; set; }
        public string ConnectionString { get; set; }
        public string FileSystemPath { get; set; }
        public string SSRSWebServiceUrl { get; set; }
        public string _ssrsFolder = string.Empty;
        public string SSRSFolder
        {
            get
            {
                // Use "/" as default value
                string firstCharacter = "/";
                if (string.IsNullOrEmpty(_ssrsFolder))
                {
                    _ssrsFolder = firstCharacter;
                }

                // Report folder should start with one "/"
                _ssrsFolder = firstCharacter + _ssrsFolder.TrimStart(firstCharacter.ToCharArray());

                return _ssrsFolder;
            }
            set
            {
                _ssrsFolder = value;
            }
        }

        /// <summary>
        /// Create a DataSource
        /// </summary>
        public void Create()
        {

            // Validate properties
            if (string.IsNullOrEmpty(this.FileSystemPath)) { throw new ArgumentNullException("this.FileSystemPath"); }
            if (!File.Exists(this.FileSystemPath)) { throw new ApplicationException(string.Format("The file [{0}] does not exist", this.FileSystemPath)); }
            if (string.IsNullOrEmpty(this.SSRSWebServiceUrl)) { throw new ArgumentNullException("this.SSRSWebServiceUrl"); }
            if (string.IsNullOrEmpty(this.SSRSFolder)) { throw new ArgumentNullException("this.SSRSFolder"); }
            if (string.IsNullOrEmpty(this.UserAccountName)) { throw new ArgumentNullException("this.UserAccountName"); }
            if (string.IsNullOrEmpty(this.UserAccountPassword)) { throw new ArgumentNullException("this.UserAccountPassword"); }

            // Initialize webservice proxy
            ReportService2005.ReportingService2005 rs = new ReportService2005.ReportingService2005();
            rs.Url = this.SSRSWebServiceUrl;
            rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

            // Determine filename without extension (used as name in SSRS)
            FileInfo fileInfo = new FileInfo(this.FileSystemPath);
            string fileNameWithoutExtension = Path.GetFileNameWithoutExtension(fileInfo.FullName);    

            // Create DataSourceDefinition
            DataSourceDefinition definition = new DataSourceDefinition();
            definition.CredentialRetrieval = CredentialRetrievalEnum.Store;
            definition.ConnectString = this.ConnectionString;
            definition.UserName = this.UserAccountName;
            definition.Password = this.UserAccountPassword;
            definition.Enabled = true;
            definition.EnabledSpecified = true;
            definition.Extension = "SQL";
            definition.ImpersonateUser = this.ImpersonateUser;
            definition.ImpersonateUserSpecified = true;

            // Use the default prompt string
            definition.Prompt = null;
            definition.WindowsCredentials = this.WindowsIntegratedSecurity;

            // Create datasource
            rs.CreateDataSource(fileNameWithoutExtension, this.SSRSFolder, true, definition, null);

        }
        /// <summary>
        /// Delete a DataSource
        /// </summary>
        public void Delete()
        {
            // Validate properties
            if (string.IsNullOrEmpty(this.FileSystemPath)) { throw new ArgumentNullException("this.FileSystemPath"); }
            if (string.IsNullOrEmpty(this.SSRSWebServiceUrl)) { throw new ArgumentNullException("this.SSRSWebServiceUrl"); }
            if (string.IsNullOrEmpty(this.SSRSFolder)) { throw new ArgumentNullException("this.SSRSFolder"); }

            // Initialize webservice proxy
            ReportService2005.ReportingService2005 rs = new ReportService2005.ReportingService2005();
            rs.Url = this.SSRSWebServiceUrl;
            rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

            // Determine filename without extension (used as name in SSRS)
            FileInfo fileInfo = new FileInfo(this.FileSystemPath);
            string fileNameWithoutExtension = Path.GetFileNameWithoutExtension(fileInfo.FullName);

            // Determine full SSRS path
            string ssrsPath = string.Format("{0}/{1}", this.SSRSFolder, fileNameWithoutExtension.TrimStart("/".ToCharArray()));

            // Delete datasource
            rs.DeleteItem(ssrsPath);

        }
    }
}

Result