Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Import most recent file with SSIS package on SQL Server 2005

Posted on 2011-03-08
10
Medium Priority
?
1,056 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 2000 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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
 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

773 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