Daily CSV import to SQL - How ?

Question

Daily CSV import to SQL - How ?

Pigger

Old Hand

Points: 384
More actions
September 12, 2014 at 8:36 am

#293169

Hi I am receiving 1 csv file daily. The schema is static and the same for each file. I have created a table with the same schema as the csv.
Is it possible to create a SSIS job which
1. Monitors the directory or runs at a particular time
2. imports the csv data into the table
3. moves the file once it has been processed to an archive directory.
Any good links to a tuturial would be appreciated. I have tried a couple but they are not basic enough for a newbee:-P
Thanks in advance

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

G-Force (9/12/2014)
Hi I am receiving 1 csv file daily. The schema is static and the same for each file. I have created a table with the same schema as the csv.
Is it possible to create a SSIS job which
1. Monitors the directory or runs at a particular time
2. imports the csv data into the table
3. moves the file once it has been processed to an archive directory.
Any good links to a tuturial would be appreciated. I have tried a couple but they are not basic enough for a newbee:-P
Thanks in advance

What do the filenames look like?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Pigger Old Hand Points: 384 More actions · Answer 2

The file name is todays date time stamp

YYYYMMDDhhmmss.csv

Thanks

G

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

G-Force (9/12/2014)
Hi I am receiving 1 csv file daily. The schema is static and the same for each file. I have created a table with the same schema as the csv.
Is it possible to create a SSIS job which
1. Monitors the directory or runs at a particular time
2. imports the csv data into the table
3. moves the file once it has been processed to an archive directory.
Any good links to a tuturial would be appreciated. I have tried a couple but they are not basic enough for a newbee:-P
Thanks in advance

Quick thought, this is straight forward in SSIS, suggest you start by reading through Stairway to Integration Services[/url].

😎

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Apologies... really got hammered by work-work.

Are you all set now?

Just as a bit of a sidebar, this is also pretty easy using only T-SQL. If you'd rather do that than work this problem in SSIS, let us know.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Pigger Old Hand Points: 384 More actions · Answer 5

Hi Jeff

Still trying to find the dummies guide 🙂 . Is there anywhere you can point me to on how to do it in TSQL ?

Any help gratefully received.

Thanks

G

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

G-Force (9/16/2014)
Hi Jeff
Still trying to find the dummies guide 🙂 . Is there anywhere you can point me to on how to do it in TSQL ?
Any help gratefully received.
Thanks
G

hi...these may help you

http://www.mssqltips.com/sqlservertip/2874/loop-through-flat-files-in-sql-server-integration-services......shows you how to move files within SSIS

http://sqlblog.com/blogs/jamie_thomson/archive/2010/03/31/filenamecolumnname-property-flat-file-source-adapter-ssis-nugget.aspx ...usefull info for recording the original filename for the import

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

G-Force (9/16/2014)
Hi Jeff
Still trying to find the dummies guide 🙂 . Is there anywhere you can point me to on how to do it in TSQL ?
Any help gratefully received.
Thanks
G

The basics are to determine if the file is there and then import it using BULK INSERT. I'll see if I can whip up an example for you tonight. In the mean time, can you post the CREATE TABLE statement for the "target" table and (if there's no personal or company private information), attach a file with at least the first 10 lines of data in it (including any headers, etc).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

P Jones SSChampion Points: 12352 More actions · Answer 8

SSIS can also do this well along with SQL Agent to run the SSIS package. Either waiting for a file to appear in a folder or running at specified times is possible.

We deal with over 500 csv format files every day in both these ways.

I use a script task in SSIS to see if a file is arriving as some of ours are quite large and wait until it is fully available then for zip files,a For Each loop with an execute command task to unzip it into a folder with the 7zip tool before processing the csv files into the staging and then main reporting database and a File System task to move the processed files to an archive directory (script task if you want to create a dated one).

Package logging and error handling ensure you know when anything goes wrong.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

P Jones (9/17/2014)
...and wait until it is fully available ....

I'm curious. What are you using to determine that a file is done being written to and is ready for consumption? (other than a popup thermometer or fork :-P)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

P Jones SSChampion Points: 12352 More actions · Answer 10

