Tuesday 25 April 2017

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

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.

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.





Sunday 16 April 2017

List of tables in SSISDB

1.       Folders
2.       Object_versions
3.       Projects
5.       Object_parameters
6.       Environments
7.       Environment_variables
9.       Operations
10.   Extended_operation_info
11.   Operation_os_sys_info
13.   Executions
14.   Executables
16.   Execution_data_statistics
17.   Execution_component_phases
18.   Execution_data_taps
22.   Execution_property_override_values
23.   Validations
25.   Folder_permissions
26.   Project_permissions
27.   Environment_permissions
28.   Operation_permissions
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.

Popular Posts