Copy a file in File System Task in SSIS by examples

In SSIS (Sql server integration services) we can copy any types of file from one location to another location by using File System Task.

Suppose we have a text file at C:\Source\ssis.txt which we want to copy at any other location say C:\Destination\

Step 1:  Create a new integration services project.

Step 2: In control flow tab, drag and drop the File System Task.

Step 3:  Double click on File System Task. We will get the File System Task Editor.

Name:  It can be any name of task. In this example task name is Copy file.

Description: It is description of the task. In this example description is Copy at c:\Destination\

Operation:  There are ten different operations performed by File System task. They are:

Task Name	Use
Create directory	To create a new folder at given path.
Delete directory	To delete an existing folder.
Delete directory content	To delete the all content of a folder.
Move directory	To move a folder from one location to other.
Copy directory	To copy a folder from one location to other.
Copy file	To copy a file from one location to other.
Move file	To move a file from one location to other.
Rename file	To rename a given file.
Set Attributes	To set the four attributes of a specific a file or folder. Attributes are:   1. Hidden   2. Read only   3. Archive   4. System

In this example we have to copy a file from one location to other so operation name will be Copy file. 

IsSorcePathVariable: It can be either true or false. If it is true then path of the source folder will stored in a variable. If it is false then path of the source folder will be static.  

In this example we will go for both the options.

Option 1: Set the IsSorcePathVariable to true.

Source variable: To specify path of the source folder which we have to copy. Select <New Variable…> from combo box. We will get the Add variable window.

Container: To specify the scope of the variable. In this example we will use Package (write name of your package). It means scope of variable is whole the package.

Name: Any name of the variable. In this example name of the variable is SourceFolderPath.

NameSpace: It can be either User of System. User means user defined variable. We can write any name of namespace of user defined variable. In this example we will keep User.

  

Value Type: Data type of variable. Since we have to store path of a folder so, we will choose String.

Value: It keeps the any value of a variable. In this example it will path of the source file i.e. C:\Source\ssis.txt

Now click on OK button.

Option 2: Set the IsSorcePathVariable to false.

SourceConnection: To specify the path of the source folder. Choose the <New connection…> from combo box. We will get File Connection Manager Editor.

Usage type: In this example choose the Existing file.

File: Click browse button to choose the source file which you want to copy.

Now click on OK button.

IsDestinationPathVariable: It can be either true or false. If it is true then path of the destination folder will be stored in a variable. If it is false then path of the source folder will be static.  Same way as we have done in case of source file. In this example we will set to False.

DestinationConnection: Choose the destination folder path where we have to copy the folder. In this example it is c:\Destination

OverwriteDestination: It can be either true or false. If it is true then while copying it will overwrite if the files and folders of source folder are already present in the destination folder if it is false then it will be not been overwritten. In this example we will keep True.

  

Now click on OK button.

Step 4: Now we will run the package. For this press F5 button from keyboard. If everything is fine we will get following screen: 

Assembly '' was not found in the SQL catalog of database ''. Msg 15135, Level 16, State 8, Procedure sp_addextendedproperty, Line 37 Object is invalid. Extended properties are not permitted on '', or the object does not exist. Msg 15135, Level 16, State 8, Procedure sp_addextendedproperty, Line 37 Object is invalid. Extended properties are not permitted on '', or the object does not exist.

When we will try to create a CLR objects CLR stored procedure of data types we may get error message like:

Assembly '' was not found in the SQL catalog of database ''.

Msg 15135, Level 16, State 8, Procedure sp_addextendedproperty, Line 37

Object is invalid. Extended properties are not permitted on '', or the object does not exist.

Msg 15135, Level 16, State 8, Procedure sp_addextendedproperty, Line 37

Object is invalid. Extended properties are not permitted on '', or the object does not exist.

Cause:

You have not created the assembly of your CLR object.

Solution:

First create the assembly of you CLR stored procedures or data types. For this go through the following link:

How to create assembly of CLR object

How to transfer or create CLR stored procedure from one database to other database in sql server

We can transfer or move or create CLR stored procedures from one database or from one sql server to other sql server (Other location):

Step 1: In the destination database where you want to create CLR stored procedure executes following sql queries:

sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

ALTER DATABASE DestinationDB SET TRUSTWORTHY ON

EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false

Note: Replace the DestinationDB by your database name.

Step 2: Right click on assembly of your stored procedure in the source database and choose Script Assembly As -> Create To -> New Query Editor Window

Step 3: You will get create assembly script something like this:

CREATE ASSEMBLY [DemoCLR]

AUTHORIZATION [dbo]

FROM 0x4D5A90000300000004...

WITH PERMISSION_SET = UNSAFE

GO

ALTER ASSEMBLY [DemoCLR]

ADD FILE FROM 0x4D6963726F

AS N'bin\Debug\DemoCLR.pdb'

GO

ALTER ASSEMBLY [DemoCLR]

ADD FILE FROM 0xEFBBBF496D706F727...

AS N'My Project\AssemblyInfo.vb'

