BULK INSERT: marked for deprecation?

I read in Microsoft SQL Server 2005 Integration Services by Kirk Haselden that the BULK INSERT task was provided for backward compatibility and its use is disrecommended.

But after looking on the web I cannot find information supporting this.

Do you think the BULK INSERT task should be used for new development?

Thanks

It's not currently marked for deprecation. It may be in the future, because using the data flow and SQL Server Destinations or using the "fast load" option on the OLE DB destinations will be much more flexible.

I would avoid using the Bulk Insert Task simply because of its rigid restrictions and the fact that it requires a CSV formatted file. That's way too limiting for me.|||

I agree with you, the Data flow task is much more flexible. And it performed better than the BULK INSERT in my case...
But the big advantage of the BULK INSERT (and some see it as a disadvantage) is that it uses Format Files.

In our case, we have flat files from about 20 providers. There's is no way of suppling the Data flow task with a transformation schema, so that would mean creating/maintaining 20 different Data flow tasks (each in a different package).

Another implementation would be to use a generic SSIS with a Bulk Insert Task taking as parameters the source/destination and a format file. In that case, we would have to create/maintain 20 different format files.

So I'm currently weighting each option. The restrictions of the BULK INSERT such as the flat file requirement are not a problem in this case. Performance is at a certain extent. Usability, deployment and maintenance are the most important criteria.

Thanks!

|||You might want to provide feedback over on connect.microsoft.com and share your feelings.

Perhaps Matt Masson, Michael Entin, or others from MS can jump in and share their opinions or even MS' official stance.|||

Phil Brammer wrote:

You might want to provide feedback over on connect.microsoft.com and share your feelings.

Perhaps Matt Masson, Michael Entin, or others from MS can jump in and share their opinions or even MS' official stance.

Are you suggesting I ask them about the BULK INSERT being deprecated or about adding format file option to the Data flow task? I'm not sure...

What I would want is a place to seek advice on implementation. There's always many ways to do the same thing, especially when using T-SQL and SSIS. But do I post in the T-SQL forum or in the SSIS forum?

|||

fleo wrote:

Are you suggesting I ask them about the BULK INSERT being deprecated or about adding format file option to the Data flow task? I'm not sure...

What I would want is a place to seek advice on implementation. There's always many ways to do the same thing, especially when using T-SQL and SSIS. But do I post in the T-SQL forum or in the SSIS forum?

I'm suggesting that you post your feedback (via https://connect.microsoft.com/SQLServer/feedback)on how you want the BULK INSERT task to stick around and not be deprecated.

As far as which forum, well, that just depends on the scenario. Don't be too worried about which forum to post in as if we feel it needs to be moved, there are a few of us here that can do that accordingly.|||

There may have been deprecation discussions when Kirk was originally writing his book, but as of now, there are no plans to deprecate the Bulk Insert task.

As previously mentioned, you get more flexibility using the data flow approach, and if you’re able to use the “fast load” option, you might even get a 10-15% performance increase over the BULK INSERT tasks. You can weigh out both options, but as Phil has suggested, using a data flow is generally the recommended approach.

|||

I think the inflexibility is a decision point between using the Data Flow over the Bulk Insert Task and whilst I would normally use Data Flow perhaps more out of habit, I think fleo's point about being able to load files of any format, by dynamically setting properties is very valid.

It is something that cannot be achieved with the Data Flow task, and should not be underestimated. It can be a very powerful tool, and one reason why many people use the T-SQL equivalent or even BCP still. I just like being able to get that functionality within the SSIS framework so I can use configurations and logging and all that good stuff.

Use it and save time, and of course Matt has responded in the positive too!

|||

hI Matt,

so the Flat File Source has better performance than the BULK INSERT Task...

Where can I found this kind of information?

Also about the deprecation topic, why hasn't it written in the MSDN documentation?

Thank you

|||

Hi Antonio,

Unfortunately, the 10-15% performance increase that I quoted wasn't from any official testing or documentation... I believe it came from a developer from an internal group that was testing out both methods. There's typically a lot of variables involved in these tests, so I'd recommend trying out both approaches yourself and seeing which works best in your situation.

I'm not sure what you're asking about the deprecation topic. I wasn't able to find any official reference to the task being deprecated when I first looked into this, and MSDN typically doesn't explicitly mention that a task is NOT going to be deprecated.

Thanks,

~Matt

sql