CREATE TABLE Test
 (
 EDate Datetime,
 Code varchar(255),
 Cdate int,
 Price int
 ); 
 
drop table Test
 

 insert into Test
 values('2015-06-19','LOP',20150701,12)
  insert into Test
 values('2015-06-22','LOP',20150701,26)
  insert into Test
 values('2015-06-23','LOP',20150701,69)
   insert into Test
 values('2015-06-23','LOP',20150701,77)
   insert into Test
 values('2015-06-23','LOC',20150701,73)
  insert into Test
 values('2011-04-08','LOP',20110501,97)

  insert into Test
 values('2011-04-07','LOP',20110501,11)
  insert into Test
 values('2011-04-06','LOP',20110501,61)

  insert into Test
 values('2011-04-08','OBP',20110501,37)
    insert into Test
 values('2011-04-08','OBC',20110501,105)
  insert into Test
 values('2011-04-07','OBP',20110501,19)
  insert into Test
 values('2011-04-06','OBP',20110501,25)

  insert into Test
 values('2015-06-19','RXP',20150701,92)
  insert into Test
 values('2015-06-22','RXP',20150701,43)
  insert into Test
 values('2015-06-24','RXP',20150701,22)
   insert into Test
 values('2015-06-24','RXP',20150701,28)
   insert into Test
 values('2015-06-24','RXC',20150701,43)


   insert into Test
 values('2015-06-24','OHP',20150701,43)

   insert into Test
 values('2015-06-23','ONP',20150701,43)

I have this Query and the below output:

EDate Code CDate Price
2015-06-24  RX 20150701 22
2015-06-24  RX 20150701 28
2015-06-24  RX 20150701 43
2015-06-23  NG 20150701 43
2015-06-24  HO 20150701 43
2011-04-08  RB 20110501 37
2011-04-08  RB 20110501 105
2015-06-23  CL 20150701 69
2015-06-23  CL 20150701 77
2015-06-23  CL 20150701 73

 SELECT EDate,
  case when Code in ('LOP','LOC') then 'CL'
	  when Code in ('OBP','OBC')  then 'RB'
	  when Code in ('RXP','RXC') then 'RX'
	  when Code in ('OHP','OHC') then 'HO'
	  when Code in ('ONP','ONC') then 'NG'
	  Else 'CodeNotPresent' end Code,CDate,Price
 
  FROM Test t
 WHERE EDate = (SELECT MAX(EDate) FROM Test WHERE code = t.code)

Now the task is to create  SSIS package which will create different .txt file for each Code

1) RX20150624.txt

2015-06-24 00:00:00.000 RX 20150701 22
2015-06-24 00:00:00.000 RX 20150701 28
2015-06-24 00:00:00.000 RX 20150701 43

2) NG20150623.txt

2015-06-23 00:00:00.000 NG 20150701 43

3) HO20150624.txt

2015-06-24 00:00:00.000 HO 20150701 43

And so on..

But the requirement is to have a dynamic query where we can have more number of Codes or less number of codes and similarly the package should generate dynamic text files, one .txt file per code.

What is the best way to create a package which can meet the above requirement?

Implement Shredding The Recordset design approach to iterate over the codes and output to a file with a parameterized name. 

PS: Use pastebin or similar to avoid long code in forums

In my task i have to create seperate files and saved those in a particular folder...

The codes you can order by in the SQL code that retrieves the needed data for the dump.

Hi SyedF,

After testing the issue in my environment, we can refer to the following steps to achieve your requirement:

1. Create two variables as below:
   
   Drag an Execute SQL Task to the Control Flow Task, connect to the database with the following SQLStatement:
   ;with cte (EDate,Code,CDate,Price)   as(SELECT EDate,
     case when Code in ('LOP','LOC') then 'CL'
      when Code in ('OBP','OBC')  then 'RB'
      when Code in ('RXP','RXC') then 'RX'
      when Code in ('OHP','OHC') then 'HO'
      when Code in ('ONP','ONC') then 'NG'
      Else 'CodeNotPresent' end Code,CDate,Price
     FROM Test t
    WHERE EDate = (SELECT MAX(EDate) FROM Test WHERE code = t.code))
    select distinct Code+CONVERT(VARCHAR, EDate, 112) from cte
2. Change ResultSet property to Full result set.
3. Add the User::DistinctCode variable with 0 as Result Name in the Result Set tab.
4. Connect the Execute SQL Task to a Foreach Loop Container.
5. Select Foreach ADO Enumerator as Enumerator and select User::DistinctCode as ADO object source variable.
6. Add the User::Code variable in the Variable Mappings tab.
7. Drag a Data Flow Task inside the Foreach Loop Container.
8. In the Data Flow Task, drag an OLE DB Source connect to the database with SQL Command as below:
   ;with cte (EDate,Code,CDate,Price)   as(SELECT EDate,
     case when Code in ('LOP','LOC') then 'CL'
      when Code in ('OBP','OBC')  then 'RB'
      when Code in ('RXP','RXC') then 'RX'
      when Code in ('OHP','OHC') then 'HO'
      when Code in ('ONP','ONC') then 'NG'
      Else 'CodeNotPresent' end Code,CDate,Price
     FROM Test t
    WHERE EDate = (SELECT MAX(EDate) FROM Test WHERE code = t.code))
    select * from cte where Code+CONVERT(VARCHAR, EDate, 112)=?
9. Click 'Parameters..', set User::Code variable as a parameter with 0 as Parameters.
10. Create a Flat File Connection Manager, then navigate to the Expressions property.
11. Click the next to the Expressions property, add a ConnectionString property with Expression as below (supposing all output files store in C:\TESTING folder):
    "C:\\TESTING\\"+ @[User::Code] +".txt"
12. Drag a Flat File Destination connects to the OLE DB Source, then target the Flat File Connection Manager.

The following screenshots are for your references:

If there are any other questions, please feel free to ask.

Thanks,
Katherine Xiong

Hi Katherine,

Thanks for the reply. I am using a database where CTE doesnt work. Do you have equivalent of CTE which i can use for my database.

The version i m using is

Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

One more point i would like to add, Instead of where Code+CONVERT(VARCHAR, EDate, 112)=?, i need

where Code=?. The data inside the text file should be filtered only on the basis of Code, not on Code+CONVERT(VARCHAR, EDate, 112). Do i need to create a seperate Execute SQL task and For Each Loop to save Code and pass it to Data Flow Task Query.