GO

ALTER ASSEMBLY [DemoCLR]

ADD FILE FROM 0xEFBBBF496D706F72...

AS N'uspGetEmployeeInfo.vb'

GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyProjectRoot', @value=N'F:\Projects\DemoCLR\DemoCLR' , @level0type=N'ASSEMBLY',@level0name=N'DemoCLR'

GO

From above script find out path of .dll file of CLR stored procedure. In above script it shown in bold characters. In this example it is:   F:\Projects\DemoCLR\DemoCLR . Copy the .dll  file (In this example DemoCLR.dll)  from that path and copy it destination sql server where you want to create clr procedure. Edit the path in above script according to path of dll file in destination sql server in above script and execute the above script to create assembly.

Step 4: In the same way in SSMS generate the create stored procedure script of your CLR stored procedure. It will look something this:

CREATE PROCEDURE [dbo].[uspGetEmployeeInfo]

    @StatusMsg [nvarchar](4000) OUTPUT

WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [DemoCLR].[DemoCLR.StoredProcedures].[uspGetEmployeeInfo]

GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'uspGetEmployeeInfo.vb' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspGetEmployeeInfo'

GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=N'14' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspGetEmployeeInfo'

GO

Execute the above script in the destination database. 

How to create CLR stored procedure in visual studio 2010 in vb.net.
Output parameters of CLR stored procedure in vb.net. 

Output parameters of CLR stored procedure in vb.net

Vb.net code for output parameters of CLR stored procedure

Output parameter of CLR procedure must be passed by reference. Output parameters are must have attribute <Out ()>. For this we have to import System.Runtime.InteropServices

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports System.Runtime.InteropServices

Partial Public Class StoredProcedures

    Private Shared command As SqlCommand

    Private Shared reader As SqlDataReader

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub uspGetEmployeeInfo(<Out()> ByRef StatusMsg As String)

        Try

            Using connection As New SqlConnection("context connection=true")

                connection.Open()

                command = New SqlCommand("SELECT EmpName,Age,Country FROM Employee", connection)

                reader = command.ExecuteReader()

                SqlContext.Pipe.Send(reader)

            End Using

            StatusMsg = "Success"

        Catch ex As Exception

            StatusMsg = ex.Message

        End Try

    End Sub

End Class

Sql query to execute:

DECLARE  @StatusMsg NVARCHAR(4000)

EXEC [dbo].[uspGetEmployeeInfo] @StatusMsg OUTPUT

SELECT   @StatusMsg AS StatusMsg

How to create CLR stored procedure in visual studio 2010 in vb.net
How to transfer or create CLR stored procedure from one database to other database in sql server.

Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6212, Level 16, State 1, Line 1 CREATE ASSEMBLY failed because method '' on type '' in safe assembly '' is storing to a static field. Storing to a static field is not allowed in safe assemblies.

When we will deploy a CLR objects like CLR stored procedures, CLR data types etc from visual studio we may get error message like:

Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6212, Level 16, State 1, Line 1 CREATE ASSEMBLY failed because method '' on type '' in safe assembly '' is storing to a static field. Storing to a static field is not allowed in safe assemblies.

Solution:

Set the permission level of your project unsafe. For this select Database tab in properties window of your project and choose Permission level Unsafe.

Now build the project solution.

Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6212, Level 16, State 1, Line 1 CREATE ASSEMBLY failed because method '' on type '' in external_access assembly '' is storing to a static field. Storing to a static field is not allowed in external_access assemblies.

When we will deploy a CLR objects like CLR stored procedures, CLR data types etc from visual studio we may get error message like:

Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6212, Level 16, State 1, Line 1 CREATE ASSEMBLY failed because method '' on type '' in external_access assembly '' is storing to a static field. Storing to a static field is not allowed in external_access assemblies.

Solution:

Set the permission level of your project unsafe. For this select Database tab in properties window of your project and choose Permission level Unsafe.

Now build the project solution.

Msg 6263, Level 16, State 1, Line 4 Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

When will execute in CLR objects like CLR stored procedures or CLR data types we may get error message like:

Msg 6263, Level 16, State 1, Line 4

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

Cause: In your sql server execution of CLR objects has not been allowed.

Solution:

To enable it execute following query in SSMS:

sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

Deploy error SQL01268: .Net SqlClient Data Provider: Msg 10327, Level 14, State 1, Line 1 CREATE ASSEMBLY for assembly '' failed because assembly '' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.

When we will deploy a CLR objects like CLR stored procedures, CLR data types etc we may get error message like:

Deploy error SQL01268: .Net SqlClient Data Provider: Msg 10327, Level 14, State 1, Line 1 CREATE ASSEMBLY for assembly '' failed because assembly '' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.  The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.

Solution:

ALTER DATABASE SampleDB SET TRUSTWORTHY ON

EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false

Note: Replace the SampleDB by your database name.

Framework that is incompatible with the target instance of SQL Server: "Deploy error SQL01268: CREATE ASSEMBLY for assembly failed because assembly failed verification". To resolve this issue, open the properties for the project, and change the .NET Framework version.

When we will deploy a CLR object like CLR stored procedures or CLR data types we may get error message like:

Framework that is incompatible with the target instance of SQL Server: "Deploy error SQL01268: CREATE ASSEMBLY for assembly failed because assembly failed verification". To resolve this issue, open the properties for the project, and change the .NET Framework version.

Cause: It is due to mismatch of .net framework. Either you're creating CLR objects in visual studio 2010 in .net framework version 4.0 etc.

Solution:

Change .net framework version to 3.5 or according to sql server version. For this right click on your project and choose Properties. Now select Compile tab and click on Advanced Compile options... and change the .net framework

And build the project solution.

If you do not have the .NET Framework version 3.5 installed on your development computer, you must install it if you want to develop SQL CLR assemblies for SQL Server 2005 or SQL Server 2008. SQL Server 2005 and SQL Server 2008 support only those assemblies that target the 2.0, 3.0, or 3.5 version of the .NET Framework. You specify the .NET Framework version in the project properties. Press F1 for more information.

When we will create CLR objects in Visual studio 2010 we may get warning message like:

If you do not have the .NET Framework version 3.5 installed on your development computer, you must install it if you want to develop SQL CLR assemblies for SQL Server 2005 or SQL Server 2008.  SQL Server 2005 and SQL Server 2008 support only those assemblies that target the 2.0, 3.0, or 3.5 version of the .NET Framework. You specify the .NET Framework version in the project properties. Press F1 for more information.

It is due to we cannot create sql objects like CLR stored procedures, CLR data types etc in .net 4.0 framework so it is necessary that your operating system must have .net framework of versions 3.5 or 3.0 etc.

How to create CLR stored procedure in visual studio 2010 in vb.net

We can create CLR stored procedure in visual studio 2010 in vb.net very easily if you following steps:

Step 1: Create a Visual Basic SQL CLR Database Project in visual studio.

Step 2: You will get Add Database Reference window. Click on Add New Reference...

Step 3: In New Database Reference window fill database information where you want to create CLR stored procedure.

Step 4: In pop up, you should enable SQL/CLR debugging for this connection.

Step 5: Right-click on your project in solution explorer and choose Add -> New Item...

Now select Stored Procedure and write down any name of your stored procedure.

Step 6: You will get auto gendered code something like this:

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub  uspGetEmployeeInfo ()

        ' Add your code here

    End Sub

End Class

Step 7: Here you can write code of your stored procedure according to your requirement. In this example, we are going to create such CLR stored procedure which displays the information of employees from Employee table. Open SQL server management studio (SSMS):

a. Creating Employee table in the SampleDb database.

USE [SampleDB]

CREATE TABLE [dbo].[Employee](

    [EmpID] BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    [EmpName] VARCHAR(100) NULL,

    [Age] TINYINT NOT NULL DEFAULT(0),

    [Country] VARCHAR(50) NULL,

 )

b. Insert some data into it. Let us assume Employee table has following data:

EmpID	EmpName	Age	Country
1	Scott	32	USA
2	Greg	31	UK
3	Akram	30	Pakistan
4	Rohit	23	India
5	Nicolus	28	Russia

c. Enable "clr enabled" configuration option.

EXECUTE SP_CONFIGURE 'clr enabled', 1

GO

RECONFIGURE

GO

d. Set your database trustworthy:

ALTER DATABASE SampleDB SET TRUSTWORTHY ON

Step 8: Edit the code of CLR stored procedure in visual studio:

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

    Private Shared command As SqlCommand

    Private Shared reader As SqlDataReader

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub uspGetEmployeeInfo()

        Using connection As New SqlConnection("context connection=true")

            connection.Open()

            command = New SqlCommand("SELECT EmpName,Age,Country FROM Employee", connection)

            reader = command.ExecuteReader()

            SqlContext.Pipe.Send(reader)

        End Using

    End Sub

End Class

Step 9: It is not possible to create CLR stored procedure in .net framework 4.0 so change it to 3.5

For this right click on your project and choose Properties. Now select Compile tab and click on Advanced Compile options... and change the .net framework

Step 10: Now make your project unsafe. For this select Database tab in the properties window of your project and choose Permission level Unsafe.

Step 11: Now build your solution and deployed your project.

Step 12: If deployment is successful then it will create CLR stored procedure in your database in SSMS.

Step 13: We cannot modify or see the script of CLR procedure in SSMS. Here we can only execute this stored procedure

USE [SampleDB]

DECLARE  @return_value INT

EXEC @return_value = [dbo].[uspGetEmployeeInfo]

SELECT   'Return Value' = @return_value

Output:

EmpName	Age	Country
Scott	32	USA
Greg	31	UK
Akram	30	Pakistan
Rohit	23	India
Nicolus	28	Russia

Note: It will also create one assembly inside Programmability -> Assemblies of SSMS

Output parameters of CLR stored procedure in vb.net.
How to transfer or create CLR stored procedure from one database to another database in SQL server.