Category: SSIS

In the real project, we need to maintain lots of environments for different purpose, like testing server, staging server and production server . How can we deploy SSIS project to different server and run directly without any code change.

I am going to use DB Connection string as an example in this blog to show you how can we connect to different database without code change after project deployed.

1. Add new Connection Managers
2. Right click the new created connection manager then select ‘Parameterize…’ from popup list

3. change property to ‘ConnectionString’, select ‘Create new parameter’ in Parameterize window and enter the parameter name

4. Once you saved this configuration then you can see your saved parameter in Project Parameters windows

5. In Management Studio, expand the Integration Services Catalogs> SSISDB node in Object Explorer, and locate the Environments folder of the project for which you want to create an environment. Type a name for the environment and optionally a description, and then click OK.

6. Right-click the new environment and then click Properties to open properties window.

7. On the Variables page, do the following to add a variable:
Enter the variable name
Select the Type for the variable.
Enter the Value for the variable

8. Click OK to save your changes to the environment properties.

9. Under the SSISDB node in Object Explorer, expand the Projects folder, right-click the project, and then click Configure.

10. On the References page, click Add to add an environment, and then click OK to save the reference to the environment.

11. Right-click the project again, and then click Configure. In the Parameters tab on the Parameters page, click the browse button next to the Value field. Click Use environment variable, and then select the environment variable you created.

12. Click OK twice to save your changes.

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.

When we use SSIS to import files to Database, there is a very comm issues for most of user. How we can dynamically set the folder path and file filters for Foreach File Enumerator in a Foreach Loop. There are lots of website mentioned how to import different type files to database, how to loop folders and most of those website are hard code folder path and file filters. But for one of my project, I need to dynamically loop the folders and pick up file dynamically per parameter.

It is quite easy to implement the feature if you know the property Directory of the Foreach File Enumerator. Now let us look at the steps:

1. Create two SSIS package parameters CSVFileFilter and FolderPathTest2.

Create two variables CSVFileLists and CSVFileName

2. Create a Foreach Loop Container task to the Control Flow panel, double click it to pop up the property window.

3. Switch to the Collection tab, choose the Enumerator as “Foreach File Enumerator”, expand Expressions and add two properties “Directory” for folder path and “FileSpec” for filtering specific types of files. Specify “@[$Package::FolderPathTest2]” to Directory and ” @[$Package::CSVFileFilter]” to FileSpec.

4. Mapping the variable

5. Click OK to finished Foreach Loop Container configuration

6. Add a Data Flow Task in the Foreach Loop Container

7. Add a Flat File Source inside the Data Flow Task,configure Flat File Source

8. Assign variable @[User::CSVFileName] ‘Flat File Connection Manager’ ConnectionString property

9. Add ADO NET Destination then we can dynamically import file to table now.

In my SSIS project, there are lots of packages used the similar code and logic. So I just copied the component and changed the things that need to be changed. Then I got a very weird error :

at ScriptMain.CreateNewOutputRows()
at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers, OutputNameMap OutputMap)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)

After lots of search and investigation, I realized it’s SSIS component copy issue.

I followed Stefan’s steps and try to fix it. But it’s not working for mine. I recreated a new script component from the scratch.

There is ticket in Visual Studio Developer community as well, the last update at 06/02/2020 mentioned it’s a VS bug and still working on it.

https://developercommunity.visualstudio.com/content/problem/225039/ssis-copying-script-component-to-another-data-flow.html

Once SSIS packages deployed to production server, there are several way to execute it. Execute it by SQL Job or performing it from SQL script.

For most of case in my project need to execute SSIS package from SQL procedure. There are few challenges come up when executing SSIS package from SQL procedure.

By default start_execution is async function. The SQL procedure move to the following script once SSIS Package is called. But some of case we need to the data which processed in SSIS Package . In this case, asynchronous execution should be used.

The syntax to add SYNCHRONIZED parameter is shown below:

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

start_execution will return success message even if SSIS Package is failed. For most of case, the whole store procedure should be stopped once SSIS package execution failed. The workaround solution to solve this issue, we need to get package execution status flag from SSISDB then raise error. Code show as below:

IF 7 <> (SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id = @execution_id)
RAISERROR(‘Json file import exception, please contact support team’, 16, 1)