Receiving (Downloading) multiple files through FTP

The FTP Task gives us the ability to send files from a local system to a remote host or retrieve them from a remote host to a local system. For example a company may export all its data into some CSV files, text files or other type of the files and they put them on an FTP address, and another company or other processes may need to get those files from FTP. The FTP Task also provides a way to work with multiple files.

Read FTP task : Click here

http://bageshkumarbagi-msbi.blogspot.in/2014/12/ftp-task.html

Receiving (Downloading) Multiple files from FTP server

The FTP Task is for the transfer of files through FTP from local to remote or the reverse. To work with the FTP Task, we first need to create an FTP connection manager. When creating the FTP connection manager, server name and port can be defined. Credentials for connections to the FTP Server are also configurable.

Microsoft provide the free FTP server: ftp.microsoft.com

 We can find some free FTP server for testing purpose.

Ø  http://test.rebex.net/

Ø  ftp.microsoft.com

Ø  http://www.wftpserver.com/onlinedemo.htm

Ø  http://www.sftp.net/public-online-sftp-servers

For this sample we used test.rebex.net.

Here I am creating FTP connection

 

Clicking on the Test Connection

 

Connection established successfully.

Options on the connection Manager

The FTP connection timeout can be defined. The default value is 60 seconds. Operations in the File Transfer tab define what operation should be done in the FTP Task.

Use Passive Mode:

There is an option in FTP Connection Manager to choose between Passive or Active mode for connection type. In the Active mode, the server initiates the connection, but in Passive mode the client initiates the connection. By default, FTP Connections are in Active mode.

File Transfer:

  

Here I am creating the local path

 

Now I am creating the Remote connection.

 

Suppose if we select the one file then we can download one file .If we want to download specific file format we need to give folder and the .extension. See the example

 

We can download all text files. Here I am downloading all .png files.

 

Now I am executing the package.

 

Executing package.

 

Package executed successfully. See the output.

 

Suppose we want to download the all file from the folder we need to give the Remote path as below.

 

Now I am executing the package

 

See the result.

List of tables in SSISDB

1.       Folders

2.       Object_versions

3.       Projects

4.       Packages

5.       Object_parameters

6.       Environments

7.       Environment_variables

8.       Environment_references

9.       Operations

10.   Extended_operation_info

11.   Operation_os_sys_info

12.   Operation_messages

13.   Executions

14.   Executables

15.   Executable_statistics

16.   Execution_data_statistics

17.   Execution_component_phases

18.   Execution_data_taps

19.   Event_messages

20.   Event_message_context

21.   Execution_parameter_values

22.   Execution_property_override_values

23.   Validations

24.   Catalog_properties

25.   Folder_permissions

26.   Project_permissions

27.   Environment_permissions

28.   Operation_permissions

29.   Data_type_mapping

30.   Catalog_encryption_keys

Data type mapping table in SSISDB

This table stores the data type mapping of sql server and SSIS data type.

SELECT [mapping_id]       ,[ssis_data_type]       ,[sql_data_type]   FROM [SSISDB].[internal].[data_type_mapping]

See the below

 

Event Messages Context table in SSISDB

This table stores the information such as package path source name, Property name and property value.

SELECT [context_id]       ,[operation_id]       ,[event_message_id]       ,[context_depth]       ,[package_path]       ,[context_type]       ,[context_source_name]       ,[context_source_id]       ,[property_name]       ,[property_value]   FROM [SSISDB].[internal].[event_message_context]

 

Executable Statistics table in SSISDB

Displays a row for each executable that is run, including each iteration of an executable. An executable is a task or container that you add to the control flow of a package.

SELECT [statistics_id]       ,[execution_id]       ,[executable_id]       ,[execution_path]       ,[start_time]       ,[end_time]       ,[execution_hierarchy]       ,[execution_duration]       ,[execution_result]       ,[execution_value]   FROM [SSISDB].[internal].[executable_statistics]

 

It store the execution duration. Here we can get the executing time of the package. The length of time the executable spent in execution. The value is in milliseconds.

Execution Result

The following are the possible values:

Ø  0 (Success)

Ø  1 (Failure)

Ø  2 (Completion)

Ø  3 (Cancelled)

Operations table in SSISDB

This table has details of all operations in the Integration Services catalog.

SELECT [operation_id]       ,[operation_type]       ,[created_time]       ,[object_type]       ,[object_id]       ,[object_name]       ,[status]       ,[start_time]       ,[end_time]       ,[caller_sid]       ,[caller_name]       ,[process_id]       ,[stopped_by_sid]       ,[stopped_by_name]       ,[operation_guid]       ,[server_name]       ,[machine_name]   FROM [SSISDB].[internal].[operations]

The type of object affected by the operation.

Object Type

The object type may be

10- Folder

20- Project

30 -package

40 -environment

50- Instance of execution

Status

The status of the operation. The possible values

1-arecreated

2-running

3-canceled

4-failed

5-pending

6-ended unexpectedly

7-succeeded

8-stopping

9-completed

Operation type

This view displays the following operation types, as listed in the operation type column:

Operation Type	Operation Type description
1	Integration Services
2	Retention window (sql Agent job)
3	Max project version(SQL Agent Job)
101	Deploy Project
106	Restore project
200	Create execution and start execution
202	Stop operation
300	Validated project
301	Validate package
1000	Configuration catalog

Created Date

In this column store when the package is executed.

Object ID

It is nothing but the package ID.

SELECT       p.name,*         FROM [SSISDB].[internal].[operations] o   INNER JOIN [SSISDB].[internal].packages p   ON p.package_id=o.object_id   order by 1

  

Start Time

When the package was stared.

End Time

When the package was completed.

We can find the time taken by the package with the help of start and end time.

Caller name

How we call the package. The name of the account that performed the operation. I mean to say we call the packages through sql server agent or manually or any third party tool.

Server name

Where the packages are store.

Machine name

From where we execute the package.

I have executed on my pc so that it is showing same.

Note: If our server is running shortage of space we can truncate this table. It will not affect our operation. Here we are store only package executing details. 

Catalog properties table in SSISDB

This table stores the information of the catalog properties

SELECT property_name,property_value   FROM [SSISDB].[internal].Catalog_properties

See below

 

Encryption Algorithm

The type of encryption algorithm that is used to encrypt sensitive data. The supported values include: 

Ø  DES

Ø  TRIPLE_DES

Ø  TRIPLE_DES_3KEY

Ø  DESX

Ø  AES_128

Ø  AES_192

Ø  AES_256

Note: The catalog database must be in single-user mode in order to change this property.

MAX Project Version

The number of new project versions that will be retained for a single project. When version cleanup is enabled, older versions beyond this count will be deleted. By default value it 10 but we can changes it to 999. It depends on the business requirement.

OPERATION CLEANUP ENABLED

When the value is TRUE, operation details and operation messages older than RETENTION_WINDOW (days) are deleted from the catalog. When the value is FALSE, all operation details and operation messages are stored in the catalog. Note: a SQL Server job performs the operation cleanup.

RETENTION WINDOW

The number of days that operation details and operation messages are stored in the catalog. When the value is -1, the retention window is infinite.  Note: If no cleanup is desired, set OPERATION_CLEANUP_ENABLED to FALSE.

VALIDATION TIMEOUT

Validations will be stopped if they do not complete in the number of seconds specified by this property.

VERSION CLEANUP ENABLED

When the value is TRUE, only the MAX_PROJECT_VERSIONS number of project versions is stored in the catalog and all other project versions are deleted. When the value is FALSE, all project versions are stored in the catalog. Note: a SQL Server job performs the operation cleanup.