Need idea on exporting column data to separate file -ssis

  • 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

  • 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)

  • 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.

  • 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

  • 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 ..

  • 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

  • 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

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

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