Working with File System Task in SSIS

Working with File System Task

File system task in SSIS enable us to transfer files from one location to another location.

File system task will be available in Control Flow Tab Tool Box as shown below.

Right click on File System Task or double click on file system Task

It will open a new window as shown below.

Select the option copy directory option so that we can copy the data across all directories.

If we directly use Move Directory we will get the error as mentioned below.

[File System Task] Error: An error occurred with the following error message: "Source and destination path must have identical roots. Move will not work across volumes.”

In order to fix this Issue we can create the directory as same as source. Then move the files using copy directory. Then remove the Source Directory using the delete directory.

The steps involved in this process are

Operations:

Create directory in Target

Copy directory into Target

Delete directory from source

Using the above three steps we can achieve moving the directory from one volume to other volume.

Execute process task in SQL Server Integration services (SSIS)

How to Open Aplication Log using Execute Process Task in SSIS

Execute process task in SSIS is used for running an executable file. For example we can use task to open event viewer application log.

Execute Process task is available in control flow tab in SSIS

Here we will open event viewer Application log using the execute process task. It will useful to identify error related to application log.

First step is to drag and drop Execute process task from control flow tab.

Then double click on  Execute process task . Go to Process Tab as shown below.

Select the working Directory as

C:\Windows\System32

Provide the executable as

eventvwr.exe

In argument menu provide with the details below.

We have used /l and full path of the application log file.

/l:C:\Windows\System32\Winevt\Logs\Application.evtx

Here are some of the arguments

/l   opens the log file

/v view the file using query

Window Style you can use as minimized to open in minimized format.

click on ok to save . Press F5 to execute the package. 

Importing Excel sheet data into SQL server(screenshots)

Import excel sheet data into SQLServer table using SSIS.

1) For creating excel connection               

Drag and drop the excel connection from dataflow 

2) Double Click on Excel Source

 3) Click on new Connection

4) Browse Path of excel need to upload

5) Select name of excel sheet

6) Click on Preview

7) Select Ignore failure and click on apply if need to skip the conversion errors. Click on ok

9) Drag and drop data conversion

10) Connect using precedence constraints

11) Double Click and select available input columns

12) Convert the Data type.

General Conversion: Excelà Database

Int        ààà    Four byte Signed integer [DT-I4]

Bigint ààà     Eight byte Signed integer [DT-I8]

Varcharààà String [DT-STR]

BIT        ààà Boolean [DT-Boolean]

DATE   àààDatabase date [DT_DBDATE]

13) drag and drop the SQLServer destination

14) Connect Data conversion with sqlserver destination using precedence constraints.

Double click and click on new 

15)  Click on new and select the appropriate server and database (to where you want to upload the excel data into sqlserver).

16) Click on OK. Select the table where you want to upload the excel data.

17)  Select mapping and select conversion columns

18) Click on OK .If there is any conversions error is there it will show in red mark.

19) Please verify and select appropriate conversions

20) If there is any warning rectify using data conversions.

21) Execute the package

22) If there any errors verify the errors. Suppose in my case client_id is contain null value

23) Change the query in connection manager .ensure that key columns cannot contain null values

24) Modify and save the package and execute.

25) Finally verify the data in database

Save SQLServer Data into XML format

Saving Data FROM SQL Server to XML Format

Here am showing the way to Save SQL Table data from SQL server to XML format into Local file.

For that we need two Controls from Control flow task

  Here is the screen shot.

Execute SQL Task

Script Task.

First Create Connection for SQL Server. Right Click on connection Managers as Shown below.

Use appropriate Server name. In my case I used my local Database.

Select appropriate database.

Click on Test connection for testing Connection

Double Click on Execute SQL task.

Go to General tab as shown below.

Select Result set as XML from Dropdown.

Enter Query which will give the XML format

Select Result set tab. Set Result Name to Zero. Select variable name for saving data to variable.

Parse Query.

Select Script Task. Select Read only Variables

Edit Script. Add this code

Finally execute the package

After Successful Execution