Skip to main content

SSIS Tasks

Data Flow Task

  • Data Flow Task: This task extracts data from a source, allows for transformations of that data, and then the data is loaded into a target data destination.

Data Preparation Tasks

  • File System Task: This task allows the user to copy/move/delete files and directories on a file system.
  • FTP Task: This task allows the user to copy/move/delete files and directories over FTP.
  • Web Service Task: This task allows the user to execute a Web service method and store the results
  • XML Task: This task is used to work with XML data. XSLT can be used along with XPath to validate, compare and merge documents. The results of this can then be stored.
  • Data Profiling Task: This task can be used for checking and validating data quality. Profiles can be set up and checked for varius data quality issues such as, column length issues, column patterns, column statics, etc.

Workflow Tasks

  • Execute Package Task: This task will run other SQL Server Integration Services packages.
  • Execute Process Task: This task will execute an application or batch file.
  • Message Queue Task: This task allows you to send and receive messages between SSIS packages, or to send messages to an application via an application queue. This task uses Message Queuing (MSMQ)
  • Send Mail Task: This task allows for email messages to be created and sent using an SMTP server.
  • WMI Data Reader Task: This task allows a Package to query, using WQL, computer systems (local and remote) for information regarding that computer.
  • WMI Event Watcher Task: This task watches for WMI events that have occurred on a computer system, and allows the package to take an action if certain criteria are met.

Scripting Tasks

  • Script Task: This task can be used to program functions that are not available in the standard SSIS tasks or transformations. In SSIS 2005 this task can be programmed in VB .NET. In SSIS 2008 VB .NET and C# can be used to program a Script Task.

SQL Server Maintenance Tasks

  • Back Up Database Task: This task will allow you to backup a one or many SQL Server databases.
  • Check Database Integrity Task: This task will allow you to check the integrity of all the objects in one or many SQL Server databases.
  • Execute SQL Server Agent Job Task: This task allows for the execution of a SQL Server Agent job.
  • Execute T-SQL Statement Task: This task is similar to the Execute SQL Task, however it only supports Transact SQL Statements. It should be used for SQL Server specific SQL statements.
  • History Cleanup Task: This task allows for the cleanup of historical activity data. It will cleanup the history for database maintenance plans, backup activites, restore activities and SQL Server agent jobs.
  • Maintenance Cleanup Task: This task allows for the cleanup of backup files, and the reports of maintenance plans.
  • Notify Operator Task: This task allows SSIS to notify SQL Server Agent operators. They can be notifies by email, pager, or netsend.
  • Rebuild Index Task: This task will rebuild an index or indexes on one or many databases.
  • Reorganize Index Task: This task will reorganize an index or indexes on one or many databases.
  • Shrink Database Task: This task will shrink the size of the SQL Server database data and database log files.
  • Update Statistics Task: This task will update the statistics for one of many tables in one or many databases.
SQL Server Tasks
  • Bulk Insert Task: This task offers an efficient way to copy large volumes of data.
  • Execute SQL Task: This task allows the execution of a SQL statement. If the statement returns results, they can be stored in a variable.
  • Transfer Database Task: This task will copy or move a SQL Server database between two instances of SQL Server. It can even be used to make a copy of a database on the same server. Databases can be copied either online or offline.
  • Transfer Error Messages Task: This task will transfer a single or multiple SQL Server user defined error messages between SQL Server instances. It can be setup to transfer specific user messages or all error messages.
  • Transfer Jobs Task: This task will transfer a single or multiple SQL Server Agent jobs between SQL Server instances.
  • Transfer Logins Task: This task will transfer a single or multiple SQL Server logins between SQL Server instances.
  • Transfer Master Stored Procedures Task: This task will transfer a single or multiple SQL Server Master database stored procedures between SQL Server instances.
  • Transfer SQL Server Objects Task: This task will transfer a single or multiple SQL Server database objects between SQL Server instances. Most of SQL Servers DDL objects can be copied with this task.

Analysis Services Tasks

  • Analysis Services Execute DDL Task: This task will run data definition language statements on Analysis Services. This allows for the create, drop, alter of cubes, dimensions and mining models.
  • Analysis Services Processing Task: This task will process Analysis Services Cubes, Dimensions, and Mining Models.
  • Data Mining Query Task: This task will run a DMX (Data Mining Extensions) query that create a prediction based on new data that is run against a Analysis Services data mining model.

Labels:

Comments

Post a Comment

Popular posts from this blog

Self Introduction and Responsibilities of a SQL Server DBA.

Hi, Self Introduction:                      I am Vang chew bigger , currently I am working with   Citronics , Here my role is SQL Server Database Administrator , I am having 3+ yrs Exp in SQL SERVER DBA with T-SQL, I have experience on SQL Server 2000, 2005 and 2008, I have a great experience on installation and configuration of SQL SERVER versions, and also applying the patches, hot fix, services packs and RTM’s accordingly, involved in upgrading on in place and side by side as per client requirement, and also Migrating the database on SQL SERVER 2000 to 2005 and 2008, Solid experience on configuring and maintenance of High availability SQL Server solutions, including Log shipping, Database Mirroring, Replication(Transactional and Merge) and SQL Server Clustering., having experience on performance tuning on server level, database level and query level, Responsible for working with application developers in identifying, resolving and proactively working to prevent performance or oth
3 comments
Read more

SQL Server 2016 Windows Server 2016 Firewall Rule Step-By-Step.

SQL Server 2016 Windows Server 2016 Firewall Rule Step-By-Step. Opening Firewall in SQL Server 2016. Once the SQL Server is installed on the machine. We must enable the incoming traffic i.e., from the application to the SQL Server Database. For this purpose, we must create rule for SQL Server ports so that the application can connect to database & perform the CRUD operations on the tables. Now Let’s start Step-by-Step to enable it. 1.   Start System and Login (As Administrator); 2.   Open Control Panel. Click on Windows Firewall; 3. Now click on Advanced Settings; 4. Windows Firewall console open. If you click on Properties (right side) – you can disable firewall for all networks. We going to Inbound Rules (left side) for our rule creation; After Clicking on the Inbound rules. We will see the below screen.  Click on New
Post a Comment
Read more

Shrinking a SQL Server Log File with Database in Always ON Availability.

Shrinking a SQL Server Log File with Database in Always ON Availability.                    Once we had a situation in our production server that the database which is in Always ON Availability group has been grown to the maximum size.  Many of them say that we can't shrink the log file as the database is in Always ON and we should take the downtime to perform the operation. But we shrink-ed the database log file to 5 GB from 180 GB. Steps are as below: --To Check the Number of pages allocated by the LogFile. DBCC LOGINFO --To check the file sixe sp_helpdb [SQLPantry] --To check the Log file Size DBCC SQLPERF(LOGSPACE) The above statement will show the logspace of all db at present situation. --To check the Log_reuse_wait description of a log file. select log_reuse_wait_desc,* from sys.databases The above statement will show the status of the log whether it has to be backuped i.e., LogBackup, Nothing, Active Transaction.  When ever there is status as Activ
1 comment
Read more