locked
Dynamic column support in Bulk Load RRS feed

  • Question

  • Hi,

    Is there any solution for the following scenario in bulk insert. 

    "The csv file which is going to get processed for a particular table will change its column order periodically , and sometimes some columns will be deleted and sometimes some new columns will be added . I need to map corresponding column value from csv to table when the  order changes or values deleted or new values added"

    Hope you will get ?

    ~Selva

    Wednesday, March 11, 2015 5:29 AM

Answers

  • No.

    If I understand your description correctly, I think you are best off writing a custom program to run the import which can parse the file. This is far beyond what BCP is designed for.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, March 16, 2015 12:49 PM

All replies

  • How do you determine which column corresponds to what? Are you sure if column names are atleast consistent?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, March 11, 2015 6:01 AM
  • yes, coulmn names are consistent but the order alone might get changed and the new column may comes in . Is there any solution ?
    Wednesday, March 11, 2015 8:57 AM
  • In the format you can define the column order, so you would have always modify your format file; see Non-XML Format Files (SQL Server) => "Server Column Order"

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, March 11, 2015 9:13 AM
  • Hi,

    I think this will support only when the column order changes . Suppose what happens when new column comes in the data file .

    Selva

    Wednesday, March 11, 2015 9:59 AM
  • Then you have to modify the format file again.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, March 11, 2015 10:05 AM
  • Is there any other solution?

    Wednesday, March 11, 2015 10:10 AM
  • Is there any other solution?

    Nope

    Other than using script task to build a data flow programtically

    see example here

    http://mahiways4dotnet.blogspot.in/2005/10/how-to-programmatically-create-ssis.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, March 11, 2015 10:44 AM
  • If csv file order gets changed means  it's like we use to keep on changing the format file.Thats why i asked any solution like instead of mapping column order mapping column name from sql to data header name in csv file.

    ~Selva

    Wednesday, March 11, 2015 10:51 AM
  • What is missing from your posts is that you don't tells how you know that the column order has changed. Does the file have a header? Is there a separate message that gives the column order? Is it a purely a manual process where someone sends a mail?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, March 11, 2015 12:00 PM
  • Hi,

        CSV files will be given by third party organization . They use to keep changing the order(not often) order of the column . I have read about the non-xml format in which we use to map based on column position . Is there way by mapping column name in non-xml to csv file column name

    With Regards,

    Selvam.M

    Monday, March 16, 2015 10:43 AM
  • No.

    If I understand your description correctly, I think you are best off writing a custom program to run the import which can parse the file. This is far beyond what BCP is designed for.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, March 16, 2015 12:49 PM