Solved

Import most recent file with SSIS package on SQL Server 2005

Posted on 2011-03-08
10
1,014 Views
Last Modified: 2012-05-11
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
Comment
Question by:dabdowb
  • 6
  • 4
10 Comments
 
LVL 2

Expert Comment

by:Michael-Thomas
ID: 35074283
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
 

Author Comment

by:dabdowb
ID: 35101959
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
 
LVL 2

Accepted Solution

by:
Michael-Thomas earned 500 total points
ID: 35102357
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
 

Author Comment

by:dabdowb
ID: 35110883
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
 

Author Comment

by:dabdowb
ID: 35110946
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 2

Expert Comment

by:Michael-Thomas
ID: 35111020
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
 
LVL 2

Expert Comment

by:Michael-Thomas
ID: 35111025
or just add drop table #FileList at the end of your code
0
 

Author Comment

by:dabdowb
ID: 35111134
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
 

Assisted Solution

by:dabdowb
dabdowb earned 0 total points
ID: 35400552
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
 

Author Closing Comment

by:dabdowb
ID: 35431104
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now