Bulk Insert with Added Columns

Hi, I'd like to do a bulk copy insert inside a dts package but the table i'm
inserting to has more columns than the text file. I know you can change the
format file to take care of this, but how do you insert actual values into
those extra columns instead of just NULLS? The values for those extra column
s
are uniquely identified by the text file.
What I could do is do a bulk insert and then run an execute sql command
afterwards which updates the table by replacing the NULL values with actual
values. However, this db is multi-user with each user having different value
s
to insert, so if two imports are done at the same time, someone could be
updating the rows of another person's import. Locking the table is also not
a
possibility because the imports take a long time and we don't want one user
waiting on another one to finish.
Right now, I'm using an ActiveX script in a transform data task in the DTS
package. So there's two transformations going on in that task: one is the
copy column from the text file, and another is adding values to the extra
columns. This works well, however, it requires row by row processing and thu
s
it takes over 3 times longer than a bulk insert.
Does anyone have a solution to setting values of extra columns in a bulk
insert? Any help is appreciated. Thanks!Where do those extra values come from?
ML|||These extra values are global variables in the dts package
"ML" wrote:

> Where do those extra values come from?
>
> ML|||In that case you can design a set-based solution. You need something like th
is:
1) you need access to the file - one way to achieve this is linking the file
as a linked server. Read more on this here:
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
http://msdn.microsoft.com/library/d...ma_ini_file.asp
2) you need access to your global variables - the best way would be to
create a procedure that accesses the linked file and accepts the values of
those global variables as parameters;
3) in your procedure you then combine the values from the source file and
the values from the DTS, and insert them into the destination table(s).
You could even use a folder-sniffer to start the job automatically, as soon
as the source file becomes available in the source folder.
ML