Hello,
Base on your experience, what do you advise. Use a program to import to sql server using loop or using bulk import. What are the pros and cons.
Example of bulk import:
BULK INSERT tb.do.apple
FROM C:\drive
GO
My experience has been that both BCP and BULK INSERT have been specifically written to write to SQL Server without going through the normal user interface APIs and so it's faster than anything you could write and has been tested for decades. Writing your own program would also create a lot of unnecessary traffic.
As with any tool, BCP and BULK INSERT have some nuances you need to learn (like all the different ways a BCP FORMAT file can be used, etc) but it's well worth it. Learn the tool to the max and use it.
I have tried Bulk import but I haven’t had success in it. Maybe there is some restriction.
I don’t know why, I like a program because I have better control to it. although is slower.
SSIS was built to do these kinds of operations and actually wraps BULK INSERT (if you use the OLEDB Destination or the SQL Server Destination).
It would be interesting to figure out what kind of problems you're having with BULK INSERT. It sounds like your bent on writing program.
Just an FYI, I'm also not a fan of SSIS for many reasons.
The promise of SSIS is great, and I have really tried to like it. But, after reading a lot about it, and trying to use it several times over a period of time, I dislike it only slightly less than a root canal. I attributed it to my inferior ability to grasp the greatness of it, so seeing that there are other non-fans helps my self-esteem a little bit 
I am a fan of Powershell. It is transparent, it is testable, it is debuggable (is that a word?), and there is plenty of documentation, support, and scripts available online.
I use it for all kinds of things - for interacting with Web Services, exporting files to Excel or csv, importing large amounts of data, using .Net dlls that others have written and so on.
It would be useful to read up on Powershell basics. But even without that, search online, and most of the time, you will find exactly what you need.
Here is a link that shows an example of inserting a single row of data into a table. You can use it to test whether you can run powershell scripts, and diagnose connectivity problems if any.
Here is a script that allows you to efficiently bulk insert large files.
1 Like
James
i am also a big fan of powershell
basically i find that using SCRIPTING languages gives a lot of flexibility and minute control
of all things
powershell can also be used to automate HUGE work ... rather than using UI ( UI is painful )
I have not found SSIS to be a problem or hard to use. For a simple bulk insert it is very easy to setup and configure and get working.
I have found that the hardest aspects around SSIS have generally been around deployment - which has been significantly improved with the project deployment to the Integration Services Catalog.
I am also a fan of Powershell and use it quite extensively.
1 Like
Depends on the situation.
If you are confident about the file format and it is suitable then I would go for bulk insert as the simplest to implement and maintain. Better to have something to reconcile against to make sure nothing gets lost (in all cases).
I recently had one where the source system kept adding fields to the file and changing the order - for that I used SSIS as it could be configured to be resilient to changes as long as file has a header and the fields needed are in the file.
For another which was to be used by a system which couldn't cope with large files I used powershell to split the files up.
I always poll the folder and save the files to a table then use that to import. That gives a record of when the files appear and allows you to get rid of duplicates and check if a file has been recreated.
Depends on the situation and the expertise of the developers.
1 Like