Deploy SSIS Package in SQL Server 2012

Deploy SSIS Package in SQL Server 2012.

SQL Server 2012 added new feature of Integration Service Catalog, which makes SSIS package deployment task very easy.

To get advantage of this feature you must have sql server integration service installed on your server.

Follow the steps to deploy package.

Step1 : Create Integration Service catalog.

DeploySSISPackage1

Step2 : Enable CLR Integration and provide encryption password.

DeploySSISPackage2

Step3 : You will find SSISDB catalog will created. Creating catalog automatically creates database “SSISDB” to your instance.

DeploySSISPackage3

Step4 : Now go to your package right click on your project and select “Deploy”.

DeploySSISPackage4

Step5 : Selection will opens deployment window click on “next”.

DeploySSISPackage5

Step6 : Provide destination server detail to server name.

DeploySSISPackage6

Step7 :  Click on “Browse” to select Path:

DeploySSISPackage7

Create New folder with your desired name.

DeploySSISPackage8

Step8 : Click on “Next”.

DeploySSISPackage9

Step9 : Check the summary and click on “Deploy”.

DeploySSISPackage10

Step10 :  Click on “Close”.

DeploySSISPackage11

Step11 : Check your deployed package under Integration service in SSMS.

DeploySSISPackage12

Step12 : To Schedule this package create new job with name “Deployment Testing”.

DeploySSISPackage13

–> Select “SQL Server Integration Services Package’ in Type.

–> Select “SSIS Catalog” in Package Source and provide server connection details.

DeploySSISPackage14

–> Select your package path.

DeploySSISPackage15

–> Schedule the package as your desired time.

DeploySSISPackage16

 

Regards,

Nirav Gajjar

SSIS Package to generate MailProfile script

SSIS Package to generate MailProfile script.

There is no provision to generate script for mail profile in sql server. but i have one script that can be used to create mail profile, mail account and map them together.

I am using this script whenever i needed it, letter i have an idea to start scripting automatically of mail profile and i have created one package that genrates all three scripts and store it in one sql file. this is very helpfull for disaster recovery planning for script backup.

Step1 : To create package you need one Data Flow Task, One Flat File Connection, One OLEDB Connection, and one variable
Please see in figure.

BackupMailProfileScript 1

Step2 : Drag one OLE DB Source and one Flat File Destination. Right click on Data Flow Task –> Edit

BackupMailProfileScript 2

Step3 : Right click on OLEDB Source Editor — > Edit

Select your local machine connection.

Select SQL Command in Data Access mode.

Paste Below Code.


