December 10, 2012 at 8:05 am
I have a couple of situations with text files. Some have headers, some do not. I am tasked with moving processes that run in Access to Sql. A contractor that was on site kept insisting that i use bcp. When I told him my concern was making sure that columns were mapped properly (plus some files have columns that aren't needed and they are not always in the same order) all he told me was headers are irrelevant. He didn't explain where his thinking was on the process or if the notion was to bcp to another table then move that into a properly formatted table from which the data is brought up to snuff.
I had looked at a site that talked about running SSIS from a stored procedure, but haven't been able to fully absorb it. Plus I'll have to google for it again since I had to restart my computer this morning.
Aside from using SSIS, how do I ensure that bcp maps the right column in a text file to the right column in a table?
I've done Access development for years and am working to equate what I understand in Access to how I need to approach it in sql.
Thanks.
December 10, 2012 at 8:09 am
My advice would be to stick with SSIS. For someone with an Access background, SSIS would be a much friendlier road to travel than bcp. Far simpler to map your columns, choose which to import and which to ignore, etc. That's my 2¢.
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
December 10, 2012 at 6:01 pm
I would use the import / export wizard. It creates a very basic SSIS package that can be saved to file. You select a source, you select a target, you can change the mappings, and then you can execute it.
import / export wizard (ss2012):
http://msdn.microsoft.com/en-us/library/ms141209.aspx
A saved package can be executed later by the dtexec command line utility.
dtexec:
http://msdn.microsoft.com/en-us/library/ms162810(v=sql.105).aspx
You can always learn SSIS, but that will take a little longer than the wizard.
Good luck.
Sincerely
John Miner
Crafty Dba
John Miner
Crafty DBA
www.craftydba.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply