April 13, 2005 at 9:03 am
Im not sure if this is possible but...
From Sql Server (DTS or SP) can we read a text file sitting on your C drive.
The name of the file can change everyday except for part of the file name.
Ex:
Day 1 file: abc2005_04_10.txt
Day 2 file: abc2005_04_11.txt
April 13, 2005 at 9:19 am
one way of doing this in DTS is though .ini file
1. create a ini file with following tags
[source]
FileName=c:\abc2005_04_10.txt
2. In Dts package create a Activex task with following code.
Function Main()
Dim gsConfigINIPath
gsConfigINIPath = DTSGlobalVariables("gsConfigINIPath").Value
Dim oPKG
Set oPKG = DTSGlobalVariables.Parent
Dim oAssignments
Dim oAssignment
'DTSTask_ DTSDynamicPropertiesTask_1 is the task described as the 'Properties From INI File task
Set oAssignments = oPKG.Tasks"DTSTask_DTSDynamicPropertiesTask_1").CustomTask.Assignments
For Each oAssignment In oAssignments
oAssignment.SourceIniFileFileName =gsConfigINIPath
Next
Main = DTSTaskExecResult_Success
End Function
3. create a dynamic properties task where you can set properties for source
through .ini file name
and have a usual data transformation task.
So each day you just have to change the filename in .ini file.
HTH
April 13, 2005 at 9:25 am
This looks really good, but It has to be dynamic. I have to load and leave.
Thanks for the heads up.
April 14, 2005 at 12:40 am
I suppose you want load the file into a table. You can do this like this :
declare @file varchar(1024)
set @file = '''C:\abc2005_04_10.txt'''
exec ('bulk insert tempdb.dbo.test from ' + @file )
Or with a sp :
create procedure dbo.usp_loadabc
(
@file varchar(1024)
) as
begin
exec ('bulk insert tempdb.dbo.test from ''' + @file + '''')
end
go
exec dbo.usp_loadabc 'C:\abc2005_04_10.txt'
go
Bert
April 14, 2005 at 2:11 am
You Can do something like this using an activex script
Function Main()
Dim oPKG
Set oPKG = DTSGlobalVariables.Parent
Dim oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
'vars for date calcs
dim dt, sdt
dim dtyr, dtm, dtd
dim dtlimit
'filename
dim filename
dt = DTSGlobalVariables("gdtCalendardate") ' get calendardate from Global variables
' construct string of date
dtyr = cstr(datepart("yyyy", dt))
dtm = cstr(datepart("m", dt))
if len(dtm) = 1 then dtm = "0" & dtm
dtd = cstr(datepart("d", dt))
if len(dtd) = 1 then dtd = "0" & dtd
sdt= dtyr & "_" & dtm & "_" & dtd
filename = DTSGlobalVariables("gsfilePath") & "abc" & sdt & ".dat"
' gsfilepath is a dynamic variable which stores the path of the file
If oFSO.FileExists(Filename) Then
opkg.connections("textSource").DataSource = Filename
exit do
end if
'textsource is connection to the file
Main = DTSTaskExecResult_Success
End Function
HTH
Meghana
April 14, 2005 at 2:20 am
I assume your file is always abcYYYY_MM_DD.txt - in which case use an ActiveX script as first step in the DTS job to make up the file name (you can use "Date" function to get the date and Month,YEar and Day functions to get the parts of date you need then build up the format of file name you have from this)) and store it to a global variable then use a Dynamic Properties task to set the connection datasource for the text file to use this file name rather than one it was designed with
James Horsley
Workflow Consulting Limited
April 14, 2005 at 6:30 am
thanks for all the info....I think Meghana's script can help alot.
April 14, 2005 at 8:33 am
Hi,
You may already have your solution. But, if you can get the
abc-2004-04-10.txt in a folder by itself you could just copy it to a file with a
static name of your choosing each day. copy *.txt SameNameEveryDay.txt
Teague
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply