Create SQL CLR Project in VB

Create SQL CLR Project in VB:

File -> New Project
SQL Server
OK
At Project properties select SQLCLR Tab
Change Lanugage to VB
Add new items
SQL CLR VB -> SQL CLR VB User Defined Functions

Cheers!
Sanjay

SSRS – User Domain\login does not have required permissions.

Verify that sufficient permission have been granted and Windows user account control (UAC) restriction has been addressed.

Resolution:
Start IE Administrator
Browse to SSRS reportserver (http://server/reports)
Click on the “Folder Settings” button.
Click on the “New Role Assignment” button.
Provide access to user

Cheers!
Sanjay

 


 

Open sub report in new window from SSRS – SharePoint integrated mode

For the textbox field in SSRS Tablix set an action to go to URL with following Expression:

javascript:void(window.open(‘” & Globals!ReportServerUrl &“/Pages/ReportViewer.aspx?” & Globals!ReportFolder & “/PO_Lines.rdl&rc:Parameters=False&PONumber=”& Fields!PO_NUMBER.Value & “‘))

Where:

  1. PO_Lines.rdl is the sub report name
  2. PONumber is the Sub report parameter
  3. PO_NUMBER.Value is the main report field containing value for the sub report parameter.

Cheers!
Sanjay

 

 

 

 

 

 

SSIS Delete folder

Public Sub Main()
        Dim ParentFolder As String
        Dim RetentionPeriod As Integer
        RetentionPeriod = Convert.ToInt32(Dts.Variables("User::RetentionPeriodInDays").Value)
        ParentFolder = Dts.Variables("User::sTargetFolder").Value.ToString()
        Dim folders = New DirectoryInfo(ParentFolder).GetDirectories()
        Dim sArchiveFolder As String
        Dim dir As System.IO.DirectoryInfo
        For Each Folder In folders
            If Folder.CreationTime < DateTime.Today.AddDays(-RetentionPeriod) Then
                sArchiveFolder = ParentFolder & Folder.Name
                MessageBox.Show(sArchiveFolder)
                ClearFolder(sArchiveFolder)
                dir = New System.IO.DirectoryInfo(Folder.FullName)
                dir.Delete(True)
            End If
        Next
        Dts.TaskResult = ScriptResults.Success
    End Sub
    Private Sub ClearFolder(ByVal FolderName As String)
        Dim dir As New DirectoryInfo(FolderName)
        For Each fi As FileInfo In dir.GetFiles()
            fi.IsReadOnly = False
        Next

        For Each di As DirectoryInfo In dir.GetDirectories()
            ClearFolder(di.FullName)
        Next
    End Sub
End Class

 

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

 

 

 

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