Mar 05, 2014 2 - Create a File Connection with the same dynamic path and delay validation to true 3 - Use a File Task to Delete the 'File Connection' pointing to the excel file in case of existing 4 - Use a Execute SQL Task and run a Create table. So that the excel file is created with the specified sheet on disk. Creating SSIS Packages dynamically from template. To build a SSIS package for every source file. To build these SSIS packages dynamically. You received flat files or text files or csv files in one of the source folder. You need to write an SSIS Package that should read the file columns and create table. Dynamic Flat File Connections in SQL Server Integration Services. Here is an example of creating a dynamic flat file connection in SSIS.

1) DFT task (Get distinct locations and respective table names) • Create first DFT to get distinct locations from file. Game Barbie Horse Adventure.

This post is a continuation of a previous and if you are new to ETL process using SSIS check it first to get more tips on how to solve most of the errors you might solve while working on this one. We will begin where we left off the previous blog and the requirement that we have to solve is we need to create excel file dynamically using ssis with some file name plus and the year month day to indicate when the report is created or the ETL executed. For Example “SalesPerson2013-10-13.xlsx” It is a good idea to always name your tasks and connections something that is meaningful so that you when you start to work hands of those task in a single project. Let us begin by renaming our source OLE DB Source to “SRC AdventureWorks” and our destination toe “DST Excel” Now let us create a variable to make our life easier. Change the variable Name to FileName in the variable property change EvaluateAsExpression=True and enter the Expression for your folder filename.xlsx For this exercise give the folder name the file name and year month and day as follow. Don’t forget to escape all the illegal characters in the file name by using backslash “ ” For our case the expression looks like this one.