Jeff Moden (9/17/2014)
P Jones (9/17/2014)
...and wait until it is fully available ....
I'm curious. What are you using to determine that a file is done being written to and is ready for consumption? (other than a popup thermometer or fork :-P)

Stick a finger in the air and see which way the wind's blowing. 😀

Actualy a bit of vb script in a script task as the ftp comining in takes about 20 minutes to transfer a big zip file and the file shows as existing from the start but is locked by the ftp until fully done. I probably found the idea somewhere by Googling but it was a couple or three years ago. And I do lots of package logging so I can just run a quick query as a daily check to see package errors.

While (FileLocked)

Try

' Check if the file isn't locked by an other process by opening

' the file. If it succeeds, set variable to false and close stream

fs = New FileStream(fnvar, FileMode.Open)

' No error so it is not locked

logmsg = "File not locked: " & fnvar

Dts.Log(logmsg, 0, b)

FileLocked = False

' Close the file and exit the Script Task

fs.Close()

Dts.TaskResult = ScriptResults.Success

Catch ex As IOException

' If opening fails, it's probably locked by an other process. This is the exact message:

' System.IO.IOException: The process cannot access the file 'D:\example.csv' because it is being used by another process.

' Log the locked status (once)

If (ShowLockWarning) Then

logmsg = "File locked: " & fnvar & " " & ex.Message

Dts.Log(logmsg, 0, b)

End If

ShowLockWarning = False

' Wait two seconds before rechecking unless we've done 30 mins

If DateDiff(DateInterval.Minute, Date.Now, starttime) < 30 Then

Thread.Sleep(2000)

Else

logmsg = "Given up after waiting 30 mins. File locked: " & fnvar & " " & ex.Message

Dts.Log(logmsg, 0, b)

Dts.TaskResult = ScriptResults.Failure

Exit While

End If

Catch ex As Exception

' Catch other unexpected errors and break the while loop

logmsg = "Unexpected error: " & fnvar & " " & ex.Message

Dts.Log(logmsg, 0, b)

Dts.TaskResult = ScriptResults.Failure

Exit While

End Try

End While

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

P Jones (9/19/2014)
Jeff Moden (9/17/2014)
P Jones (9/17/2014)
...and wait until it is fully available ....
I'm curious. What are you using to determine that a file is done being written to and is ready for consumption? (other than a popup thermometer or fork :-P)
Stick a finger in the air and see which way the wind's blowing. 😀
Actualy a bit of vb script in a script task as the ftp comining in takes about 20 minutes to transfer a big zip file and the file shows as existing from the start but is locked by the ftp until fully done. I probably found the idea somewhere by Googling but it was a couple or three years ago. And I do lots of package logging so I can just run a quick query as a daily check to see package errors.
While (FileLocked)
Try
' Check if the file isn't locked by an other process by opening
' the file. If it succeeds, set variable to false and close stream
fs = New FileStream(fnvar, FileMode.Open)
' No error so it is not locked
logmsg = "File not locked: " & fnvar
Dts.Log(logmsg, 0, b)
FileLocked = False
' Close the file and exit the Script Task
fs.Close()
Dts.TaskResult = ScriptResults.Success
Catch ex As IOException
' If opening fails, it's probably locked by an other process. This is the exact message:
' System.IO.IOException: The process cannot access the file 'D:\example.csv' because it is being used by another process.
' Log the locked status (once)
If (ShowLockWarning) Then
logmsg = "File locked: " & fnvar & " " & ex.Message
Dts.Log(logmsg, 0, b)
End If
ShowLockWarning = False
' Wait two seconds before rechecking unless we've done 30 mins
If DateDiff(DateInterval.Minute, Date.Now, starttime) < 30 Then
Thread.Sleep(2000)
Else
logmsg = "Given up after waiting 30 mins. File locked: " & fnvar & " " & ex.Message
Dts.Log(logmsg, 0, b)
Dts.TaskResult = ScriptResults.Failure
Exit While
End If
Catch ex As Exception
' Catch other unexpected errors and break the while loop
logmsg = "Unexpected error: " & fnvar & " " & ex.Message
Dts.Log(logmsg, 0, b)
Dts.TaskResult = ScriptResults.Failure
Exit While
End Try
End While

And it's even got some documentation built into it! Thank you for taking the time to post that. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)