import data properly from csv file.

I need to extract data from a csv file, validate it, and populate other
tables with that data for a multi user web application.
I am importing a csv file via linked servers as follows:
EXEC('SELECT * into ##temptbl FROM '+@.linked_server + '...['+@.file + '#' +
@.extension + ']')
Once data gets into ##temptbl then I do proper validation and populate other
tables.
This will not work if there are other users importing the file as well
because of global temp table ##temptbl.
Do I create a separate physical table to populate and delete based on
certain criteria for that user?
I tried using table variable inside the dynamic sql but did not work. So my
best bet for now is
to have a physical table, populate it for certain criteria, do validation,
and populate other permanent tables. After
successful population I would go ahead and delete rows this temporary
staging for certain criteria.
Does this make sense or this approach stinks?
TIA...I would really appreciate if any guru/expert could address this.
TIA...
"sqlster" wrote:

> I need to extract data from a csv file, validate it, and populate other
> tables with that data for a multi user web application.
> I am importing a csv file via linked servers as follows:
> EXEC('SELECT * into ##temptbl FROM '+@.linked_server + '...['+@.file + '#' +
> @.extension + ']')
> Once data gets into ##temptbl then I do proper validation and populate oth
er
> tables.
> This will not work if there are other users importing the file as well
> because of global temp table ##temptbl.
> Do I create a separate physical table to populate and delete based on
> certain criteria for that user?
> I tried using table variable inside the dynamic sql but did not work. So m
y
> best bet for now is
> to have a physical table, populate it for certain criteria, do validation,
> and populate other permanent tables. After
> successful population I would go ahead and delete rows this temporary
> staging for certain criteria.
> Does this make sense or this approach stinks?
> TIA...

import column Transform and relative/absolute path

Hi,

I have a table with a BLOB column, and I need to populate this table including the BLOB column (image type in the database).

What I have done is:

1. use a flat file transform to read a .csv file which specifies the names of the files that store the binary contents for the BLOB column for each row.

2. use an Import Column Transform to read the binary files.

3. use an OLE DB Dest transform to dump the data into my destination table.

I got the error saying:

Error: 0xC02090BB at XXXX, Import Column [1]: Opening the file ".\diagram1.bin" for reading failed. The file was not found.

I guess this is because my file "diagram1.bin" is not in the current path? (The current path can be found by "System.IO.Directory.GetCurrentDirectory() call, in my case it is "c:\program files\microsoft visual studio 0\common7\IDE".)

My question is: how to determine the directory path information of the package I am running?

Thanks!

Wenbiao

Absolute paths should be used. Can you use a variable set to the path and then use that variable in an expression to set the root path.|||

Phil Brammer wrote:

Absolute paths should be used. Can you use a variable set to the path and then use that variable in an expression to set the root path.

Thanks Phil for the quick reply, variable works beautifully! I will just have to make sure that I use a configuration file to change the path when I deploy the package.

Thanks again!

Wenbiao