Feb 12, 2014

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 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

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

Feb 11, 2014

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 enabled SQL/CLR debugging for this connection.


Step 5: Right click on you 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 sever 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 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