none
Join three data sets from different data flows into one txt file RRS feed

  • Question

  • Hi, I was wondering how it is posible to join three data sets from different data flows into one txt file.

    Let's explain a little more:

    1. I have 3 dataflows. Each of them connect to sql server and and by a SQL command, they bring data into SSIS.
    2. Each SQL command differ between them. So each data set have different columns (they dont have the same format). Also the amount of columns differ between each one.
    3. What I need is to join the three data sets into one txt file. How can I do this? It is posible to join them with different data set formats into a txt file?
    4. Is this the best way to join different data? It is better to use as many OLE DB Sources are needed instead of different data flows?

    Thanks for your help!

     

     

    Sunday, March 9, 2008 3:19 PM

Answers

All replies

  • Yes, you can use a Execute Process task to copy the files together. If you check out this post, and look at the Method 2 I described, it shows how to do this. The article is talking about something a little different, but the concept is the same.

     

    http://agilebi.com/cs/blogs/jwelch/archive/2008/02/08/adding-headers-and-footers-to-flat-files.aspx

     

    • Marked as answer by Beli Wednesday, December 26, 2018 7:44 PM
    Sunday, March 9, 2008 5:34 PM
    Moderator
  •  

    Thanks for your answer jwelch. Do you think that this could be solved by some native SSIS functionality or only with this package that you told me?
    Sunday, March 9, 2008 5:54 PM
  • Everything described in that sample is "native" SSIS functionality, with the exception of the Data Generator source. The only reason I am using that is to quickly generate some sample data for the package. To actually apply the sample to a real life problem, you'd need to delete the Data Generator source and replace it with an OLE DB Source (the one you'd use to access SQL Server), a flat file source, or one of the other source components. Everything else is native, out of the box SSIS.

    Sunday, March 9, 2008 8:34 PM
    Moderator
  • have you investigated the merge join transformation?
    Monday, March 10, 2008 2:28 AM
    Moderator
  • Information security guys don't allow to run OS commands from SSIS. That's why This example doesnt work for me

    Does anyone solved this by a script or any other idea? Remember that I'm trying to join/merge 3 datasets into one. The three fo them are in different dataflows. They all end in text file and they need to be merged. Datasets have different width in bewteen.

    Any idea on how I can start looking for a solution?

    Thursday, May 8, 2008 8:09 PM
  • Go back to the example I provided and look at how the footer functionality is being handled. The example shows appending a footer by creating the footer in a Script Source. Instead of using a Script Source, just use a Flat File Source (set to read in the entire row as a single column) against your other file to write to the flat file destination in Append mode. No OS commands needed.

     

    Be aware, this will be slower than concatenating them through an OS command. Or you could write a script task, that uses IO.File class (as shown in Method 1 in that article) if it is OK to execute OS commands through .NET.

     

    Thursday, May 8, 2008 8:22 PM
    Moderator
  • Try this.

    http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx

     

    In your case you can use Union All Transformation instead of Merge Transformation.

    Thursday, May 8, 2008 8:31 PM