Need idea on exporting column data to separate file -ssis

Question

Need idea on exporting column data to separate file -ssis

JoNTSQLSrv

Default port

Points: 1420
More actions
February 17, 2018 at 5:04 am

#333602
Hi all,
I need a help to generate XML file from a table output and Create main folder and subfolder to put those files dynamically using table output.
consider below script as table output
create table #master_table(Xml_out varchar(max),[Type] varchar(50),Folder_no int,Xml_name varchar(100) )
insert into #master_table(Xml_out,[Type],Folder_no,Xml_name) select '<1></1>' , 'BANK',1,'BANK_1' union all select '<2></2>' , 'BANK',1,'BANK_2' union all select '<3></3>' , 'BANK',2,'BANK_3' union all select '<4></4>' , 'BANK',3,'BANK_4' union all select '<5></5>' , 'BUSINESS',1,'BUSINESS_1' union all select '<6></6>' , 'BUSINESS',1,'BUSINESS_2' union all select '<7></7>' , 'BUSINESS',2,'BUSINESS_3'
select * from #master_table -- consider this as query out put
Consider below as query out . I have to generate xml file from column xml_out for each row with it xml_name, and create respective folder to put it.

2 main folder BANK and BUSINESS ( because select distinct([type]) from #master_table )
and 3 subfolder inside BANK like this d:\BANK\1 & d:\BANK\2 & d:\BANK\3
like wise 2 subfolder inside BUSINESS like this d:\BUSINESS\1 & d:\BUSINESS\2)
which is based on query out.
so d:\BANK\1 will have BANK_1.xml and BANK_2.xml files
and
d:\BANK\2 will have BANK_3.xml and BANK_4.xml files
like wise
d:\BUSINESS\1 will have BUSINESS_1.xml and BUSINESS_2 .xml files
and d:\BUSINESS\2 will have BUSINESS_3.xml files
In microsoft site , i found below code , but i dont know c sharp , dont know how to inclue this in script task .. and dont know if it will really wil help
```
public override void Input0_ProcessInputRow(Input0Buffer Row)
 {
 string fileName = string.Format("{0}_{1}_{2}.xml",Row.ChannelSequenceNumber,Row.ChannelDesignator,Row.Id);

 using (System.IO.FileStream fs = new System.IO.FileStream(string.Format(@"{0}\{1}",this.Variables.filePath,fileName),System.IO.FileMode.Create))
 using (System.IO.StreamWriter sw = new System.IO.StreamWriter(fs))
 {
 sw.Write(System.Text.Encoding.Unicode.GetString(Row.Body.GetBlobData(0, (int)Row.Body.Length)));
 sw.Close();
 }
 }
```
How to do it in ssis.
I tried with bcp and EXEC xp_cmdshell but i dont have persmission to use xp_cmdshell , they wanted me to use script task or via ssis component .. i dont know c sharp .. so please help me

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Heh... they won't allow you to use xp_CmdShell because they think it's a security risk (it's patently NOT when used correctly) but they will let you call a C# script from SSIS? Fascinating.

Perhaps you can do it through the command line and still be compliant with their (mostly incorrect) perception of security. Please see the following article with the understanding that I do use xp_CmdShell for such things and so haven't actually tried to do what you ask from SSIS.
https://sqlserverrider.wordpress.com/2013/01/03/executing-msdos-commands-in-ssis/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tim.ffitch 25252 SSCrazy Points: 2055 More actions · Answer 2

You could achieve this in SSIS without any C# coding. This is a rough outline of what is involved.
Use the Execute SQL Task to get a Distinct List of Types and set the option to create a Full Result Set.
Use a For Each Loop Task set to Foreach ADO Enumerator, create package variables to map to the fields in the Result Set
Create a File System Task inside the Foreach Task to create the directories from the data you can now read from the variables, you can use an Expression Task to help build folder and file paths.
Add a Data Flow Task to the Foraech Task
Add an OLE DB Data Source to read the data for the file using parameters to filter for the current Type and Folder Number and use FOR XML in your SQL query to output to XML
Add a Flat File Destination to the Data Flow using the variable data to set the file name.

JoNTSQLSrv Default port Points: 1420 More actions · Answer 3

tim.ffitch 25252 - Monday, February 19, 2018 4:36 AM
You could achieve this in SSIS without any C# coding. This is a rough outline of what is involved.
Use the Execute SQL Task to get a Distinct List of Types and set the option to create a Full Result Set.
Use a For Each Loop Task set to Foreach ADO Enumerator, create package variables to map to the fields in the Result Set
Create a File System Task inside the Foreach Task to create the directories from the data you can now read from the variables, you can use an Expression Task to help build folder and file paths.
Add a Data Flow Task to the Foraech Task
Add an OLE DB Data Source to read the data for the file using parameters to filter for the current Type and Folder Number and use FOR XML in your SQL query to output to XML
Add a Flat File Destination to the Data Flow using the variable data to set the file name.

thanks a lot tim for ur wonder idea. it helped me creating folder n files .. Thanks

JoNTSQLSrv Default port Points: 1420 More actions · Answer 4

Jeff Moden - Saturday, February 17, 2018 1:09 PM
Heh... they won't allow you to use xp_CmdShell because they think it's a security risk (it's patently NOT when used correctly) but they will let you call a C# script from SSIS? Fascinating.
Perhaps you can do it through the command line and still be compliant with their (mostly incorrect) perception of security. Please see the following article with the understanding that I do use xp_CmdShell for such things and so haven't actually tried to do what you ask from SSIS.
https://sqlserverrider.wordpress.com/2013/01/03/executing-msdos-commands-in-ssis/

thanks jeff, yes they dont allow xp shell cmd ... let me try the link u gave .. thanks ..

ManicStar SSCoach Points: 17992 More actions · Answer 5

JoNTSQLSrv - Monday, February 19, 2018 11:58 AM
Jeff Moden - Saturday, February 17, 2018 1:09 PM
Heh... they won't allow you to use xp_CmdShell because they think it's a security risk (it's patently NOT when used correctly) but they will let you call a C# script from SSIS? Fascinating.
Perhaps you can do it through the command line and still be compliant with their (mostly incorrect) perception of security. Please see the following article with the understanding that I do use xp_CmdShell for such things and so haven't actually tried to do what you ask from SSIS.
https://sqlserverrider.wordpress.com/2013/01/03/executing-msdos-commands-in-ssis/
thanks jeff, yes they dont allow xp shell cmd ... let me try the link u gave .. thanks ..

You tried an Export Column transformation? Basically feed the column w/the output data to this task, send the output to a file

JoNTSQLSrv Default port Points: 1420 More actions · Answer 6

ManicStar - Monday, February 19, 2018 12:03 PM
You tried an Export Column transformation? Basically feed the column w/the output data to this task, send the output to a file

Thanks Manic. It sounds great. Let me try your idea