February 1, 2007 at 2:26 pm
Can anyone please let me know if there is a quick solution to my problem. I am loading a file whose filename is (servername.domainname.datetimestamp.) into a sql table. this sql table has columns like servername, domainname and so on. Is it possible to use an active xscript to get the servername and filename used into teh sql table. Has anyone got the code for it or is there any other easy way to resolve this. Any help will be greatly appreciated.
TIA
February 1, 2007 at 3:56 pm
Before I dig in, let me make sure I understand your problem. I think that this can be done.
How do you get this file? Is there a folder that contains files that are named using the scheme you gave? Are you pulling all of the files in?
I assume that a sample file would have a name like MyServer.MyDomain.20070201. You will be putting the contents of this file into a SQL table. You will put MyServer into the ServerName column, MyDomain into the DomainName column and 20070201 into some column. The rest of the columns in the table will be filled with information from the text file.
Does this sound accurate?
Russel Loski, MCSE Business Intelligence, Data Platform
February 1, 2007 at 3:56 pm
You could use an activeX script to get the file name and then set a global variable. You can parse the file name into the component parts and then set other global variables.
Untested but take a look at http://www.sqldts.com for more activeX script that work with file objects:
Option Explicit
Function Main()
Dim pkg
Dim conTextFile
Dim fso
Dim fold
set pkg = DTSGlobalVariables.Parent
set conTextFile = pkg.Connections("Text File (Source)")
set fso = CREATEOBJECT("Scripting.FileSystemObject")
set fold = fso.GetFolder(DTSGlobalVariables("gv_FileLocation").Value)
for each fil in fold.Files
DTSGlobalVariables("gv_FileFullName").Value = fil.path
next
conTextFile.DataSource = DTSGlobalVariables("gv_FileFullName").Value
Main = DTSTaskExecResult_Success
End Function
SQL = Scarcely Qualifies as a Language
February 2, 2007 at 6:58 am
Exactly Russel. You are right. I need the servrname to be inserted into servername column , same with domain name also. I dont need the timestamp colum,n though. Do you have an example in activexscript taht i could possibly use. I need to loop thru every single file in a directory and for each file pass servername, domain name to another DTS package which potentially will load thsi into a sql table. the inner DTS loads some other data also along with server and domain name. Now this should work for every single file that exists in the path that i give initialy. your help will be greatly appreciated .
TIA
February 2, 2007 at 10:01 am
There are so many things to this. I would probably take a day to put it together. I have done similar things in different projects, but not all in the order you ask. Let me see if I can start you off.
Look at http://www.sqldts.com/246.aspx. I don't have the environment or time to test it out, but I respect the author's ability. Note that this is very similar to Carl's solution.
Next, you need to get the values for server name and domain name. You need two global variables: gvServer, gvDomain.
In his ShouldILoop method in step 4 you need to get the file name and parse it:
dim sName
dim sServer
dim sDomain
dim pos1
sName = fil.Name
pos1 = instr(sName,".")
if pos1 > 0 then
sServer = left(sName , pos1 -1 )
sName = mid(sName, pos1 + 1)
else
sServer = ""
end if
pos1 = instr(sName,".")
if pos1 > 0 then
sDomain = left(sName , pos1 -1 )
else
sDomain = ""
end if
DTSGlobalVariables("gvServer").Value = sServer
DTSGlobalVariables("gvDomain").Value = sDomain
Now, in your transformation that imports from the text file. Add an ActiveX transformation for the Server and Domain columns. Add the code:
DTSDestination("ServerCol") = DTSGlobalVariables("gvServer").Value
DTSDestination("DomainCol") = DTSGlobalVariables("gvDomain").Value
Russel Loski, MCSE Business Intelligence, Data Platform
February 2, 2007 at 12:16 pm
It can also be done with just T-SQL.
Declare
@cmdshell nvarchar(500), @Path nvarchar(200), @FileName nvarchar(100)
Set
@Path = 'D:\Location\Of\Directory\'
Create Table
#Files (FileID int identity(1, 1) not null primary key, FileName nvarchar(255) null)
/*
Get a directory listing of all files to determine the newest one
/O-D Sort directory list by date descending
/TW Date column sorted on = date last written. Change 2nd letter to C for Date Created.
/B Bare format: no attributes, descriptions, or summaries. Just file names.
*/
Set
@cmdshell = 'dir ' + @Path + '*.* /O-D /TW /B'
Insert
Into #Files (FileName)
Exec
xp_cmdshell @cmdshell
/* Delete the empty or otherwise invalid rows returned by xp_cmdshell */
Delete
From #Files
Where
FileName Is Null Or Charindex('.', FileName) = 0
/* File names are sorted by date written descending, so first file in list is newest */
Select
@FileName = FileName
From
#Files
Where FileID = 1
And think you can figure out the rest from there. You now have the file name in a variable that you can parse and insert.
One more hint. There is a function called ParseName which is used for Parsing the 4 part name for objects. It can be used to parse any string delimited by decimals that are 4 or less parts.
Declare
@ParseMe nvarchar(100)
Set
@ParseMe = 'MyServer.MyDomain.20070201'
Select
ParseName(@ParseMe, 1), ParseName(@ParseMe, 2), ParseName(@ParseMe, 3)
----------- ------------ ----------
20070201 MyDomain MyServer
(1 row(s) affected)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply