February 16, 2009 at 6:36 am
Hello Everyone
I am just searching to see how many different ways. I could load my files, since I am relatively new to this. Here are my requirements.
1) Must be automatic and run with Job Agent
2) Input file names are variable
MYFILEmmddyyyy.txt YOURFILEmmddyy.xls
THEIRFILEmmdd.cvs S65mmddyyyy (still a txt)
3) database table will be the name of the file
[dbo].[INFILES].[MYFILEmmddyyyy.txt]
4) actual file name will be part of the record MYFILE02162009.txt
5) some files have the col names as 1st record some don't
6) I am using SQL server 2005
Should I use SSIS , BCP, OR any thing else you can think of.
February 16, 2009 at 8:02 am
How would the "variable file name" be automatically determined/provided?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 16, 2009 at 8:42 am
I have a table with the names in it. Or the directory could and should be polled for the names of the actual files.
February 16, 2009 at 11:25 pm
you can change filename with this way also
i think you should use bcp ....with use of xp_cmdshell to find the path of folder and it will take all the filename in one table .....and then it's easy you have path of the file and filename also.....i think that this hint will work for you...
DECLARE @d AS DATETIME
SET @d = GETDATE()
DECLARE @filename VARCHAR (50)
SET @filename = 'one_'
SELECT CONVERT (VARCHAR,@d,101)
--SET @filename = @filename+'_'+CONVERT (VARCHAR,@d,112)
--- or you can use following
SET @filename = @filename+'_'+CONVERT (VARCHAR,@d,112)
SELECT @filename
DECLARE @sql VARCHAR(1000)
SET @sql = 'create table '+@filename+' (id int) '
PRINT @sql
EXEC (@sql)
Raj Acharya
February 17, 2009 at 1:27 pm
Well let's see, BCP & BULK INSERT will not load XLS's, AFAIK, so they're out. So, that leaves:
A) SSIS
B) Dynamic-SQL driven OPENQUERY(sp?) tricks
C) SQLCLR
D) Client-side .Net driven through command lines
E) Third-party products
SSIS (A) might be able to handle this, but I think that you will find that not having specific column-sets and file-types ahead of time will be more trouble than than it's worth in SSIS.
I am unfamiliar with third-party products (E) in this space and I do not really know how to use the OPENQUERY tricks, so I cannot comment on these.
That leaves SQLCLR (C) which should be able to do it, but has the annoying habit of stopping you from doing things at the end of a project that you had assumed all along that it would let you do.
So my bet is on (D): plain-old console command-line driven .Net client apps that just find the files and load them for you. Since these can be called from SQL Agent Jobs, I see them as your most likely solution.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 17, 2009 at 10:35 pm
Is this going to be a one-time job?
Or will you be running this scheduled (daily, weekly...) as new files come in?
So you receive MYFILE01012009.txt, then MYFILE01022009.txt... each day?
Will all the MYFILE... have the same format?
Will all the YOURFILE... have the same format?...
If all the files have the same format in each group:
From your import stored procedure, use EXEC xp_cmdshell to copy the actual file MYFILE... to "MYFILEtemplate.txt"
Create an SSIS package to import the specific file "MYFILEtempate.txt" into dbo.inifile.temp_MYFILEtemplate.
Use EXEC xp_cmdshell to run that SSIS package.
In SQL, create the new table.
Copy data from the temp table into the new table.
February 18, 2009 at 5:27 am
Is this going to be a one-time job? Yes Daily
So you receive MYFILE01012009.txt, then MYFILE01022009.txt... each day? Yes
Will all the MYFILE... have the same format? Yes
Will all the YOURFILE... have the same format?...Yes
If all the files have the same format in each group:
From your import stored procedure, use EXEC xp_cmdshell to copy the actual file MYFILE... to "MYFILEtemplate.txt" Good Idea
Create an SSIS package to import the specific file "MYFILEtempate.txt" into dbo.inifile.temp_MYFILEtemplate.
Use EXEC xp_cmdshell to run that SSIS package.
In SQL, create the new table.
Copy data from the temp table into the new table.
This is what I am looking for a few differant Ideas to complete my task
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply