1

My requirement is to produce one single flat file from three different datasets that should have timestamp value in the name of the file. All three datasets are from SQL server and using SQL query to take these data from source. Source type is OLEDB Source.

DataSet 1: This is a header dataset with just one single row which has different columns. First two columns are hardcoded and timestamp calculated dynamically. 

+--------+----------+----------------------+--------------------+
| FileID | FileType | SourceBeginTimestamp | SourceEndTimestamp |
+--------+---------------------------------+--------------------+
|    1   |    TXT   |   Time SSIS starts   |   Time SSIS Ends   |

DataSet 2: Dataset that contains several columns with data. Total rows are typically 1000 to 10000. 

+---------+---------+---------+---------+---------+
| Column1 | Column2 | Column3 | Column4 | Column5 |
+---------+---------+---------+---------+---------+
|         |         |         |         |         |

DataSet 3: Footer dataset contains the total number of rows including Dataset1, Dataset2 and dataset3. Below example -considering Dataset2 has 1198 rows. 

+-----------+----------+
| TotalRows | FileType |
+-----------+----------+
|    1200   |    txt   |

What I've done So Far: I used three different Dataflow tasks and flat file connection managers for each dataset. Using an expression to dynamically create the flat file names with timestamp without Seconds in it. This way, when i run the package, it'll create only one file and dump all three datasets into one single file.

The problem is, when the package runs in middle of a minute change, then there will be two files.

Expected Result: I want to combine these three flat files into one single flatfile with file name like SampleText_YYYYMMDDHHMISS.txt. I'm trying a way to use the File System Task or have the DFT to dump the data into rowset and then use script task to dump all the data into one flat file.

The output file will not have the headernames (ColumnNames)

Please suggest the best way to do it.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
gopi nath
  • 186
  • 3
  • 15
  • 1
    Have you looked at the Union All or Merge Join Transformations? Not sure which you need, as it *appears* your data has different numbers of columns, so I assume you want Merge Join, rather than Union All. – Thom A Sep 18 '19 at 14:15
  • i don't think merge join, merge or union all will help as the data are all different on each dataset and the columns are not the same. – gopi nath Sep 18 '19 at 15:01
  • But if you want them in the same file, then they need to be the same dataset... One of those transformations is the right choice for what you're after, as those are your 2 options. – Thom A Sep 18 '19 at 15:06
  • @Larnu that's not exactly true. A flatfile doesn't HAVE to conform to a specific format. There's no reason a .txt file can't have Header, Data, Footer all with different datashapes. – Tab Alleman Sep 18 '19 at 15:07
  • True, @TabAlleman but considering the OP is trying to output a dataset to a file, using a DataFlow, then that is the only option they would have as SSIS *does* require the data for conform to a specific format. – Thom A Sep 18 '19 at 15:09
  • @Larnu that's true if a single dataflow is used, but the way I read the question, the OP expects to use 3 different DFTs each with different metadata, and wants to stack the 3 resulting datasets on top of each other in the same file. No reason it can't be done. It just can't be done ONLY with dataflows. – Tab Alleman Sep 18 '19 at 15:11
  • The "What I've done so far" section should contain the way you specify the filename. I suggest to assign the filename to a variable in an "expression task" prior to the file access. This way, the filename can't change between the operations. – Wolfgang Kais Sep 18 '19 at 15:12
  • I see, @TabAlleman, I read *"I want to combine these three flat files into one single flatfile"* as meaning that the OP wants to coimbine the datasets into one. Might be more clear if the OP showed us what the end result is meant to look like. – Thom A Sep 18 '19 at 15:13
  • @Larnu I'm not using single DFT. I'm currently using 3 different DFT for different dataset. Each dataset has different columns. – gopi nath Sep 18 '19 at 16:33
  • @WolfgangKais, Currently i'm using a expression inside the Connection manager file for connection string. It dynamically creates the file name with current timestamp. – gopi nath Sep 18 '19 at 16:34
  • @gopinath That's what I thought, and that's why I suggested to compose the filename into a variable only once. You can still use that variable in the expression inside the connection manager. – Wolfgang Kais Sep 18 '19 at 16:39

1 Answers1

0

Instead of having your dataflows populate three separate flatfiles, have them populate 3 object-type package variables.

Then have your script task create a single flat file, and write to the file from the 3 variables in the desired order.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • can you please share any sample for the script task component to do this ? – gopi nath Sep 18 '19 at 15:07
  • No I'm afraid I don't have any sample of doing this handy. I would start by googling `SSIS Script populate file from object variable` – Tab Alleman Sep 18 '19 at 15:08
  • It's not a "Script Task Component" @gopinath . You have Script Tasks, which are used in the Control Flow, and Script Components, which are used in the Data Flow (and can be a Source, Destination or Transformation). Although similar they have different uses. – Thom A Sep 18 '19 at 15:15