• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1084
  • Last Modified:

Import most recent file with SSIS package on SQL Server 2005

I have a text file that is dumped to a network drive every morning at 1:00am.  I then manually run the following code to pick up the new file and bring it into my database on SQL.  I know this can be automated with a SSIS package, however, since the file name has the date in it, the name is not consistent from day to day.

How can I tell the SSIS package to pull in the most recent file available or today's file??

Thanks
-- delete the contents of table PRE_BO
truncate table PRD_BO

-- drop the index on table PRE_BO to allow smooth data load
USE [demand_plan]
GO
/****** Object:  Index [SALES_ORD_ITEM]    Script Date: 09/28/2010 11:00:35 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[PRD_BO]') AND name = N'SALES_ORD_ITEM')
DROP INDEX [SALES_ORD_ITEM] ON [dbo].[PRD_BO] WITH ( ONLINE = OFF )

-- import the data from the PRE backorder file on STLFTP01 into the PRE_BO table on SQL
BULK INSERT PRD_BO
    FROM 'S:\sap_ftp\Daily_BW_Feeds\PRD_ABOUOBO00_20110308.txt' 
    WITH 
    ( 
        FIELDTERMINATOR = '|', 
        ROWTERMINATOR = '\n' 
    )

-- index table PRE_BO for better performance
USE [demand_plan]
GO
/****** Object:  Index [item loc]    Script Date: 09/27/2010 13:40:48 ******/
CREATE UNIQUE CLUSTERED INDEX [SALES_ORD_ITEM] ON [dbo].[PRD_BO] 
(
	[DOC_NUMBER] ASC,
	[S_ORD_ITEM] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Open in new window

0
dabdowb
Asked:
dabdowb
  • 6
  • 4
2 Solutions
 
Michael-ThomasCommented:
If you are still thinking of using SSIS take a look at using the file watcher task (http://www.sqlis.com/sqlis/post/File-Watcher-Task.aspx) to retrieve the filename.  Then move the file to an archived location after processing.
0
 
dabdowbAuthor Commented:
You asked if I am "still thinking of using SSIS"....is there an alternative?  The idea is I need to schedule it to get this file, but I am new to all of this, so really confused as to how to make it work.  I know in VBA I could just put in a formula to look at the datestamp added to the field, or a wildcard after a point in the filename, but it doesn't seem SQL plays nicely with that idea.
0
 
Michael-ThomasCommented:
You could use a master table that stores the processedfilename,then use the code below to get the listfrom your desired folder. From that list you can eliminate the ones you have alreadyprocessedand use your code above to import any that you have not.

-- Get file list in specified folder (directory) - dynamic command

-- T-SQL list files - MSSQL insert exec - T-SQL commmand shell - xp_cmdshell

CREATE TABLE #FileList (

Line VARCHAR(512))

DECLARE @CMD nvarchar(256)

SET @CMD = 'dir f:\data\ /A-D /B'

INSERT #FileList

EXEC MASTER.dbo.xp_cmdshell @CMD

DELETE #FileList

WHERE Line IS NULL

SELECT *

FROM #FileList

GO

Open in new window


code from http://www.sqlusa.com/bestpractices2005/filesindirectory/
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
dabdowbAuthor Commented:
Ok, so loving this latest idea, but how do you get it to generate through again??  For instance, I put it in my database, ran the job and it ran beautifully.  Then I went to execute again, figuring it would simply rebuild the table with the latest data, but instead, it keeps saying that object #FileList already exists and it won't process the code.

And maybe I am not understanding the idea, but I am guessing that what I will end up doing is creating a SSIS job that truncates my existing table (PRE_BO), take away the indexes on the table, and then run the code you gave me to find the newest filenames.  Now, how do I write code that take the data in the newly made FileList and puts it in the BULK INSERT command?  Then once the BULK Insert completes, I will rebuild the indexes, and move the newest file to an archive folder so that only the newest file shows up in the load folder the next day.

But then I imagine it will fail when it goes to execute the code you gave me because it will say that the object already exists.

Am I on the right track?

Thanks
0
 
dabdowbAuthor Commented:
So this is kind of what I see as far as a logic flow in just T-SQL...I can convert to SSIS later
-- delete the contents of table PRE_BO
truncate table PRE_BO

-- drop the index on table PRE_BO to allow smooth data load
USE [demand_plan]
GO
/****** Object:  Index [SALES_ORD_ITEM]    Script Date: 09/28/2010 11:00:35 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[PRE_BO]') AND name = N'SALES_ORD_ITEM')
DROP INDEX [SALES_ORD_ITEM] ON [dbo].[PRE_BO] WITH ( ONLINE = OFF )

-- Get file list in specified folder (directory) - dynamic command

-- T-SQL list files - MSSQL insert exec - T-SQL commmand shell - xp_cmdshell

CREATE TABLE #FileList (
 
Line VARCHAR(512))

DECLARE @CMD nvarchar(256)

SET @CMD = 'dir \\stlftp01\ftp\sap_ftp\Daily_BW_Feeds\ /A-D /B'

INSERT #FileList

EXEC MASTER.dbo.xp_cmdshell @CMD

DELETE #FileList

WHERE Line IS NULL

SELECT *

FROM #FileList

GO 

-- import the data from the PRE backorder file on STLFTP01 into the PRE_BO table on SQL
BULK INSERT PRE_BO
-- what do I put for the filepath using what is in the FileList table ??
-- (note, there is a PRE file and a PRD file both in the same folder)
    FROM 'S:\sap_ftp\Daily_BW_Feeds\PRE_ABOEBO00_20110311.txt' 
    WITH 
    ( 
        FIELDTERMINATOR = '|', 
        ROWTERMINATOR = '\n' 
    )

-- index table PRE_BO for better performance
USE [demand_plan]
GO
/****** Object:  Index [item loc]    Script Date: 09/27/2010 13:40:48 ******/
CREATE UNIQUE CLUSTERED INDEX [SALES_ORD_ITEM] ON [dbo].[PRE_BO] 
(
	[DOC_NUMBER] ASC,
	[S_ORD_ITEM] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [demand_plan_backorder]

-- not sure what code to use to move the now uploaded file from S:\sap_ftp\Daily_BW_Feeds
-- to S:\sap_ftp\Daily_BW_Feeds\Archived_BW_Files

Open in new window

0
 
Michael-ThomasCommented:
the reason you get the error is that #FileList is a temp table to your session, if you are looping you will need to drop it before recreating it.  You can also you a table variable rather than the temp table.

0
 
Michael-ThomasCommented:
or just add drop table #FileList at the end of your code
0
 
dabdowbAuthor Commented:
CREATE TABLE #FileList (
 
Line VARCHAR(512))

DECLARE @CMD nvarchar(256)

SET @CMD = 'dir \\stlftp01\ftp\sap_ftp\Daily_BW_Feeds\ /A-D /B'

INSERT #FileList

EXEC MASTER.dbo.xp_cmdshell @CMD

Does the pound sign make it a temp table and that is why I can't see it??  

Also, how do I get what is in the new table (whatever I end up calling it) into the BULK INSERT code to tell it what file to pull out??

BULK INSERT PRE_BO
-- what do I put for the filepath using what is in the FileList table ??
-- (note, there is a PRE file and a PRD file both in the same folder)
    FROM 'S:\sap_ftp\Daily_BW_Feeds\PRE_ABOEBO00_20110311.txt'
    WITH
    (
        FIELDTERMINATOR = '|',
        ROWTERMINATOR = '\n'
    )

So here is what is in the table when it is done running the query to see what files are out there:
PRD_ABOUOBO00_20110311.txt
PRE_ABOEBO00_20110311.txt

I have two different T-SQL codes that bring these tables in, but manually, each day, someone has to go in and change the date on the code to look for today's code...hence my question to the forum.  I feel like this solution you are providing is really close to what I am looking for, now just not sure how to get the correct file names into the jobs, unless I can just run them both in one job somehow, but each file listed loads a different table.  I feel like we're right there and I just don't know how to put the pieces together.
0
 
dabdowbAuthor Commented:
Unfortunately, a working solution was never really provided that addressed my question, but I did find one.  I have posted it here.

I am crediting the work that expert Michael-Thomas did, because his code has offered another tool for me to use...just not the one for this particular situation.  If there was a way to put the name from the newly made table listing the files in a directory, into the code to pickup the newest file, then it too would be a working solution for me.

In the meantime, here is what I found to work for my needs quite well.
-- delete the contents of table PRE_BO
truncate table PRD_BO

-- drop the index on table PRE_BO to allow smooth data load
USE [demand_plan]
GO
/****** Object:  Index [SALES_ORD_ITEM]    Script Date: 09/28/2010 11:00:35 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[PRD_BO]') AND name = N'SALES_ORD_ITEM')
DROP INDEX [SALES_ORD_ITEM] ON [dbo].[PRD_BO] WITH ( ONLINE = OFF )

-- import the data from the PRE backorder file on STLFTP01 into the PRD_BO table on SQL
declare
	@sql varchar(1000),
	@date smalldatetime,
	@target varchar(20)

-- get the current date
set @date = getdate()
-- move back a day, convert to a string
select @target = convert(varchar(20),  @date,  101)
-- get the formatted target
select @target = substring(@target, 7, 4) + substring(@target, 1, 2) + substring(@target, 4, 2)

set @sql = 'BULK INSERT PRD_BO '
set @sql = @sql + 'FROM ''S:\sap_ftp\Daily_BW_Feeds\PRD_ABOUOBO00_' + @target + '.txt'' ' 
set @sql = @sql + '    WITH '
set @sql = @sql + '    ( '
set @sql = @sql + '        FIELDTERMINATOR = ''|'', '
set @sql = @sql + '        ROWTERMINATOR = ''\n'' '
set @sql = @sql + '    )'

exec (@sql)

-- index table PRE_BO for better performance
USE [demand_plan]
GO
/****** Object:  Index [item loc]    Script Date: 09/27/2010 13:40:48 ******/
CREATE UNIQUE CLUSTERED INDEX [SALES_ORD_ITEM] ON [dbo].[PRD_BO] 
(
	[DOC_NUMBER] ASC,
	[S_ORD_ITEM] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Open in new window

0
 
dabdowbAuthor Commented:
I am not sure how to do this.  I hope this awards Michael-Thomas with all the points as he is the expert on the question, but the true solution I asked for is in my final comment and code posting.  Hope they both offer help to others, depending on what they are looking to accomplish with their project.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now