Dynamically extract data from Sql table into flat file

Question

Hi all,
I’m new to SSIS and had a question.
I have a table with fields ID, Filename, Filecontent.
I want to give ID and DestinationFolder as input and the output should be all flat files with ID greater than or equal to the input with the Filecontent and Filename as the actual file name of the flat file.
Eg: Id , Filename, Filecontent => (1, abc, ‘how are you?’), (2, def, ‘I am good,) , (3, ghi, ‘Good to know’)
Input: ID = 1 , destination folderpath: ‘C://‘
Output :
abc.txt (content in the file: ‘how are you?’)
def.txt (content in the file: ‘I am good’)
chi.txt (content in the file: ‘good to know’)

Please let me know if it’s possible in SSIS or if my question is unclear.
Thanks you very much!

Answer 1

Hi @Prajakta Sonawane ,

What about create a store procedure in the database first and then run the query via SSIS?

create table test0922( id int, Filename varchar(max), Filecontent varchar(max))  
insert into test0922 values  
(1, 'abc','how are you?'), (2, 'def', 'I am good') , (3, 'ghi', 'Good to know')  
  
  
CREATE PROC outputdata   
@id INT   
AS  
SELECT Filename+'.txt(content in the file:'+Filecontent+')' as filepatht FROM test0922  
WHERE id>=@id  
GO  

And the you may store query to variables to pass the parameter like shown below.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.