Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSIS Package for data migration issue_urgent help..

Answered SSIS Package for data migration issue_urgent help..

  • Tuesday, November 20, 2012 6:26 PM
     
     
    Sign In to Vote
    0

    Hi...

    I am migrating data from flat file source to database using ssis

    The flat file is like:

    Column 0 Column1 Column2 Column3
    B:Ferrari
    C:Fiat
    D:BMW
    E:Hyundai
    Quantity 12345 2345 1234
    Amount 567 456 444
    Loss 234 876 444
    Profit 12345 66666 9897

    From the flat file above the data is needed to load in database in the below manner:

      • Parse the row that starts with “B/C/D:”, column 0
        1. New Column “Name”.......................in database
        2. Take the value after the “:”.............for database
    1. Parse the row that starts with “Quantity”, column 0
      1. New Column “Qty”
      2. Take the value in Column 2
    1. Parse the row that starts with “Amount”, column 0
      1. New Column “Amt”
      2. Take the value in Column 3

    and so on..

    How to do the above using SSIS. Can it be done through pivot operation??

    Please anyone suggest solution.Its very urgent....

    j

     

All Replies

  • Tuesday, November 20, 2012 9:05 PM
     
     
    Sign In to Vote
    0
    I think the easiest way is to parse the flat file with .NET in a script component as a source.

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

     
  • Tuesday, November 20, 2012 9:09 PM
    Moderator
     
     
    Sign In to Vote
    0

    Break this file apart into two files.

    Then load each one into a separate staging table and finally create a proper SQL join or make multiple Execute SQLs in the SSIS package to further transform the data.

    You would need to use a Conditional Split transform to split the file apart based on a condition, perhaps the record length.

    I am just not sure about the keys for the unpivot transformation.

    Arthur My Blog

     
  • Wednesday, November 21, 2012 12:31 AM
     
     
    Sign In to Vote
    0

    Not Very clear what the output will be, I would suggest to show in a tabular form the desired result.

    I would use a conditional split to separate the columns that start with A/B/C on one branch and the rest in another branch. Then use script component destination.

     
  • Wednesday, November 21, 2012 4:00 AM
     
     
    Sign In to Vote
    0
    I am new  to ssis . Can you tell me how to do and what .NET script task code to be written..

    j

     
  • Wednesday, November 21, 2012 4:02 AM
     
     
    Sign In to Vote
    0

    Hi Arthur,

    Since i am new to ssis can you be more clear for your solution..

    Can you please tell me stepwise..

    j


    • Edited by j_aer Wednesday, November 21, 2012 4:03 AM
    •  
     
  • Wednesday, November 21, 2012 4:09 AM
     
     
    Sign In to Vote
    0

    Hi Fanor,

    The table will be like:

    Name Qty Amt
    Ferrari Column 2 value from flat file source Column 3 value from flat file source
    Fiat Column 2 value from flat file source

    Column 3 value from flat file source

    and so on.....

    Please elaborate how to do with conditional split.If not then what custom code can I use for this??

    j

     
  • Wednesday, November 21, 2012 6:49 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi Arthur,

    Since i am new to ssis can you be more clear for your solution..

    Can you please tell me stepwise..

    j



    here http://consultingblogs.emc.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx is a very good post that demonstrates how a file can be split into chunks conditionally.

    Arthur My Blog