August 20, 2008 at 10:52 pm
I want to load a csv file into a table. I want the source file and destination table to be dynamic, ie any file into any table.
I know a data flow task in ssis cannot do this as the column mappings metadata have to be predefined for the source and destination.
Perhaps the bulk insert task in ssis can. I think it needs the source columns to match the target table columns exactly though.
What are the alternatives? Can BCP handle the case where the columns in the source file are in a different order to the columns in the target table? Can the BCP format file be used to specify these column mappings?
Any other methods?
Cheers
August 21, 2008 at 1:27 am
Not SSIS.
You could do it with BCP but your table columns / file columns will have to be aligned.
i.e.
File:
FirstName|LastName
would not do into a table with
LastName|FirstName
Asking to import any file into any table is a tall order and will be fraught with compromises and problems.
Personally, if someone asked me to do it I would tell them to go away. Come back with a proper request.
It's like the stored procedure I have been trying to write for years now: Give it any parameter and and it will insert into any table.
Just imagine how easy life would be - only one stored proc in my database. :w00t:
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 21, 2008 at 1:28 am
You _could_ do it with a code like VB or c#. Personally though, I would not.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 21, 2008 at 5:36 pm
I've actually written some C# code to dynamically create a package with a data flow and dynamic column mappings but it just doesn't feel right. For one it's not flexible. Plus the api to interact with ssis is hell. I was hoping bcp could solve all my problems. 🙂
August 24, 2008 at 5:50 pm
Crispin Proctor, you said:
"You could do it with BCP but your table columns / file columns will have to be aligned".
Are you sure of this? I thought that's exactly what the format file could do.
August 24, 2008 at 6:40 pm
For those interested, here is a page that describes how to use BCP to import data where the source file columns are in a different order to the target table columns:
August 27, 2008 at 12:12 pm
I have a similiar issue Paul, I need to load from excel where the columns will vary on each import into an OLEDB destination. I have been wrestling with the SSIS Pipeline Object model for about two weeks now trying to wrap my head around it...its hell
August 27, 2008 at 2:26 pm
Paul_J (8/24/2008)
Crispin Proctor, you said:"You could do it with BCP but your table columns / file columns will have to be aligned".
Are you sure of this? I thought that's exactly what the format file could do.
When I sent it I was not convinced I was right. Seems I was right (about being wrong) :w00t:
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 27, 2008 at 2:27 pm
oxfield (8/27/2008)
I have a similiar issue Paul, I need to load from excel where the columns will vary on each import into an OLEDB destination. I have been wrestling with the SSIS Pipeline Object model for about two weeks now trying to wrap my head around it...its hell
For excel, (even text now I think about it) have a look at DataDefractor. http://www.datadefractor.com
It's a seriously powerful tool.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 27, 2008 at 3:01 pm
Paul_J
A very similar question was asked in another forum and here is the most flexible approach given as the answer. This may also be the answer to your question. Look at
http://www.sqlservercentral.com/Forums/Topic559811-149-1.aspx#bm559856
by the way the above was posted today
August 27, 2008 at 5:50 pm
'oxfield':
I wouldn't even bother trying to work with the ssis object model for something like this. It's very annoying and not worth the effort. BCP seems to be a very good solution for me in this case.
The only problem I've had with BCP is that it doesn't log the error when one of the rows in the input CSV file has fewer (or more) columns than expected. I guess BCP assumes the CSV file has to be valid in the first place.
If you're still keen on working with the ssis object model with .NET, have a look at the following site if you haven't already found it. I found it very useful.
http://lamp.codeproject.com/KB/database/Digging_SSIS_object_model.aspx
I still need to look in detail at the linked server solution suggested by 'bitbucket'.
August 27, 2008 at 8:24 pm
thanks for the reply Paul...the earlier suggestion with a linked server works great... I just need my DBA to create the linked server in our dev environment (permission issue)..However, in my local version of SQL..i set it (a linked server) up and it was flawless. Easy to do, quick and desired results.. I haven't used BCP since pre - SQL2K Am I wrong, but from what I recall all my columns would need to be defined in the format file to utilize BCP.. I don't remember it being dynamic. I used to use BCP quite a bit before SQL 2000 came out. I am hell bent though on getting a better understanding of this convoluted..supposedly "improved" tool...that MS claims SSIS is. Again..thanks for the reply
Rich
August 27, 2008 at 8:42 pm
oxfield (8/27/2008)
thanks for the reply Paul...the earlier suggestion with a linked server works great... I just need my DBA to create the linked server in our dev environment (permission issue)..However, in my local version of SQL..i set it (a linked server) up and it was flawless. Easy to do, quick and desired results.. I haven't used BCP since pre - SQL2K Am I wrong, but from what I recall all my columns would need to be defined in the format file to utilize BCP.. I don't remember it being dynamic. I used to use BCP quite a bit before SQL 2000 came out. I am hell bent though on getting a better understanding of this convoluted..supposedly "improved" tool...that MS claims SSIS is. Again..thanks for the replyRich
BCP doesn't need a format file any more than Bulk Insert does. If the delimiter is consistent between columns and any header info has the same number of and type of delimeters, you're golden. The trick with the text linked server invokes something a bit smarter and, I believe, you can pull off the same trick with OpenRowSet.
The problem with the text linked server shows up when you have no header on the file.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply