SQL 2005 Flat file Import? 1

[octavian10]

Hello all again,
I have to start off by saying I just installed SQL 2005 and am not that versed with it. With that said I have to write a job that imports data from a flat file but only when a "trigger" file is present. This trigger file is only there when the flat file has been updated. If the trigger file is present then run the import data job and delete the trigger file. If it is not present then stop the job and do nothing. The info being imported is FirstName, LastName, EmployeeID this is being imported to a temp database where I then run other sp and querys. I am sure I can figure out how to pull data from flat file, but that trigger file these people insist on is really throwing me for a loop. Thanks in advance.

"I hear and I forget. I see and I remember. I do and I understand."
- Confucius (551 BC - 479)

 

[gmmastros]

Use the system stored procedure xp_fileexist

Sample usage...

Declare @FileExists Int

exec master..xp_fileexist 'C:\document.txt', @FileExists Out

If @FileExists = 1
  Select 'File Does Exist'
Else
  Select 'File Not Found'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Deleting files is a bit more problematic. There are several ways to accomplish this.

1. Write a CLR to do it. I've never written a CLR so I can't really advise you on 'how to do it'.
2. Use xp_cmdshell. This is going to be problematic for you because it requires elevated priveleges to use it.
3. You could also use the file system object to delete a file. This would require that you use the sp_oacreate, sp_oadestroy, sp_oamethod procedures, which also require elevated rights to use.


Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mrdenny]

I would simply make the delete the third step of the job and have the step type me a operating system command and the step to be a delete command. This way you don't have to worry about CLR, or xp_cmdshell or the sp_oa procedures.

If you were going to use an SSIS package to do the import you could use a script object to delete the file after the import is successful.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[octavian10]

Hey gmmastros, can you please explain a little further. This Flat File has a lot of info in it that other applications need, all I need from it is the FirstName (Col 1), LastName (Col 2), and Employee ID (Col 3), this info gets iported into a Table called CardHolder which has 3 columns FirstName LastName and EmployeeID. Can you if you have time give me a quick example of how I can use the xp_fileexist with what I am trying to do.

"I hear and I forget. I see and I remember. I do and I understand."
- Confucius (551 BC - 479)

 

[gmmastros]

ok.

First, create a folder on the server's hard drive. I called my folder tektips. So, C:\tektips is the folder I am using.

Next, create a file named C:\tektips\datafile.txt with this...

George,Bush,1
Al,Gore,2
Hillary,Clinton,3

Create another file in the same folder and name it C:\tektips\trigger.txt The trigger file does not have to have anything in it.

Now, open query analyzer and copy/paste this code.

[COLOR=blue]Declare[/color] @FileExists [COLOR=blue]Int[/color]

[COLOR=green]-- Check to see if the trigger file exists.
[/color][COLOR=blue]exec[/color] master..xp_fileexist [COLOR=red]'C:.txt'[/color], @FileExists [COLOR=#FF00FF]Out[/color]

[COLOR=blue]If[/color] @FileExists = 1
	[COLOR=blue]Begin[/color]
		[COLOR=green]-- The trigger file exists, so import the data
[/color]		[COLOR=blue]Select[/color] [COLOR=red]'File Does Exist'[/color]
	
		[COLOR=green]-- Create a temp table to store the data
[/color]		[COLOR=blue]Create[/color] [COLOR=blue]Table[/color] #Temp(LastName [COLOR=blue]VarChar[/color](50), FirstName [COLOR=blue]VarChar[/color](50), EmployeeId [COLOR=blue]Int[/color])

		[COLOR=green]-- load the data from the file in to the temp table
[/color]		[COLOR=blue]Bulk[/color] [COLOR=blue]Insert[/color] #Temp [COLOR=blue]From[/color] [COLOR=red]'C:.txt'[/color] [COLOR=blue]With[/color](FieldTerminator=[COLOR=red]','[/color])

		[COLOR=green]-- do something meaningul with the data
[/color]		[COLOR=blue]Select[/color] * [COLOR=blue]From[/color] #Temp

		[COLOR=green]-- remove the temp table
[/color]		[COLOR=blue]Drop[/color] [COLOR=blue]Table[/color] #Temp

		[COLOR=green]-- delete the trigger file.
[/color]		xp_cmdshell [COLOR=red]'del C:.txt'[/color]

	[COLOR=blue]End[/color]
[COLOR=blue]Else[/color]
	[COLOR=blue]Begin[/color]
		[COLOR=green]-- The trigger file does not exist, so do nothing
[/color]		[COLOR=blue]Select[/color] [COLOR=red]'File Not Found'[/color]
	[COLOR=blue]End[/color]

When you run this code, you will see that the contents of the data file are loaded in to a temp table (and displayed to you with the select statement). Then, the trigger file is deleted. When you run this a second time, you will see that nothing is done (except the message 'File not found').

Hope this helps.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mrdenny]

George,
Take a look at xp_delete_file. This way you don't have to use xp_cmdshell.

I talk about it (a little bit) here

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1270919,00.html.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[gmmastros]

Good to know. thanks.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[octavian10]

Thanks gmmastros this works great. Is there a way that I can make this a job where if the file exixts go to next step else quit job.

"I hear and I forget. I see and I remember. I do and I understand."
- Confucius (551 BC - 479)

 

[gmmastros]

Read through the code again. It's already doing that.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[octavian10]

Yea, you are right for the example code you provided, I changed it around a little and would like the bulk insert or some other code to be step 2 of a job. So Step 1, check for trigger file, if its there go to step 2, if it is not quit the job. I really appreciate your effort I am learning alot.

"I hear and I forget. I see and I remember. I do and I understand."
- Confucius (551 BC - 479)