One time load of data into sql server 2012

SSIS and Import/Export
1

I have an excel spreadsheet 2013 that I want to load data into sql server 2012 for a one time load. I am going to run this load on a test server first and then into production for real later. The table that will be loaded will exist long enough to transfer the data to other permanent tables. Thus I am trying to determine what is the best way to load the data. I am going to list options and ask you to tell me what option is the preferred method and why you chose that option. Based upon the option you chose, would you give me detailed directions on how to accomplish my goal of loading data to sql server 2012?

The choices are:

  1. create an ssis package and save that to the database.

  2. create a package and save it to the file system.

  3. Open up excel 2013 and have it load data to sql server 2013.

  4. Some option I am not aware and you know exists.

2

If it's just a straight data load, meaning no transformations, etc:
4. I would just use the import wizard in SSMS.

As far as detailed instructions, I don't have those. Just right click on the database and navigate through the import wizard.

2 Likes
3

One option I use - but its a String & Gum solution, so may/may NOT!! be appropriate in your case - is to add a column to the XLS that uses some string-handling to massage the relevant XLS columns of data into the syntax for an INSERT statement (or a UNION ALL SELECT) which can then be used (e.g. Cut & Paste into SSMS) to populate a temporary table, say, and then from there you can write some SQL that INSERTS into the real table - having, first, made whatever sanity checks are necessary to avoid SNAFU etc - which I'm sure you already have in mind, or in hand :smile :sunglasses:

1 Like
4

Can you tell me how to use the file system option? I am told that I will not have permission access to save the file to the sql server database and that is why I want the file system option.

5

All those sorts of complications, most especially where I do not regularly create such things (e.g. I am accessing a Client's server for which I do not have God permissions) is exactly why I use a String & Gum approach ...

Of course if you want a regularly re-usable "job" then setting it up properly would be the way to go. My job involves at least as many one-offs as reusable-job creations