locked
SSIS Destination help RRS feed

  • Question

  • Good Day,
    I do have 6 Excel Sources and named as Source1,Source2,Source3,...,and destination is ".csv" file after the Transformations.
    Actually, at the destination, the data must be in the source order, i mean to say that, like... In the Destination CSV file, first Souce1 then Source2,Source3...
    Please provide me a useful solution.
    Thank you.

    Regards
    Raghu
    Friday, October 9, 2009 9:28 AM

Answers

  • Hi Raghu,
    Hope you remember the last post on the 6 source & 6 destination.

    Now you need the data from the 6 source to one csv file destination.
    After the UNION ALL, instead of the conditional split, use the SORT transform and sort by the column FileName as per my example.
    Once the data gets sorted you can put it to the csv file destination and it will be in the order as u require.

    for reference check this post where I had provided the screen shots.

    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/3b9cf6fa-6978-403f-9362-f37b41db3bc7

    Do remember that srting as a costly operation.


    Hope this helps !!
    Sudeep      My Blog
    • Marked as answer by RP Reddy Friday, October 9, 2009 1:06 PM
    Friday, October 9, 2009 11:47 AM

  • After every Excel source component use derived column for creating order_no (new column), in the derived column for first source give order_no as 1 in the same way for second source order_no as 2....  apply the same logic for all sources. Now you can use 'Union all' transformation for combining all the input data along with order_no, after that take 'Sort' transformation. select order_no in the sort component, then finally connect to your flat file destination. The data must be sorted based on order_no means according to your sources.
    Lakshman
    • Proposed as answer by Murty Addanki Friday, October 9, 2009 10:45 AM
    • Marked as answer by RP Reddy Friday, October 9, 2009 1:06 PM
    Friday, October 9, 2009 9:59 AM
  • If you aren't using a For Each Container and you have 6 Excel sources in your Data Flow Task (DTF) then you should follow Lakshmans steps.

    So:

    1) Create 6 EXCEL SOURCE
    2) For each source, create a DERIVED COLUMN transformation to make a Sort column with the order value
    3) create a UNION ALL transformation to take all the sources to a single data set.
    4) create a SORT transformation to order your dataset on the Sort column.
    5) create a flat file destination to output the rows to


    every day is a school day
    • Marked as answer by RP Reddy Friday, October 9, 2009 1:06 PM
    Friday, October 9, 2009 10:12 AM
  • Good Day,

    Yes, I got exactly what i wanted.But, the column which i add, is placed at the last column, i need that column at the First column of the table.Is it possible,please tell me the solution.
    Thank you.

    For that you have to recreate the table with the new column as the first column.
    No other way AFAIK.
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Marked as answer by RP Reddy Friday, October 9, 2009 1:33 PM
    Friday, October 9, 2009 1:18 PM

All replies

  • I think that the files will be picked in alphabetical order if you use a for each loop container, so here you don't need to do anything but to put a for each loop container around your package. The files will be picked as they are named i.e., Source1, Source2......

    Regards.
    Friday, October 9, 2009 9:36 AM
  • Yes,
    coderbyfate is correct if you are using for each loop and you are processign the excel source files individually inside DFT.
    But if you want to have all the 6 excel files inside DFT at same time, then I am not sure if you will be able to get the records in source order in the destination table/file .
    Nitesh Rai- Please mark the post as answered if it answers your question
    Friday, October 9, 2009 9:54 AM

  • After every Excel source component use derived column for creating order_no (new column), in the derived column for first source give order_no as 1 in the same way for second source order_no as 2....  apply the same logic for all sources. Now you can use 'Union all' transformation for combining all the input data along with order_no, after that take 'Sort' transformation. select order_no in the sort component, then finally connect to your flat file destination. The data must be sorted based on order_no means according to your sources.
    Lakshman
    • Proposed as answer by Murty Addanki Friday, October 9, 2009 10:45 AM
    • Marked as answer by RP Reddy Friday, October 9, 2009 1:06 PM
    Friday, October 9, 2009 9:59 AM
  • Nitesh,

    You know i,am new to SSIS, can you please explain me bit of clearly.Yes, i want all the sources inside the DFT at the same time and at the Destination, data must be like Source1 after that Source2, then Source3...
    Thank you.

    Regards
    Raghu
    Friday, October 9, 2009 10:01 AM
  • If you aren't using a For Each Container and you have 6 Excel sources in your Data Flow Task (DTF) then you should follow Lakshmans steps.

    So:

    1) Create 6 EXCEL SOURCE
    2) For each source, create a DERIVED COLUMN transformation to make a Sort column with the order value
    3) create a UNION ALL transformation to take all the sources to a single data set.
    4) create a SORT transformation to order your dataset on the Sort column.
    5) create a flat file destination to output the rows to


    every day is a school day
    • Marked as answer by RP Reddy Friday, October 9, 2009 1:06 PM
    Friday, October 9, 2009 10:12 AM
  • Hi Raghu,
    Hope you remember the last post on the 6 source & 6 destination.

    Now you need the data from the 6 source to one csv file destination.
    After the UNION ALL, instead of the conditional split, use the SORT transform and sort by the column FileName as per my example.
    Once the data gets sorted you can put it to the csv file destination and it will be in the order as u require.

    for reference check this post where I had provided the screen shots.

    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/3b9cf6fa-6978-403f-9362-f37b41db3bc7

    Do remember that srting as a costly operation.


    Hope this helps !!
    Sudeep      My Blog
    • Marked as answer by RP Reddy Friday, October 9, 2009 1:06 PM
    Friday, October 9, 2009 11:47 AM
  • I would go with the process Dorababu proposes also

    This was asked somewhere else and I wrote this as the answer.  The flow seemed lengthy enough to write the entire process
    http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/derived-column-and-the-data-flow-task

    This would be enhanced by small changes as outlined in the other replies to fit well and efficient for everything I think
    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    Friday, October 9, 2009 12:20 PM
  • Good Day,

    Yes, I got exactly what i wanted.But, the column which i add, is placed at the last column, i need that column at the First column of the table.Is it possible,please tell me the solution.
    Thank you.

    Regards
    Raghu
    Friday, October 9, 2009 1:06 PM
  • I may be mistaken but are you asking how you would go about chaning the ordinal column position in the table itself?  You can achieve that in the design of the table but there is no functional gains out of storing them in any order.  It also doesn't matter which order they are in the mappings as long as they are mapped correctly

    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    Friday, October 9, 2009 1:12 PM
  • Good Day,

    Yes, I got exactly what i wanted.But, the column which i add, is placed at the last column, i need that column at the First column of the table.Is it possible,please tell me the solution.
    Thank you.

    For that you have to recreate the table with the new column as the first column.
    No other way AFAIK.
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Marked as answer by RP Reddy Friday, October 9, 2009 1:33 PM
    Friday, October 9, 2009 1:18 PM
  • Good Day,

    I got it. Thank you

    Regards
    Raghu
    Friday, October 9, 2009 1:34 PM