Author: LeoLi

In today’s blog post, I am going to show you how to debug ForEach Loop Container variable.

Let’s say you fetched a data list from table and want to loop this list to do other tasks item by item. But you want to debug the item value before run other task.

First, Create two variables for Foreach Loop Container:

In the control flow of an SSIS package, add an Execute SQL Task and open the editor. Set the ‘Result Set‘ property to Full result set. Create an OLE DB connection to the database. Add the following T-SQL statement:

SELECT URL as BookListURL,Id as BookCategoryId,PreviousProcessedPageNumber as BookPreviousProcessedPageNumber
FROM URL_BookList

Click the ‘Result Set‘ option to the left. Set the Result Name to 0 and for the Variable Name, select User::BookListURLs. This variable will hold the results returned by the query. Click OK to close the editor.

Next,  add a ForEach Loop container and connect the Execute SQL  task to it. Open the editor and click Collection. In the enumerator drop down menu, select ForEach ADO Enumerator. In the ADO Object Source Variable drop down menu, select BookListURLs and for the Enumeration Mode, select Rows in the first table.

Set up Variable Mappings then click OK to close the editor:

To debug looping item value, you need to add a Breakpoint:

Now, execute the package. The package will stop and a watch window should open at the bottom of the screen. If it doesn’t, click Debug > Windows > Watch 1. In the watch window, add the names of the variables you want to monitor. Keep in mind that the variable names are case sensitive here.

I have a Integration Services Project:SSIS_UK_Projects contains the package LEI_Lookup.dtsx. But how I can run it from Stored Procedure?

1. calls catalog.create_execution to create an instance of execution for the LEI_Lookup.dtsx package.

EXEC [SSISDB].[catalog].[create_execution]
@package_name=N’LEI_Lookup.dtsx’,
@execution_id=@execution_id OUTPUT,
@folder_name=N’UK’,
@project_name=N’SSIS_UK_Projects’,
@use32bitruntime=False,
@reference_id=@env_reference_id

2 calls catalog.set_execution_parameter_value to set values for the parameter, and LOGGING_LEVEL parameters.

Select @execution_id
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N’LOGGING_LEVEL’,@parameter_value=1

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N’Client_code’, @parameter_value=@parameter1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N’Source_table’, @parameter_value=N’processing_hold’

3. calls catalog.start_execution to start an instance of execution.

EXEC [SSISDB].[catalog].[start_execution] @execution_id

I have an SSIS package with a Data Flow that import data from Flat File Source to ADO NET Destination. Output column type of Flat File Source was configured correctly. Input Columns type and External Columns type in ADO NET Destination is automatically generated. But I got below warning messages:

Warning: 0x802092A7 at DFT_import_json, ADO NET Destination Transaction [62]: Truncation may occur due to inserting data from data flow column “reduced_trade_units” with a length of 100 to database column “reduced_trade_units” with a length of 50.

I see the column length of External Columns in ADO NET Destination Input is different with Input Columns length. I manually changed the length to the same as Input Columns length in the Advanced Editor for ADO NET Destination. But It reverted to wrong length again once I closed and open it again. How can I solve it?

After long investigate, I find out it’s related with Destination property setting. By default ADO NET Destination property ValidateExternalMetadata‘s value is true and it will automatically update the external columns type. Need change this property value to false if we wan to manually set external columns type.

In some project we need to execution stored procedure in SSIS Execute SQL Task and want to capture the stored procedure’s return value. Can we do this in SSIS Execute SQL Task? How can implement it in SSIS?

The answer is yes. The Execute SQL Task configuration is depend on your stored procedure parameter setting and database connection type. For output parameter and stored procedure return value, the SSIS parameter config is different For OLE DB connection and ADO.NET connection config are different as well. In below example, I am using OLE DB connection.

Firstly, we need to create one stored procedure with both output parameter and return value:

create procedure sp_check_import_status @client_code varchar(10),@currenty_day varchar(8),
@output int output
as
begin
declare @result int
select @result = count(1) from transfer_history
where client_code=@client_code and day_transactions >= @currenty_day
set @output = @result
if @result = 1
begin
return @result
end
return 0
end

Secondly, need to config SSIS Execute SQL Task

Double click Execute SQL Task to open task editor. In the editor window, set ResultSet to None and SQLSourceType to Direct Input, input following statement to SQLStatement

exec ? = sp_import_oan_check_import_status ?,?,? output

Switch to the Parameter Mapping window, click Add to add the variables/parameters created in SSIS project and map the parameters with the stored procedure result as follows:

Note that the ParameterName values are numeric. You need to set the name starting from 0 according to the parameters occurrence sequence in the SQLStatement.