Results 1 to 10 of 10
  1. #1
    Join Date
    May 2010
    Posts
    52

    Unanswered: Multiple Flat Files to different SQL Tables (SSIS)

    so we are running SQL 2000 and we use a DTS Package to load about 100 txt files to different tables in a SQL DB.
    so in DTS packages you define the connection (database) and the different source files will connect to the connection. you define the destination on the flow arrow.
    My question is how do i accomplish this in SQL 2008 using SSIS. (upgrading SQL).
    thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Personally I would be using BCP or BULK INSERT which allow you to simply use "Execute SQL Tasks".

    Hope this helps
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Mosdef? Some friends of mine saw you at the 9:30 club a day or 2 ago. I had no idea you had a day job.

    I am not as big of an SSIS hater as I once was. Does it have it's quirks and inconsistencies and gotcha's? Sure, but that is why people pay us. I have come to realize I hate linked servers more than SSIS, so if I am dealing with multiple datas ources across many servers and I am doing ETL (more E and L and not so much the T), SSIS is an option.

    As to how to accomplish this task? This is a very general and broad question. I would suggest that you do the tutorials in SQL Server Books Online, and if you have any specifc questions or problems, come back here and ask more specific questions.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I'm still a fan of linked servers and openrowset, but if the number of text files is predefined and the structures of those text files are finalized and don't change often, - sure, SSis is fine. But when using it I prefer configuration files over embedded values of connection strings. Gives you a lot more flexibility and control.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Neh. Linked servers are perfomance headaches without end when you are trying to work multiple datasources.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It always depends.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    nice. you got me.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    OK, I'll clarify. When you're saying "Linked servers are performance headaches without end..." Are you talking about every type of linked server that you can create from SQL? If not, then, - you got me, they ALL are truly a performance headache, because you dealt with one type, and based on your bad experience just decided to generalize them all as headaches. Cool!

    In short, and still valid - it always depends
    Last edited by rdjabarov; 02-22-12 at 12:41. Reason: Conclusion
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Ok I'll clarify. I thought you referring to my recent comments made here to Rudy about never saying "Always" and "Never".
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Nah, just referring to linked servers
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •