Access/Mapping the File Name during the copy process to a SQL Datatable
I need a way to store the FileName that is been copied to a SQL Datatable mapped column. Will be great to have access to other file properties like size, rowcount, etc. But the file name will help us to work with undo processes.
J. Morgan Smith commented
Using Mapping Data Flows works fine if you have several files to import and/or have several transformations to perform, however, if all you want to do is bulk load one or two files then the Copy Activity is much faster. Why should we have to wait 5 minutes just to "warm up" the data flow?
Having the filename available for mapping to a Sink column is desperately needed. This feature is continuously being requested and MS continues to ignore by offering slower or heavily coded solutions. The Copy Activity obviously knows what file it is importing... make it available for mapping to a Sink column!
Guess we need to get the votes on this suggestion instead of spreading them over several other similar suggestions.
Mark Kromer commented
If you are copying many source files into a SQL DB, you can map the name of each source file to a field in your SQL DB using Mapping Data Flows. See the Source transformation "column to store file name" setting: