Part 7: Logging the SSIS package events to a dynamically named csv file

Part 7: Logging the SSIS package events to a dynamically named csv file

  1. Right click on the surface area of control flow tab of SSIS package and select logging.ScreenShot027
  2. Select SSIS Log providers for text files:ScreenShot028
  3. On details table select even which needs to be logged:ScreenShot029
  4. Open the connection manager properties for the SSIS log providerScreenShot031
  5. Update connection string property to set every time during run timeScreenShot032
  6. with following script:
    "F:\\SFTP\\Logs\\"+  @[System::PackageName] + 
    (DT_STR,4,1252)DATEPART( "yyyy" , @[System::StartTime]   ) + 
    RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" ,  @[System::StartTime]  ), 2) + 
    RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime]   ), 2) + 
    RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime]   ), 2) + 
    RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" ,  @[System::StartTime]  ), 2) + 
    RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime]   ), 2) + 
    ".csv"

 
Save the package, now the SSIs package will save logs to text file for every execution.
 
See Also:
Part 1 : Automate SFTP file operation using SSIS
Part 2 : Setting up Free SFTP Server
Part 3 : Testing the SFTP Server from a client
Part 4 : Creating a SSIS Package to automate SFTP File operations
Part 5 : Scheduling the SSIS Package
Part 6 : Save SFTP incoming files info to SQL table
Part 7 : Logging SSIS Package

Cheers!
sANJAY

Part 6 :Using Foreach Loop Container task in SSIS to inventory SFTP folder

Part 6 :Using Foreach Loop Container task in SSIS to inventory SFTP folder

