Post reply

SSIS or VB code?

  • May 6, 2009 at 9:18 am

    #200182

    I am on a project where I need to upload a few hundred thousand records into a SQL 2005 db and it has 7 different tables and about 100 columns in one bulk load. I haven't used SSIS much at all, but used to use DTS in 2000 frequently.

    My question is really what people would suggest for a task like this. I want this to be somewhat simple to maintain in the event that I can no longer help the client. I know I could write some VB.NET code for this, but would SSIS be the way to go? Also, can I create an ASP page where the end-user could trigger the SSIS event? It's been a while and I am a little rusty!

    Thanks in advance.

  • May 6, 2009 at 9:25 am

    #989655

    The answer to your question is YES. SSIS is perfect for this kind of thing. Especially since you have the "fast insert" (bulk) mode for large amounts of data. The only other way to get that is to write your own provider or call it through a SQL Server BULK INSERT or BCP command.

    And yes, you can create a webpage where an end user can initiate a process to execute an SSIS package. In fact you can create SOAP endpoints in SQL Server, mapped to stored procs that handle job execution and batching for your SSIS package using SQL Agent. Don't even need IIS to make it work.

  • May 6, 2009 at 9:30 am

    #989662

    Very cool. Thanks for the tips. I am going to create a dummy .xls today and toy around with this. Should save me some time and keep it simple for the company. I appreciate the help and I will post back what I find out, or maybe pop in again if I get stuck on something.

    Thanks

  • May 6, 2009 at 10:47 am

    #989725

    One thing I failed to mention is that these 7 tables are relational. Does that complicate things in SSIS? Thanks

  • May 6, 2009 at 10:51 am

    #989727

    Not really. You can either set up a process flow in SSIS to insert the data in the order that its supposed to exist or set a flag to tell it to ignore constraints.

    Obviously in an optimal fashion you would do both, since constraint checking on the insert can decrease performance, and you'll want to handle the process in such a way that you can stop and restart without breaking data integrity.

  • May 6, 2009 at 10:53 am

    #989730

    Thanks Jeremy. Will give it a try!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply