excel worksheet renaming -- sql server dts

rshivaraman NO[at]SPAM gmail.com
6/22/2007 8:37:57 AM

the following code works for me : i do not have excel installed on the
server.
I get the name of the file that needs to be renamed from a dbtable
And then if that file exists in a directory, i rename it to a standard
name, so that i can call it in a transformdata task.

HTH,

thanks
RS

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()

Dim vbFileName
Dim FSO
Dim File
Dim ConStr
Dim rs
Dim sql

ConStr = "Provider=sqloledb;" & _
"Data Source=localhost;" & _
"Initial Catalog=dbname;" & _
"User Id=uid;" & _
"Password=pwd"

set rs = createobject("ADODB.RecordSet")
sql = "Select FileName from tableA where Status = 'DTSed' "
rs.open sql,ConStr,0,1
If rs.EOF=FALSE and RS.BOF= FALSE Then
vbFileName = rs("FileName")
End If
rs.close
set rs = nothing

Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists("D:\Path_here\" & vbFileName) then
Set File1 = FSO.GetFile("D:\Path\" & vbFileName)
File1.Name = "insert filename you want here"
End If

set FSO = nothing
set File1 = nothing
set File2 = nothing

Main = DTSTaskExecResult_Success
End Function

gremlin321
6/22/2007 10:30:00 AM

renaming the file is not a problem, the problem is to rename worksheet, that
can be of any value. excel reader expects a specific name(table) so i need to
prep file before i can read from excel

[quoted text, click to view]

rshivaraman NO[at]SPAM gmail.com
6/22/2007 11:26:06 AM

I am having a similar problem.
i had asked this questions previously, but did not get an answer from
this group.
Do you know how to delete a worksheet, only on the condition that it
exists ?

I have need to put data into an excelworksheet called 'output_data'.
so need to create the sheet which i do.
but if it is already there, then i need to drop it
i know execsql commands which are DROP TABLE `output_data' and CREATE
TABLE `output_data'
But if i drop it when it is not there, i get an error.

Also, i dont have to drop it, i can also delete the contents of the
worksheet, again only if it is there
Do you know how to do this

thanks
RS

gremlin321
6/25/2007 6:36:01 PM

well, the easy way would be to have excel installed and just add activex task
to your package. once you create excel application object you can manipulate
worksheets any way you want, you can go through collections and check for
names.counts etc act act accordingly.

[quoted text, click to view]

rshivaraman NO[at]SPAM gmail.com
6/25/2007 7:18:56 PM

yes, i did see some code which calls the excel application, i think in
sqldts.com.
but like i said, i dont have excel on my server and for all practical
purposes, they might not install it for me, so if you do come across a
solution, let me in on it

thanks
RS

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server dts

sql server dts : excel worksheet renaming