5

What will be best way to handle a use case where-

  1. I have a old -db source containing 10 columns

  2. This source data need to go to three places with different fields from source

    • Excel 1 ( 5 fields from Source )
    • Excel 2 with different field than previous excel
    • SQL server table to with another combination of fields

Script component is used to choose column seems to be an option. Multicast does not provide ability to pick and choose specific column.

Please see picture for my solution. Need to know if there is other option to achieve it

Current Data flow Solution

Hadi
  • 36,233
  • 13
  • 65
  • 124
raj
  • 51
  • 2
  • I don't know that I quite understand your scenario. Could you click the edit button and provide some examples, a row or two of data from the Excel worksheets and where you're trying to land them? – billinkc May 09 '19 at 15:15
  • How do you want to pick those 5 fields? Top 5? Bottom 5? – Gen Wan May 09 '19 at 15:51
  • @Billinkc In data source has 10 columns and each file destination will will select mix and match of columns. File one for example will have column 1, 2 3...7 and file 2 will have column 4,5,6,...9 – raj May 09 '19 at 15:54
  • I added picture to the post so you can see my current plan – raj May 09 '19 at 15:57
  • If you don't want a column in a destination, don't map it. Removing a column will cost you more than doing nothing with it – billinkc May 09 '19 at 18:06
  • @raj did you checked the answer provided? – Hadi May 11 '19 at 07:28

2 Answers2

1

There are some tips that may helps:

Avoid Script Components

Instead of adding script components to select specific columns, in each OLE DB Destination, just don't map these columns.

Example:

enter image description here

image reference : how to assign a constant value to a column in oledb destination in ssis

Select specific columns in the OLEDB Source

If there are some columns in the OLE DB Source that wont be used in any of the destinations it is better to change the Access Mode and use SQL Command instead of Table or View and specify the columns needed in the Select query. As example, if the table contains 5 columns [Col1],[Col2], ... [Col5] and you only need [Col1],[Col2] use the following query:

Select [Col1],[Col2] From [Table]

Instead of of selecting the Table name

For more information:

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

There isn't a better approach than what you have. Instead of adding script components, In each OLE DB Destination, just don't map the columns that you don't want to use in that destination.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • Hi, thank you for your response. The destination with a different set of the column is a flat file. OLE DB destination is fine. For selecting different specific columns in flat file destinations, is there a way to do it without script component. Script component is just added as an intermediate step to exclude columns. – raj May 10 '19 at 18:00
  • https://learn.microsoft.com/en-us/sql/integration-services/flat-file-destination-editor-mappings-page?view=sql-server-2014 – Tab Alleman May 10 '19 at 19:16