At previous post part 1 to part 5 the  process automation SFTP file operations was described. This post details how to extend the SSIS package to scan for the SFTP incoming folder, inventory all the incoming files and create entries at the SQL Table with in coming file info such as fully qualified file name, directory name, file name, file extension and file size.

  1. Create a table at SQL Database which will host the incoming file info
    USE [DatabaseName]
    GO
    
    /****** Object:  Table [dbo].[IncommingFiles]    Script Date: 07/21/2014 00:54:51 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[IncommingFiles](
    	[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    	[IncommingFileName] [nvarchar](500) NOT NULL,
    	[ImportDateTime] [smalldatetime] NOT NULL,
    	[IncommingDirectory] [nvarchar](500) NOT NULL,
    	[IncommingFullyQualifiedFileName] [nvarchar](500) NOT NULL,
    	[IncommingFileExtention] [nvarchar](500) NOT NULL,
    	[IncommingFileSize] [nvarchar](500) NOT NULL,
     CONSTRAINT [PK_IncommingFiles] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[IncommingFiles] ADD  CONSTRAINT [DF_IncommingFiles_ImportDateTime]  DEFAULT (getdate()) FOR [ImportDateTime]
    GO
  2. Add Foreach loop container to SSIS package
    ScreenShot021
  3. Add IncommingFile, IncommingDirectory, IncommingFileExtention, IncommingFullyQualified, InsertSQL, and IncommingFileSize SSIS variables to package @ SSIS > Variable
  4. Add Script Task and Execute SQL Task inside the For Each Loop container
  5. Configure the For each loop container as followsScreenShot022
    ScreenShot023
  6. Configure the Script task as follows:ScreenShot024
  7. Code the Script task as follows:
    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    
    <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
    <System.CLSCompliantAttribute(False)> _
    Partial Public Class ScriptMain
    	Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    
    	Enum ScriptResults
    		Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
    		Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    	End Enum
    	
        Public Sub Main()
            Dts.Variables("IncommingFile").Value = System.IO.Path.GetFileName(Dts.Variables("User::IncommingFullyQualifiedName").Value)
            Dts.Variables("IncommingDirectory").Value = System.IO.Path.GetDirectoryName(Dts.Variables("User::IncommingFullyQualifiedName").Value)
            Dts.Variables("IncommingFileExtention").Value = System.IO.Path.GetExtension(Dts.Variables("User::IncommingFullyQualifiedName").Value)
            Dts.Variables("IncommingFileSize").Value = FileLen(Dts.Variables("User::IncommingFullyQualifiedName").Value).ToString
            Dim sqlstr As String = "insert into IncommingFiles(IncommingFileSize,IncommingFileExtention,IncommingFullyQualifiedFileName,IncommingFilename,IncommingDirectory) values ('" & Dts.Variables("User::IncommingFileSize").Value & "','" & Dts.Variables("User::IncommingFileExtention").Value & "','" & Dts.Variables("User::IncommingFullyQualifiedName").Value & "','" & Dts.Variables("User::IncommingFile").Value & "','" & Dts.Variables("User::IncommingDirectory").Value & "')"
            Dts.Variables("InsertSQL").Value = sqlstr
            Dts.TaskResult = ScriptResults.Success
        End Sub
    
    End Class
  8. Configure the Execute SQL Task as followsScreenShot026
  9. Save the package. Executing the package should now save the file info for each incoming file at the SQL table.

See Also:
Part 1 : Automate SFTP file operation using SSIS
Part 2 : Setting up Free SFTP Server
Part 3 : Testing the SFTP Server from a client
Part 4 : Creating a SSIS Package to automate SFTP File operations
Part 5 : Scheduling the SSIS Package
Part 6 : Save SFTP incoming files info to SQL table
Part 7 : Logging SSIS Package

/

Cheers!
Sanjay

 

 

 

Manually host a wcf service on IIS

Manually host a wcf service on IIS:

Attempted to deploy the webservice created here to IIS.  The wcf deploy from visual studio through web deploy kept on complaining web management service is not running on webserver – figured out a manual deploy would be simple solution.

  1. Create empty website on IIS.
    1. Home directory -> C:\WCFService on webserver
  2. Open the WCF visual studio project.
    1. Build >> Publish
    2. Create a new profile as WCFService
    3. Publish Method = File System
    4. Target Location c:\wcfservice on Visual studio machine
    5. Publish
    6. The publish folder will contain :
      1. PreCompiledApp.config
      2. Service1.svc
      3. web.config
      4. bin/webservice.dll
      5. bin/webservice.xml
  3. Copy the contents of published WCF project to the root directory of website published
    1. Copy c:\wcfservice\*.* on visual studio machine
      to
      c:\wcfservice on webserver
  4. Browse the service1.svc

See also:
How to create a WCF returning response in JSON format.

Cheers!
Sanjay

 

Automate SFTP with SSIS – Part 5 – Schedule the SSIS Package at SQL Agent Service

Automate SFTP with SSIS – Part 5 – Schedule the SSIS Package at SQL Agent Service

  1. Create new agent job
    ScreenShot009
  2. Point to SSIS package created at part 4
    ScreenShot020
  3. Schedule the package to complete automation of SFTP!!!

See Also:
Part 1 : Automate SFTP file operation using SSIS
Part 2 : Setting up Free SFTP Server
Part 3 : Testing the SFTP Server from a client
Part 4 : Creating a SSIS Package to automate SFTP File operations
Part 5 : Scheduling the SSIS Package
Part 6 : Save SFTP incoming files info to SQL table
Part 7 : Logging SSIS Package

Cheers!
Sanjay

 

 

Automate SFTP with SSIS – Part 4 – Creating SSIS Package

Automate SFTP with SSIS – Part 4 – Creating SSIS Package

  1. Create an Integration Service Package and name it winscpScreenShot001
  2. Create to Script Tasks
    ScreenShot002
  3. Add 4 package level variables and provide values
    ScreenShot003
  4. The value for sshHostKeyFingerPrint would the value copied when connecting to the SFTP server for the first time using winscp client as explained in part 3 point 2.
    ScreenShot008
  5. Double click the upload script task and make available the package level variables as read only variables
    ScreenShot004
  6. Double click the download task and make available the package level variables as read only variables.
  7. Double click the upload files script task, and edit the script
    ScreenShot005
  8. At the script editor add reference to the winscpnet.dll
    ScreenShot007
    ScreenShot006
  9. Similarly add reference to the download task as well.
  10. Double click on upload task, click on edit script and replace the script. Ensure to retain the original namespace line.  The script should be updated for the folder path.
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
    using System.AddIn;
    using WinSCP;
    
    
    namespace ST_c8db38a05a7d457bb96729ea03fbf461.csproj
    {
        [AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
        public partial class ScriptMain : VSTARTScriptObjectModelBase
        {
            public void Main()
            {
                // Setup session options
                SessionOptions sessionOptions = new SessionOptions
                {
                    Protocol = Protocol.Sftp,
                    // To setup these variables, go to SSIS > Variables.
                    // To make them accessible from the script task, in the context menu of the task,
                    // choose Edit. On the Script task editor on Script page, select ReadOnlyVariables,
                    // and tick the below properties.
                    HostName = (string)Dts.Variables["User::HostName"].Value,
                    UserName = (string)Dts.Variables["User::UserName"].Value,
                    Password = (string)Dts.Variables["User::Password"].Value,
                    SshHostKeyFingerprint = (string)Dts.Variables["User::SshHostKeyFingerprint"].Value
                };
    
                try
                {
                    using (Session session = new Session())
                    {
                        // As WinSCP .NET assembly has to be stored in GAC to be used with SSIS,
                        // you need to set path to WinSCP.exe explicitly, if using non-default location.
                        session.ExecutablePath = @"c:\Program Files (x86)\WinSCP\WinSCP.exe";
    
                        // Connect
                        session.Open(sessionOptions);
    
                        // Upload files
                        TransferOptions transferOptions = new TransferOptions();
                        transferOptions.TransferMode = TransferMode.Binary;
    
                        TransferOperationResult transferResult;
                        transferResult = session.PutFiles(@"F:\SFTP\Outgoing\*", "/Incomming/", true, transferOptions);
    
                        // Throw on any error
                        transferResult.Check();
    
                        // Print results
                        bool fireAgain = false;
                        foreach (TransferEventArgs transfer in transferResult.Transfers)
                        {
                            Dts.Events.FireInformation(0, null,
                                string.Format("Upload of {0} succeeded", transfer.FileName),
                                null, 0, ref fireAgain);
                        }
    
                    }
    
                    Dts.TaskResult = (int)DTSExecResult.Success;
                }
                catch (Exception e)
                {
                    Dts.Events.FireError(0, null,
                        string.Format("Error when using WinSCP to upload files: {0}", e),
                        null, 0);
    
                    Dts.TaskResult = (int)DTSExecResult.Failure;
                }
            }
        }
    
    }
  11.  Similarly update the script for the download script task.  Ensure to retain the original namespace line.  Update scripts for folder paths.
    /*
       Microsoft SQL Server Integration Services Script Task
       Write scripts using Microsoft Visual C# 2008.
       The ScriptMain is the entry point class of the script.
    */
    
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
    using System.AddIn;
    using WinSCP;
    
    namespace ST_85cb36206d6549bc9cca1653725a7ea8.csproj
    {
        [AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
        public partial class ScriptMain : VSTARTScriptObjectModelBase
        {
            public void Main()
            {
                // Setup session options
                SessionOptions sessionOptions = new SessionOptions
                {
                    Protocol = Protocol.Sftp,
                    // To setup these variables, go to SSIS > Variables.
                    // To make them accessible from the script task, in the context menu of the task,
                    // choose Edit. On the Script task editor on Script page, select ReadOnlyVariables,
                    // and tick the below properties.
                    HostName = (string)Dts.Variables["User::HostName"].Value,
                    UserName = (string)Dts.Variables["User::UserName"].Value,
                    Password = (string)Dts.Variables["User::Password"].Value,
                    SshHostKeyFingerprint = (string)Dts.Variables["User::SshHostKeyFingerprint"].Value
                };
    
                try
                {
                    using (Session session = new Session())
                    {
                        // As WinSCP .NET assembly has to be stored in GAC to be used with SSIS,
                        // you need to set path to WinSCP.exe explicitly, if using non-default location.
                        session.ExecutablePath = @"c:\Program Files (x86)\WinSCP\WinSCP.exe";
    
                        // Connect
                        session.Open(sessionOptions);
    
                        // Download files
                        TransferOptions transferOptions = new TransferOptions();
                        transferOptions.TransferMode = TransferMode.Binary;
    
                        TransferOperationResult transferResult;
                        transferResult = session.GetFiles("/Outgoing/*", @"F:\SFTP\Incomming\*", true, transferOptions);
    
                        // Throw on any error
                        transferResult.Check();
    
                        // Print results
                        bool fireAgain = false;
                        foreach (TransferEventArgs transfer in transferResult.Transfers)
                        {
                            Dts.Events.FireInformation(0, null,
                                string.Format("Upload of {0} succeeded", transfer.FileName),
                                null, 0, ref fireAgain);
                        }
    
                    }
    
                    Dts.TaskResult = (int)DTSExecResult.Success;
                }
                catch (Exception e)
                {
                    Dts.Events.FireError(0, null,
                        string.Format("Error when using WinSCP to upload files: {0}", e),
                        null, 0);
    
                    Dts.TaskResult = (int)DTSExecResult.Failure;
                }
            }
        }
    }
  12.  Link the packages to execute the upload first and download second.
  13. Save the package and test package execution.

See Also:
Part 1 : Automate SFTP file operation using SSIS
Part 2 : Setting up Free SFTP Server
Part 3 : Testing the SFTP Server from a client
Part 4 : Creating a SSIS Package to automate SFTP File operations
Part 5 : Scheduling the SSIS Package
Part 6 : Save SFTP incoming files info to SQL table
Part 7 : Logging SSIS Package a>

Cheers!
Sanjay

 

 

 

 

 

Automate SFTP with SSIS – Part 3 – Testing sftp service from a client.

Automate SFTP with SSIS – Part 3 – Testing sftp service from a client.

  1. Install Winscp client. http://winscp.net/download/winscp554setup.exe
  2. Start the Winscp client:ScreenShot
  3. Continue to view the root folder at sftp.
  4. Follow the same step at SQL server which will run the SSIS package with winscp.net library.

See Also:
Part 1 : Automate SFTP file operation using SSIS
Part 2 : Setting up Free SFTP Server
Part 3 : Testing the SFTP Server from a client
Part 4 : Creating a SSIS Package to automate SFTP File operations
Part 5 : Scheduling the SSIS Package
Part 6 : Save SFTP incoming files info to SQL table
Part 7 : Logging SSIS Package a>

Cheers!
Sanjay

Automate SFTP with SSIS – Part 2 – setup for sftp server

Automate SFTP with SSIS – Part 2 – setup for sftp server

  1. Download freeftpd server http://www.freesshd.com/?ctt=download
  2. Install freeftpd as administration on sftp server
  3. Configure freeFTPdScreenShot
  4. Start the sftp service.
  5. Add users to sftp server ScreenShot1

See Also:
Part 1 : Automate SFTP file operation using SSIS
Part 2 : Setting up Free SFTP Server
Part 3 : Testing the SFTP Server from a client
Part 4 : Creating a SSIS Package to automate SFTP File operations
Part 5 : Scheduling the SSIS Package
Part 6 : Save SFTP incoming files info to SQL table
Part 7 : Logging SSIS Package >

Cheers!

Sanjay

 

Automate SFTP with SSIS – Part 1 – Winscp installation

Automate SFTP with SSIS – Part 1 – Winscp installation

At the SQL Server where SSIS package will execute:

    1. Set Local security policy
      Security Setting
      Security Options
      User Account Control: Admin Approval Mode for the Build – in Administrator account – Enabled
    2. Reboot the SQL Server
    3. Download winscp.net assembly  http://winscp.net/download/winscp554automation.zip
    4. Download winspc program
      http://winscp.net/download/winscp554setup.exe
    5. Install winscp on server to default location c:\program files (x86)\winscp
    6. Copy winscp .net assembly to winscp install folder.
    7. Locate the gautil.exe and gautil.exe.config at a machine where visual studio is installed :
      C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools\gacutil.exe
      C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools\gacutil.exe.config
    8. Copy the above files to windows 2008 R2 server where SSIS package will execute to folder C:\Windows\Microsoft.NET\Framework\v4.0.30319
    9. Register the assembly at the windows 2008 R2 server
      1. Open command prompt as administrator
      2. change direcotyr to C:\Windows\Microsoft.NET\Framework\v4.0.30319
      3. gcautil /I “c:\program files (x86)\winscp\winscpnet.dll”
      1. Set Local policy
        Security setting
        Security Options
        User Account Control:Admin Approval Mode for the built-in-Administrator account – Disabled.
      2. Reboot the windows 2008 R2 Server

See Also:
Part 1 : Automate SFTP file operation using SSIS
Part 2 : Setting up Free SFTP Server
Part 3 : Testing the SFTP Server from a client
Part 4 : Creating a SSIS Package to automate SFTP File operations
Part 5 : Scheduling the SSIS Package
Part 6 : Save SFTP incoming files info to SQL table
Part 7 : Logging SSIS Package

Cheers!
Sanjay

WCF webservice returning data in JSON format – VB.Net

WCF webservice returning data in JSON format:

  1. Create a visual basic WCF Service Application project at visual studio 2013
  2. Name the project JSONWebservice.
  3. Replace Iservice1.vb with following code
  4. <ServiceContract()> _
    Public Interface IService1
        <OperationContract()> _
        <WebInvoke(Method:="GET", ResponseFormat:=WebMessageFormat.Json, BodyStyle:=WebMessageBodyStyle.Wrapped, UriTemplate:="GetApprover/{value}")>
        Function GetApprover(ByVal value As String) As String
    End Interface
    
    
  5. Replace Service1.svc with following
  6. Imports System.Data
    Imports System.Data.SqlClient
    Public Class Service1
        Implements IService1
        Public Function GetApprover(ByVal value As String) As String Implements IService1.GetApprover
            Dim connectionstring As String
            Dim connection As SqlConnection
            Dim command As SqlCommand
            Dim adaptor As New SqlDataAdapter
            Dim ds As New DataSet
            Dim sql As String
            connectionstring = "Data Source = SQLSERVER;Initial Catalog=DBNAME;User Id=USERID;Password=PASSWORD"
            sql = "select approverlogin from DBNAME.dbo.TABLENAME where employeelogin = '" & value & "'"
            connection = New SqlConnection(connectionstring)
            connection.Open()
            command = New SqlCommand(sql, connection)
            adaptor.SelectCommand = command
            adaptor.Fill(ds)
            adaptor.Dispose()
            command.Dispose()
            connection.Close()
            If ds.Tables(0).Rows.Count > 0 Then
                connectionstring = ds.Tables(0).Rows(0).Item(0)
            Else
                connectionstring = "approver not found"
            End If
            ds.Clear()
            Return connectionstring
        End Function
    End Class
  7. Update the web.config
  8. <system.serviceModel>
        <services>
          <service name="JSONWebService.Service1" behaviorConfiguration="JSONWebService.Service1Behavior">
            <!-- Service Endpoints -->
            <endpoint address="../Service1.svc"
                      binding="webHttpBinding"
                      contract="JSONWebService.IService1"
                      behaviorConfiguration="webBehaviour"/>
          </service>
        </services>
        <behaviors>
          <serviceBehaviors>
            <behavior name="JSONWebService.Service1Behavior">
              <!-- To avoid disclosing metadata information, set the value below to false before deployment -->
              <serviceMetadata httpGetEnabled="true"/>
              <!-- To receive exception details in faults for debugging purposes, set the value below to true.  Set to false before deployment to avoid disclosing exception information -->
              <serviceDebug includeExceptionDetailInFaults="false"/>
            </behavior>
          </serviceBehaviors>
          <endpointBehaviors>
            <behavior name="webBehaviour" >
              <webHttp />
            </behavior>
          </endpointBehaviors>
        </behaviors>
      </system.serviceModel>
  9. Setup up the start page at project propertiesScreenShot017
  10. Create a table with two columns approverlogin and employeelogin
  11. Insert sample date employeelogin (Sanjay Rajarao), (Ryan Jeffryes)
  12. Test ::  http://localhost:57086/Service1.svc/GetApprover/Sanjay%20Rajarao
  13. Return Value :
    {"GetApproverResult":"Ryan Jeffryes"}
  14. Webservice returns data in JSON format.

See also:
How to manually host WCF Service on IIS

 

Cheers!

Sanjay

Delete a Subsite from Sharepoint 2013

Delete a Sub Site from Sharepoint 2013

  1. Log on the parent site
  2. Click on the gear icon at the top left corner
  3. Click on site settings
  4. Click on Site Administration > Sites and workspace
  5. All sub-sites are listed, delete the sub site button is listed at end of each sub site listing.

Cheers!

Sanjay