none
how to load two flat files data into single sqlserver table?

    Question

  • Hi All,

    I have requirement like,I have two falt files which contains one column each.Now I have to load the files data into sqlserver table which should contains two columns.

    Ex:FlatFile1-->SID

    FaltFile2-->SName

    Table-->SID  SName

    Table should contain two columns as shown above without Null values.

    Can some one help me on this ASAp,Since it was urgent task for me.

    Regards,

    Sudha


    sudha

    Tuesday, February 25, 2014 7:25 AM

Answers

  • Hi Sudha,

    To combine the two columns together into a single SQL table, we need to create relationship between the two source files. To do this, we can store the source data in two staging table, and create an identity column in each staging table, and then join the two staging tables based on the identity column. So, we need two Data Flow Tasks (DFT) in the package, in the first DFT, we load the source data to the staging tables using two sets of Flat File Source/OLE DB Destination pairs. The queries to create the staging tables for the two flat file re as follows:

    CREATE TABLE Stgtbl1 (
      ID int identity (1,1),
      SID int
    )
    
    CREATE TABLE Stgtbl2 (
      ID int identity (1,1),
      Name varchar(20)
    )

    In the second DFT, we use OLE DB Source and OLE DB Destination. In the OLE DB Source, we use the following SQL command to implement the inner join:

    SELECT        Stgtbl1.SID, Stgtbl2.Name
    FROM            Stgtbl1 INNER JOIN Stgtbl2 ON Stgtbl1.ID = Stgtbl2.ID

    Regards,


    Mike Yin
    TechNet Community Support

    Tuesday, March 04, 2014 4:07 AM

All replies

  • Is that possible you have SID and do not  SName and opposite? 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 25, 2014 9:11 AM
  • How do you determine which SID value has to be merged with which SNAme value from both the files?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, February 25, 2014 9:17 AM
  • It is possible if you have a relationship between the two files 

    you can use a merge join to do this 

    please refer : https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-merge-join-transformation/


    --------------------------------------------------------

    Surender Singh Bhadauria

    My Blog

     

    Tuesday, February 25, 2014 2:11 PM
  • Hi,

    Thanks all for your replies.Here I don't have any relation between two files,I have tried using merge join,i was able to get two columns but getting null values.

    Regards,

    Sudha


    sudha

    Wednesday, February 26, 2014 9:36 AM
  • If you have no relations then the merge join will not be of any use for you

    how are you planning to relate which SID is corresponding to which SName because without that it will not be logical to dump the data by just rows


    --------------------------------------------------------

    Surender Singh Bhadauria

    My Blog

     

    Wednesday, February 26, 2014 1:01 PM
  • Hi Sudha,

    To combine the two columns together into a single SQL table, we need to create relationship between the two source files. To do this, we can store the source data in two staging table, and create an identity column in each staging table, and then join the two staging tables based on the identity column. So, we need two Data Flow Tasks (DFT) in the package, in the first DFT, we load the source data to the staging tables using two sets of Flat File Source/OLE DB Destination pairs. The queries to create the staging tables for the two flat file re as follows:

    CREATE TABLE Stgtbl1 (
      ID int identity (1,1),
      SID int
    )
    
    CREATE TABLE Stgtbl2 (
      ID int identity (1,1),
      Name varchar(20)
    )

    In the second DFT, we use OLE DB Source and OLE DB Destination. In the OLE DB Source, we use the following SQL command to implement the inner join:

    SELECT        Stgtbl1.SID, Stgtbl2.Name
    FROM            Stgtbl1 INNER JOIN Stgtbl2 ON Stgtbl1.ID = Stgtbl2.ID

    Regards,


    Mike Yin
    TechNet Community Support

    Tuesday, March 04, 2014 4:07 AM
  • Hi,

    Thanks all for your replies.Here I don't have any relation between two files,I have tried using merge join,i was able to get two columns but getting null values.

    Regards,

    Sudha


    sudha

    I didnt understand whole purpose of doing this then. If it doesnt have any relation, what sense will it make to merge them onto a row?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, March 04, 2014 4:29 AM