asked
How to create a Dynamic SSIS package to generate muliptle weekly flat files
Hi I got a requirement that I should create SSIS package which when runs generates weekly flat files for the months July and August , i mean totally there are 8 weeks from july 1 to august 25, so thee package should generate 8 flat files once I run this SSIS package. I am using SQL Server 2005. Thank You
You can achieve this by processing the data in a loop. First you get a list of months you need to process. then in the For Each Loop you put the data flow, which will process the data for single particular month and write those data to a flat file. The Flat File destination will be configured in a way, that it will use an expression for the file name, where the name for particular month will be constructed.
Thanks for the reply Pavel, but I am pulling data into the notepad weekly but not monthly due to size limits of notepad. I usually use from and ToDates from a Table. My question is how to create these dates dynamically in the table and loop the ForLoopContainer until it loads data into 8 notepads that is 8 weeks when I execute the package once.
You can get list of the weeks using for example a query like the one below. You can put the query into Execute SQL Task, store the output record set into an object variable and then use that variable in For Each Loop Container (For Each ADO Enumerator). Inside the loop you will put Data Flow which will process data for particular week. Of course as I have mentioned you will have an expression on the Flat File destination to output the data to particular file (the file name you can construct from the information returned by the query below. DECLARE @startDate datetime, @endDate datetime; SELECT @startDate = '20110701', @endDate = '20110825'; WITH TallyWeeks AS ( SELECT TOP(DATEDIFF(week, 0, @endDate) - DATEDIFF(week, 0, @startDate) + 1) DATEADD(week, DATEDIFF(week, 0, @startDate) + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) -1, 0) AS WeekStart FROM sys.all_objects ) SELECT WeekStart, DATEADD(day, 6, WeekStart) AS WeekEnd, DATEPART(week, WeekStart) AS WeekNo FROM TallyWeeks WHERE WeekStart >= @startDate AND WeekStart
