July 26, 2007 at 7:59 am
I shall explain my situation (SQL 2000)
I have a folder A, which contains excel sheets with the following name Services wXX.xls, XX stands for the number of the week.
Now I want to import all these excel sheets with the different weeks into an existing table.
And after that I what to move all those imported files into folder B, so I know these files are imported.
Each week I receive new excel sheets which i put in folder A and I want to schedule my job to import the new excel sheets etc. etc.
Hopefully someone can advise/help me
July 26, 2007 at 10:48 am
a vbs script sets a global variable to create your tablename.
ww= datepart(ww,date())
DTSGlobalVariables("importpath").Value = "\\import path to file\"
DTSGlobalVariables("exportpath").Value = "\\export path to file\"
DTSGlobalVariables("importname").Value = "w" & ww & ".xls"
a dynamic properties task sets the import file of your transform data task to the global variable
a vbs script moves the file to the new folder
set fso = createobject("scripting.filesystemobject")
if fso.fileexists (DTSGlobalVariables("importpath").Value & DTSGlobalVariables("importname").Value) then fso.movefile DTSGlobalVariables("importpath").Value & DTSGlobalVariables("importname").Value, DTSGlobalVariables("exportpath").Value & DTSGlobalVariables("importname").Value
July 26, 2007 at 4:04 pm
mrpolecat, could you please be more specific> I really do not understand how I can use your code?
I have to have some more help with this one, I learn quickly I'll promise.
I changed some of your code into for me usefull code. i made i bold, I also add some questions
My first question is, where do I have to program this code?
DTS? Job with ActiveX Script? please advise
a vbs script sets a global variable to create your tablename. how can I do this? and where?
ww= datepart(ww,date())
DTSGlobalVariables("importpath").Value = "F:\Import"
DTSGlobalVariables("exportpath").Value = "F:\Export"
DTSGlobalVariables("importname").Value = "Services" & ww & ".xls"
a dynamic properties task sets the import file of your transform data task to the global variable, how can I do this?
a vbs script moves the file to the new folder. how can I do this?
set fso = createobject("scripting.filesystemobject")
if fso.fileexists (DTSGlobalVariables("importpath").Value & DTSGlobalVariables("importname").Value) then fso.movefile DTSGlobalVariables("importpath").Value & DTSGlobalVariables("importname").Value, DTSGlobalVariables("exportpath").Value & DTSGlobalVariables("importname").Value
July 26, 2007 at 5:35 pm
Take a look at this article.
http://www.sqldts.com/246.aspx
--------------------
Colt 45 - the original point and click interface
July 27, 2007 at 4:26 am
I try to understand that article.
Where are DTSGlobalVariables("gv_ArchiveLocation")
and DTSGlobalVariables("gv_FileLocation")
defined? or do i need to change "gv_FileLocation" into F:\Import?
Where can I assigne the filename that only files start with Serrvices* are imported in that folder with different weeks?
July 27, 2007 at 11:42 pm
Did you download the sample package?
--------------------
Colt 45 - the original point and click interface
July 30, 2007 at 4:07 am
yes I download the sample package
July 30, 2007 at 4:17 am
Then it should show you what needs to go where.
--------------------
Colt 45 - the original point and click interface
July 30, 2007 at 4:35 am
not everything is clear to me.
where are those gv stored?
July 30, 2007 at 9:09 am
If you don't know where global variables are stored then you need to a lot more reading and learning than can be provided in a simple forum response.
Start of with this to learn about Global Variables. http://www.sqldts.com/280.aspx
There are also a few other tutorials and examples on that same website.
--------------------
Colt 45 - the original point and click interface
July 31, 2007 at 2:54 am
oke I found Where I can find GV and what they to, I followed some tutorials.
If Im correct only files with the name F:\Files\Copy (10) of Copy of Copy of Copy of Copy of Copy of File1.txt are imported?
and that are txt files, I want to import xls files.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply