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
The SSISDB database is automatically created when we create SSIS catalog. All the SSIS projects, packages and execution history are saved in this database. So if you have a package that run every few minutes you could see the database size is growing exponentially. I deployed one project at AWS EC2 server with limited disk storage size (100GB SSD), the package was automatically executed every 3 minutes by SQL Agent Job. After one weeks later, I noticed the disk size almost full. After investigation, I notice the size of SSISDB is very big already. How can I control SSISDB to a minimum size?
SSIS created a maintenance job already when we create the catalog, it’s SIS Server Operation Records Maintenance.
Looking the job steps, you will notice it calls a stored procedure internal.cleanup_server_retention_window, in this stored procedure you will see it is using catalog.catalog_properties to do database maintain.
Now is solution is easy, I just need to update RETENTION_WINDOW property of catalog_properties .
After this change, then just waiting for SSIS Maintenance Job to do database cleaning task.
If you don’t want to do this via T-SQL. You can right click on your catalog then click Property to change settings from Catalog Properties window:
After you reduced Database size, you maybe will notice the log file size is going up now. For the log file, if it’s not very critical system, you can used DBCC SHRINKFILE to shrink files directly:
USE SSISDB; GO –use below script to find ssisdb log file id –SELECT file_id, name
FROM sys.database_files; — Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE SSISDB SET RECOVERY SIMPLE; GO — Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (2, 1); GO — Reset the database recovery model. ALTER DATABASE SSISDB SET RECOVERY FULL; GO
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:
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)