The Ssis-sqltask control uses

Using SQL Task controls

Supplemental Knowledge: execution_id: Is the unique identity of each execution of the SSIS package; Package_name: is the name of the SSIS package; Machine_Name: is the machine name; These parameters can be found in the system parameters in the SSIS package!

Example 1: Inserting data into the target database from the SSIS package itself.

1. Set up target database: t_logs

Create TableT_logs (IDINT PRIMARY KEY IDENTITY(1,1), execution_idNVARCHAR(255), Package_nameNVARCHAR(255), Machine_NameNVARCHAR(255), Start_timeDATETIME NULL, Finish_timeDATETIME NULL, execution_status_idINT)

2. Use the SQL task control in SSIS to insert data, connect data sources, build SQL statements, insert into T_logs VALUES (?,?,?, GETDATE (), null,1), note, "? The form of this placeholder applies only to SQL Server.

3. Run to see the results:

Example 2: Executing a stored procedure with SQL task

1. Ready-to-execute stored procedures:

CREATE PROCEDUREPro_tlogs@EXECUTION_ID NVARCHAR(255),@PACKAGE_NAME NVARCHAR(255),@MACHINE_NAME NVARCHAR(255),@ID INTOUTPUT asBEGIN        SETNOCOUNT on; INSERT  intoT_logsVALUES(@EXECUTION_ID,@PACKAGE_NAME,@MACHINE_NAME,GETDATE(),NULL,2) [email protected] @IDENTITY is a built-in parameter in SQL Server that represents the ID that was automatically generated when the record was last inserted
    SELECT @ID=@ @IDENTITY    RETURN  -ENDGO


--If the above stored procedure is executed manually, use SQL script
DECLARE @IDOUT INT
DECLARE @RETURN_VALUE INT

EXEC @RETURN_VALUE = pro_tlogs @EXECUTION_ID = ' 002 ', @PACKAGE_NAME = ' HELLO ', @MACHINE_NAME = ' ABO ', @[email protected] OUTPUT
SELECT @IDOUT as ' self-growing ID ', @RETURN_VALUE as ' return value '
--Execution results:

2. Execute a stored procedure using the SQL Task control in SSIS

2.1 Building the SQL statement:

2.2 Because the parameters @return_value and @idout are not system parameters, we create two user variables ourselves

2.3 Execute Package View results:

The Ssis-sqltask control uses