Importing Data Automatically AND Run SQL task too

Question

Post reply

Importing Data Automatically AND Run SQL task too

Connor Brannigan

SSC Rookie

Points: 29
More actions
December 7, 2004 at 5:20 am

#163263

Hi all,
I am a complete newbie to this and thus have no idea what i am doing. This is the problema nd this is how i am currently tackeling it and how i would like to tackle it in the future.
Problem:
I need to import 10 csv files on a monthly basis to a predefined table in my DB. This is easy enough done with a simple import using DTS, however, i need this to be automated so that i dont need to 'hardcode' the import. I have set up a loop that does this ok, but the problem being that the file names vary every month and my supervisor would like this done in one file, rather than have 12 files (one per month). I have code that will get all the files names in a directoy, but have no idea how to pas it into a global variable and use this information to set the file name - the code im using to do this is:
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("C:\mydir")
For Each file In f.Files
DTSGlobalVariables("FileName") = f.Name
Next
The next big problem is this - once all these files are imported, i need to run a SQL task to update fields that are in the newly imported data. I was just wondering if you can use SQL in teh VBscript and populate certaine contraints within it with global variables, again the code i am using for the update is:
UPDATE CAF
SET Euro_Value = (SELECT [Value] FROM [CAF]
WHERE ([Market_ID] = '*') AND ([Cost_Code] = '*') AND (Euro_Value IS NULL )) /
(SELECT [ExRate] FROM [CURRENCY]
WHERE ([Currency_ID] = '*'))
WHERE (Market_ID = '*') AND (Cost_Code = '*') AND (Euro_Value IS NULL)
The asterisks are replaced with relevant codes in order to pull the correct information. I would like to dynamically replace the asterisks with gloab variables - can this be done?
So, basically, at the moment the files are imported one at a time, when the files are finished importing, the sql tasks run one at a time. This all works fine, but as i said, I need to be able to fully automate it so that I am not hardcoding the information for the import that is done every month.
Any help that you can give will be much appreciated and will make this current project a lot easier that it currently is. If you need to know anything else about this, dont hesitate to ask, thanks in advance.
Connor B

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Russell Woodham SSC Veteran Points: 243 More actions · Answer 1

Hi Connor

I can't help you with your second problem, but hope this helps with the first one:-

http://www.sqldts.com/default.aspx?246

Russ

Connor Brannigan SSC Rookie Points: 29 More actions · Answer 2

Cheers, Ill give it a shot and try to muddle my way through and hopefully get something that works.

Connor

ButchH Ten Centuries Points: 1073 More actions · Answer 3

Connor,

You didn't state whether your files to import all have a common .EXT, but if they do, you can use a DOS Copy command like this

copy /y drive:\path\*.csv drive:\path\import_me.txt

Then you only have to code for one file to import. The /y after copy lets you overwrite the import_me.txt file. This should go a long way in your automation efforts.

Hope this helps.

Butch

Connor Brannigan SSC Rookie Points: 29 More actions · Answer 4

sorry about being lax in replying to this, thanks to Russ' suggestion i managed to get the auto file rename and import working a treat.

Thanks again

Connor

Russell Woodham SSC Veteran Points: 243 More actions · Answer 5

Connor

You are more than welcome, glad i could help

Russ