RE: Bulk Insert

[allyne]

Hi Everyone,

I have the following sql statement that Bulk Inserts a .iff file from a folder. It looks like this.

BULK INSERT Received
FROM '\\Admfs1\users\CPurnell\Responses\C0009344.iff'
WITH
(
FIELDTERMINATOR ='|',
ROWTERMINATOR =' {CR}{LF}\n'
)

This works great for one file. What I really need is to bulk insert all .iff files from the Responses folder. Something like this.

BULK INSERT Received
FROM '\\Admfs1\users\CPurnell\Responses\%.iff'
WITH
(
FIELDTERMINATOR ='|',
ROWTERMINATOR =' {CR}{LF}\n'
)


Is there a way to do this in a sql statement?

Thanks for your help!

 

[Qik3Coder]

Depending on what version of SQL you are running you should be able to mix in some SQL and VB/C# and code up a for loop that sifts through all the files in the directory.

-Sometimes the answer to your question is the hack that works

 

[allyne]

Thanks for your quick response! I'm using SQL 2005 but unfortunately I do not know VB/C#. I only know SQL....And was self taught at that.....

Any suggestions?

 

[Qik3Coder]

the code for working with the file system is very minimal and is almost readable.

Psuedo Code:
For each oFile as File in FileDirObj.Files
if oFile.Extension = "iff"
'pass file name to bulk inserter
end if
Next

-Sometimes the answer to your question is the hack that works

 

[SQLSister]

I believe SSIS can do this as well. It is not so easy to learn how to use SSIS but once you make the time investment, you will find it very powerful and useful.

"NOTHING is more important in a database than integrity." ESquared

 

[allyne]

Hi SQL Sister,

I created an SSIS package but I kept getting an Access Denied error message even though I have all the permissions needed. After days of research I was told that this is a known bug so I thought I'd try this route. This seems to work great if I can figure out how to import all files in the folder. Unfortunately this seems to be beyond my knowledge.

Thanks for your help!

 

[Qik3Coder]

What if you had a separate step that loaded all the file names into a table and you had a little flag for if they were imported or not?

I don't know if you have a dev there or not, but they should be able to whip up a little one button form for loading that data into the database. it shouldn't take more than 2-4 hours for the form and db insert code...

-Sometimes the answer to your question is the hack that works

 

[allyne]

Hmmmm....Interesting idea. Worth checking out.

Thanks!

 

[SQLSister]

I'm just starting to learn SSIS but I would suspect the permissions are for the SQLServerAgent not having access to the file folder not your personal account. Maybe someone who has been running SSIS can confirm that?

"NOTHING is more important in a database than integrity." ESquared

 

[allyne]

Hi SQL Sister,

Just checked. SQL Server Agent has permissions. What is strange is that If I tell it to insert a particular file in that folder it works just fine. It seems to error out only when I want to insert a files in that folder.....

Thanks for your help!