Select '--Create Account' AS CMD
Union All
SELECT 'EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ''' + m.name + ''',
@description = ''' + m.description + ''',
@email_address = ''' + m.email_address + ''',
@replyto_address = ''' + m.replyto_address + ''',
@display_name = ''' + m.display_name + ''',
@mailserver_name = ''' + s.servername + ''',
@mailserver_type = ''' + s.servertype + ''',
@port = ''' + cast(s.port as nvarchar) + ''',
@username = ''' + isnull(c.credential_identity,0) + ''',
@password = ''x'',
@use_default_credentials = 0,
@enable_ssl = 0' AS CMD
From msdb.dbo.sysmail_account m
LEFT OUTER JOIN msdb.dbo.sysmail_server s
ON m.account_id = s.account_id
LEFT OUTER JOIN master.sys.credentials c
ON s.credential_id = c.credential_id
Union All
Select '--Create Profile' AS CMD
Union All
SELECT '
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ''' + name + ''',
@description = ''' + isnull(description,'NULL')+ '''
' AS CMD
from msdb.dbo.sysmail_profile
Union All
Select '--Create Link For Profile to Account' AS CMD
Union All
Select '
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = '''+ a.name +''',
@account_name = ''' + b.name + ''',
@sequence_number = 1 ' as CMD
from msdb.dbo.sysmail_profile as a
join msdb.dbo.sysmail_account as b on a.name = b.name

BackupMailProfileScript 3

Step4 : Click on Columns left side on OLE DB Source editor, configure column and click ok.

BackupMailProfileScript 4

Step5 : Right Click on Flat File Destination and select “Primary_Location” to flat file connection manager.

BackupMailProfileScript 5

Click on Mappings to map variable.

BackupMailProfileScript 6

Step6 : Now to make folder dynamic – select Primary_location from connection manager and hit F4.

BackupMailProfileScript 7

Click on Expression button.

BackupMailProfileScript 8
Copy below code to Expression.

@[User::FolderName]  + "<a href="file://mailprofile/">\\MailProfile</a>_" +  (DT_WSTR,4)YEAR(GETDATE())
+ RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2)
+ RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + ".sql"

Click OK and execute the package it will creates script to “D:\ScriptBackup\MailProfile\MailProfile_YYYYMMDD.Sql” this file contains three different bunch of script. Fist to create Mail Account Second to create Mail Profile. Third to map Mail Account to Mail Profile.

Regards,

Nirav Gajjar

SSIS Package to get Table Schema Discrepancy using XML

SSIS Package to get Table Schema Discrepancy using XML.

I have created package which uses XML in Execute SQL Task and compares two table’s schema discrepancy.

You can use this script to compare table in different instances with different database or in same instance with difference database or in same instance with same database.

You can save your result in database table in any text file, here i have messaged the result.

I have done this stuff in different instances with different database.

Please flow the step to do so.

Step1 : Create one sample table with one column difference on two difference instnace.

Table script for source connection

CREATE TABLE [dbo].[DatabaseInfo]

(
    [ServerName] [varchar](20) NULL,
    [DatabaseName] [varchar](20) NULL,
    [LogicalName] [varchar](20) NULL,
    [PhysicalName] [varchar](200) NULL,
    [DBSize] [int] NULL)

Table script for destination connection


CREATE TABLE [dbo].[DatabaseInfo]

(
    [ServerName] [varchar](20) NULL,
    [DatabaseName] [varchar](20) NULL,
    [LogicalName] [varchar](20) NULL,
    [DBSize] [int] NULL
)

here, i have deleted column PhysicalName to get an idea.

Step2: Now drag two Execute SQL Task, One Script Task, two OLEDB Connection and two variables.

TableSchemaDifference 1

Variables

Obj_Result –> DataType Object

XMLData –> String

Step3 : Right click on Source Execute SQL Task –> Edit.

–> Select ResultSet –> XML

–>SQL Statement


SELECT
 CLMList.Table_Name,
 CLMList.COLUMN_NAME
FROM Information_Schema.Columns CLMList
Where CLMList.Table_Name = 'DatabaseInfo'
For XML Path('SyncTables'),root('Root')

TableSchemaDifference 2

Step4 : Click on ResultSet –> Edit.

–> Select XMLData variable.

TableSchemaDifference 3

Click OK

Step5 : Right click on Source Execute SQL Task –> Edit.

–> Select ResultSet –> Full Result Set

–>SQL Statement


Declare @TableName xml
set @TableName = ?

SELECT
    SyncTables.TableName,
    SyncTables.ColumnName
FROM Information_Schema.Columns CLMList
    right join (SELECT p.value('(./Table_Name)[1]', 'VARCHAR(8000)') as TableName,
    p.value('(./COLUMN_NAME)[1]', 'VARCHAR(8000)') as ColumnName
FROM @TableName.nodes('//Root/SyncTables') t(p)) as SyncTables
    on CLMList.Table_Name = SyncTables.TableName
    and CLMList.Column_Name = SyncTables.ColumnName
 Where CLMList.Column_Name is null

TableSchemaDifference 4

Step6 : Click on Parameter Mapping  of Execute SQL Task Editor.

Select User::XMLData as input variable.

TableSchemaDifference 5

Step7 : Click on Result Set of Execute SQL Task Editor.

Select User::Obj_Result in Variable Name.

TableSchemaDifference 6

Step8 : Now Right click on Script Task –> Edit.

Select User::Obj_Result as ReadOnlyVariables.

TableSchemaDifference 7

Click on Edit Script and past below code.

</pre>
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
#endregion

namespace ST_e81be99c144345b2bb202cbd5460cb01
{
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {
  public void Main()
  {
   // TODO: Add your code here
            try
            {
                OleDbDataAdapter OA = new OleDbDataAdapter();
                DataTable DT = new DataTable();
                DataRow DR;

               
                OA.Fill(DT, Dts.Variables["User::Obj_Result"].Value);
                foreach (DataRow row in DT.Rows)
                {
                    DR = row;
                    string TableName, ColumnName;
                    TableName = DR["TableName"].ToString();
                    ColumnName = DR["ColumnName"].ToString();

                    MessageBox.Show("Table Name : " + TableName.ToString() + " Column Name : " + ColumnName.ToString());
                }

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message.ToString());
            }
   Dts.TaskResult = (int)ScriptResults.Success;
  }

        #region ScriptResults declaration
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 }
}

Now run the package and it will show you column difference in popup message.

Regards,

Nirav Gajjar

SSIS Package to Call Web Service

SSIS Package to Call Web Service.

You can Call WebService from SSIS package and transfers your data.

First of all you have to create web service with function as  you needed to call.

Step1 : Go To Microsoft Visual Studio –> New –> WebSite –> ASP.Net WebService and provide you web service name.

WebService 1

Step2 : Paste below code to your Service1.asmx.vb file.

I have created new function “MyHelloWorld” with parameter name in which i am passing a string to write in a destination file.


Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="<a href="http://tempuri.org/">http://tempuri.org/</a>")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class Service1
    Inherits System.Web.Services.WebService
    <WebMethod()> _
    Public Function MyHelloWorld(ByVal name) As String
        Dim FILE_NAME As String = "E:\WebServiceTextFromService.txt"
        Dim objWriter As New System.IO.StreamWriter(FILE_NAME, True)

        objWriter.Write(name)
        objWriter.Close()
        Return "Process Completed."
    End Function

End Class

Step3 : Now Create New SSIS Package with your desired name.

Drag Weservice Task to Control Flow.

Create one variable “Result” with datatype string and value “I am writting this content to file by web service task”.

WebService 2

Step4 : Create one http connection manager. i am using 61508 port number for my local web service so my Server URL : http://localhost:61508.

This port number is assigned by default, you can change port number from web service property.

WebService 4

Step5 : Create one file connection to write output result to local path.

WebService 8

Step6 :  Right click on Web Service Task –> Edit.

On General Tab Select HTTP Connection Manger which you have created on Step4.

Select your WSDL file path to WSDLFile field.

WebService 5

Step7 : Select Input path of Web Service Task Editor.

Select your service name in Service and Method “MyHelloWorld” which you have created on Step2.

After selecting Method, Parameter field enables automatically.

Click on Variable checkbox and select User::Result in value .

WebService 6

Step8 : Select OutPut tab on left pane on Web Service Task Editor.

Select Flat File Connection “WebServiceText.txt” in File Field.

WebService 7

Step9 : After completing this build and run the project and you will get file will be created on “D:\WebServiceText.txt”

with the content “I am writting this content to file by web service task”.

 

Regards,

Nirav Gajjar

SSIS Package with Dynamic ADO Connection

SSIS Package with Dynamic ADO Connection.

I have created a package that collects database information from different instances all over the network and stored in one central database table.

For this, I have collected Server Name, Database Name, Logical File Name, Physical File Name and File Size in MB.

I have stored all instance info in my central database table.

Table Script.

Create Table SQLServerInstances
(

    ServerName Varchar(20),
    DBName Varchar(20),

    UserName Varchar(20),

    PWD Varchar(20)
)
Insert Into SQLServerInstances Values('yourLocalMachineName','AdventureWorks2012','sa','password')

 I had taken one Execute SQL Task to get list of instances then move it to Foreach loop and store the values to database table using Data Flow Task.

Step1 : Drag Execute SQL Task, Foreach Loop Container and Data Flow task under Foreach Loop Container.

DynamicConnection 1

Step2 : Create Variables.

–> Obj_Result : DataType Object.

–> ServerName : DataType String.

–> DBName : DataType String.

–> UserName1 : DataType String.

–> PWD : DataType String.

DynamicConnection 2

Step3 : Right click on Execute SQL Task –> Edit and fill the details as shown in below figure.

DynamicConnection 3

Step4 : Select Result Set on left pane of Execute SQL Task and select your object variable.

DynamicConnection 4

Step5 : Right Click on Foreach Loop Container –> Edit.

Select Foreach ADO Enumerator in Enumerator and Select Obj_Result in ADO Object source varialbe field.

DynamicConnection 5

Step6 : Select Variable Mapping on left pane of Foreach Loop Container and select varialbes as you see in below figure.

DynamicConnection 6

Step7 : Now Right click on Data Flow Task –> Edit.

DynamicConnection 7

Step8 : Right click on OLE DB source. create one OLEDB data connection with name Dynamic Connection with your local database connection.

SQL Command.


SELECT @@ServerName as ServerName,
   DB_NAME(database_id) AS DatabaseName,
   Name AS LogicalName ,
   Physical_Name as PhysicalName,
   (size*8)/1024 DBSize
FROM sys.master_files

DynamicConnection 8

Step9 : Select Columns from left pane of OLE DB Source Editor and confirm Columns.

DynamicConnection 9

Step10 : Right Click on Data Conversion Task –> Edit and set data types for columns.

DynamicConnection 10

Step11 : Create one database table to store all servers’s database info in central database.


Create Table DatabaseInfo
(
    ServerName Varchar(20),
    DBName varchar(20),
    LogicalName Varchar(200),
    PhysicalName Varchar(200),
    DBSize int
)

DynamicConnection 11

Step12 : Right click on mapping on left pane of OLE DB Destination Editor and map the varialbes to table columns.

DynamicConnection 12

Step13 : Now right click on “DynamicConnection” that you have created before to make it dynamic.

Rikght click on it and click on Property.

Select Expression field button that will open one popup screen.

DynamicConnection 13

Map DynamicConnection’s parameter with varialbes to make it dynamic and run the project.

Regards,

Nirav gajjar

SSIS Package Foreach Loop with ADO Connection

SSIS Package Foreach Loop with ADO Connection.

In my last blog i had written SSIS Package Foreach Loop with File Connection(Link: http://nirav.extreme-advice.com/2013/02/25/ssis-package-foreeach-loop-with-file-connection/).

This package gives you same thing, variable mapping between Foreach Loop Container and any inner task contains in loop (here inner task is Script Task) but here i have used ADO Connection as enumerator in behalf of File Connection.

Step1 : Drag

–> Execute SQL Task to get list of values from database table and import to object variable

–> Foreach Loop Container.

–> Script Task.

ForEachLoopWithADO1

Step2 : Create Variables.

–> Obj_Result – Data Type Object.

–>ServerName – Data Type String.

–> DataSource – Data Type String.

ForEachLoopWithADO2

Step3 : Right Click on Execute SQL Task –> Edit.

–> Result Set – Full Result Text.

–> Connection – Your local database connection.

–> SQL Statement – Select Name,Data_Source From Sys.Servers.

ForEachLoopWithADO3

Step4 : Select Result Set from your left pane of Execute SQL Task Editor.

–> Result Name : 0 (Zero).

–> Variable Name : User::Obj_Result.

ForEachLoopWithADO4

Step5 : Right click on Foreach Loop Container –> Edit.

–> Select Foreach ADO Enumerator.

–> User::Obj_Result.

ForEachLoopWithADO5

Step6 : Select Variable mapping from your left pane of Foreach Loop Editor.

–> Select User::ServerName with Index 0.

–> Select User::DataSource with Index 1.

ForEachLoopWithADO6

Step7 : Rigth click on Script Tak –> Edit.

–> ReadOnlyvariables – Select Both Variable shown in below figure.

ForEachLoopWithADO7

Step8 : Click on Edit Script and paste below code.


#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion

namespace ST_f67917be72d748f4a19778c4b647dbf0
{
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {
  public void Main()
  {
            string ServerName, DataSource;
            ServerName = Dts.Variables["User::ServerName"].Value.ToString();
            DataSource = Dts.Variables["User::DataSource"].Value.ToString();
            MessageBox.Show("Server Name : " + ServerName + " Data Source : " + DataSource);
   Dts.TaskResult = (int)ScriptResults.Success;
  }

        #region ScriptResults declaration
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 }
}

This script will show you how to use variables and how to map variables between Execute SQL Task to Foreach Loop Container and Foreach Loop Container to Inner Script Task.

Regards,

Nirav Gajjar

SSIS Package Foreach Loop with File Connection

SSIS Package Foreach Loop with File Connection.

This package gives you an idea to get file details and variable mapping between Foreach Loop Container and any inner task contains in loop (here inner task is Script Task).

Step1 : Drag Foreach Loop and script task from SSIS Toolbox.

ForEachLoopWithFile1

Step2 : Create one variable with name “FileName” and datatype “string”.
this varialbe maps filename from Foreach Loop Container value to Script task.

Step3 : Right click on Foreach Loop Container –> Edit
On Collection pane –> select Foreach File Enumerator in Enumerator field.
choose your folder by clicking on browse button.
–> Select file extention if you want to filter file extention otherwise put *.* to get all files.
–> Select Name and Extention in Retrieve file name.
ForEachLoopWithFile2

Step4 : Select Variable mapping pane on Foreach Loop Editor.
–> Select User::FileName variable.
–> and Index to 0.
ForEachLoopWithFile3

Step5 : Right Click on Script Task –> Edit.
–> Select Script Pane –> Select User::FileName as ReadOnlyVarialbe.

Step6 : Click on Edit Scrit to write script code.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion

namespace ST_f1f3822404bc4e8d9a22fbe2749ff1d9
{
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{
		public void Main()
		{
            string FileName;
            FileName = Dts.Variables["User::FileName"].Value.ToString();
            MessageBox.Show(FileName);
			Dts.TaskResult = (int)ScriptResults.Success;
		}

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

	}
}

Regards,
Nirav gajjar

SSIS Package to get Disk Space Info with WMI and Script Task

SSIS Package to get Disk Space Info with WMI and Script Task.

In Previous blog i have created SSIS Package to get Disk Space Info with WMI.(Link: http://nirav.extreme-advice.com/2013/02/20/ssis-package-to-get-disk-space-info-with-wmi/)
That was a simple way to gather disk space information with importing data in CSV file and exporting data in database.
I have used same table in this task and i have used Script Task in behalf of CSV file.

Table Structure.

Use AdventureWorks2012
Go
Create Table ServerDiskInfo
(
Drive varchar(5),
DriveSize numeric(18,2),
FreeSpace numeric(18,2),
CollectionDate Datetime Not NUll Default GetDate()
)

Lets start to create SSIS Package to get Disk Space Info with WMI and Script Task.

Step1 : Open your SSIS package and drag WMI Data Reader Task from SSIS Toolbox.

DiskSpaceInfoWithWMIScript

Step2 : Create new WMI Connection shown as below.

DiskSpaceInfoWithWMIScript2

Step3 : Create variable Obj_Result with data type object to recieve result data.

DiskSpaceInfoWithWMIScript3

Step4 : Select User::Obj_Result in Destination.

DiskSpaceInfoWithWMIScript4 

Step5 : Right click on Script Task –> Edit.

Select User::Obj_Result as ReadOnlyVariables.

DiskSpaceInfoWithWMIScript5

Step5 : Click on Edit on Script Task Editor and paste below code.


public void Main()
 {
            try
            {
                DataTable DT;
                DataRow DR;
                SqlConnection SQLCon = new SqlConnection("Server=Nirav'sDiary;Database=AdventureWorks2012;User Id=sa;Password=pwd;");

                DT = (DataTable)Dts.Variables["User::Obj_Result"].Value;
                foreach (DataRow row in DT.Rows)
                {
                    DR = row;
                    string Name;
                    int Size, FreeSpace;
                    Name = DR["Name"].ToString();
                    Size = Convert.ToInt32(Convert.ToDouble(DR["Size"].ToString()) / 1024 / 1024 / 1024);
                    FreeSpace = Convert.ToInt32(Convert.ToDouble(DR["FreeSpace"].ToString()) /1024/ 1024 / 1024);
                    SqlCommand cmd = new SqlCommand("Insert Into ServerDiskInfo Values('"+ Name +"',"+ Size +","+ FreeSpace +",GetDate())", SQLCon);
                    SQLCon.Open();
                    cmd.ExecuteNonQuery();
                    SQLCon.Close();
                }

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message.ToString());
            }
 }

Step6 : Executing whole task it will make entry in ServerDiskinfo Table.

Query to get ServerDiskInfo.

Declare @Today as datetime
Det @Today = DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))
Delect Drive,Cast(DriveSize/1024/1024/1024 as Numeric(10,2)) as SizeInGB,Cast(FreeSpace/1024/1024/1024 as Numeric(10,2)) as FreeSpaceInGB from ServerDiskInfo
Where DATEADD(DD,0,DATEDIFF(DD,0,CollectionDate)) = @Today</pre>

Regards,
Nirav gajjar

SSIS Package to get Disk Space Info with WMI

SSIS Package to get Disk Space Info with WMI.

I have created package that gives Disk Space info using WMI Data Reader Task.
You need minor knowledge on WMI Query and WMI Class, here i have used “Win32_LogicalDisk” WMI class.
This package checks disk info for logical disk of your machine and stores value to the database.
To store information create one table on your database.


Use AdventureWorks2012
Go
Create Table ServerDiskInfo
(
Drive varchar(5),
DriveSize numeric(18,2),
FreeSpace numeric(18,2),
CollectionDate Datetime Not NUll Default GetDate()
)

Lets start to create SSIS package.
Step1 : Open your SSIS package and drag WMI Data Reader Task from SSIS Toolbox.
SSIS Package Disk Space With WMI 1

Step2 : Rigth click on Connection Manager pane –> Select New Connection –> Select WMI and click on ADD button.

SSIS Package Disk Space With WMI 2

SSIS Package Disk Space With WMI 3

Step3 : It will opens WMI Connection Manger Editor. you can give any name to connection name and discription.

If you are creating package for your local machine then

\\LocalHost to connect your local machine and check on Use Windows Authentication.

If you are creating package for remote server then

\\RemoteServerName to connect remote server and provide “UserName” and “Password” for User Credentials.

SSIS Package Disk Space With WMI 4

Click on Test button to confirm connetion established successfully.

Step4 : Right Click on WMI Data Reader Task –> Click on Edit –> WMI Options Pane.

Give your WMI Connection –> WMI_localmachine(WMI Connection Manager).

WQL Query Source Type –> Direct Input.

WQL Query Source –>Select Name, Size, FreeSpace From Win32_LogicalDisk Where DriveType = 3.

OUTPUT Type –>DataTable.

OverWrite Destination –> Overwrite Destination.

Destination Type –> File Connecion.

Destination –> Create New Connetion.

SSIS Package Disk Space With WMI 6

Click OK.

SSIS Package Disk Space With WMI 7 New

Click Ok.

Step5 : Execute task to generate C:\ServerDiskInfo.csv file.

Step6: Drag Data Flow task –> Right Click –> Edit.

Step7: Drag Flat File Source from Other Source Tool, Drag Data Conversion from Other Transforms, Drag OLEDB Destination from Other Destinations.

SSIS Package Disk Space With WMI 8 New

Step8 : Right Click on Flat File Source –> Edit

SSIS Package Disk Space With WMI 9

Click on Columns Tab to check the columns then click on OK.

Step9 : Right Click on Data Conversion –> Select FreeSpace and Size.

Give Numeric[DT_numeric] in Data Type to both columns.

SSIS Package Disk Space With WMI 10

Step10 : Right Click on OLE DB Connection –> Edit.

Select OLEDB connection manager. if there is no OLEDB Connection manager then create new connection manager for AdventureWorks2012 and Select ServerDiskInfo Table.

SSIS Package Disk Space With WMI 11

Step11: Select mappings from left pane.

SSIS Package Disk Space With WMI 12 new

map Input Columns with Destination Columns given in image.

Click OK.

Executing whole task it will make entry in ServerDiskinfo Table.

Query to get ServerDiskInfo.

declare @Today as datetime
set @Today = DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))
select Drive,Cast(DriveSize/1024/1024/1024 as Numeric(10,2)) as SizeInGB,Cast(FreeSpace/1024/1024/1024 as Numeric(10,2)) as FreeSpaceInGB from ServerDiskInfo
where DATEADD(DD,0,DATEDIFF(DD,0,CollectionDate)) = @Today

Regards,
Nirav Gajjar