February 27, 2012 at 6:02 am
Hello,
I'm not sure to what category this question belongs, so i'm sorry if I misplaced it.
What I want is to use C# code for timed jobs/stored procedures. I know it is possible to execute a .exe from within a job, but it would be great if the C# code could be actually created within the job itself. Is this possible? What are the other options for C# in SQL Server?
Goal: Check a directory on a server, to see if there are files that need to be imported into the database. If so, import the files. This has to happen at a specific hour of the day.
Problems: Threading of tasks and importing, Editing the data (creating keys from data for example), difficult file type (.txt with ~ separating columns), change program easily for each customer.
What is the best way to do this?
Thanks in advance
(btw, sorry for my English)
Alex
February 27, 2012 at 6:06 am
you could create an ssis package and give it a script task which will execute VB.net or C# code and then schedule the package as a job
February 27, 2012 at 6:09 am
Wouldn't it be easier to use a .EXE inside the job? What is the benefit of SSIS?
Could you maybe give a very simple example of what you suggested?
BTW, thank you for your time
February 27, 2012 at 6:18 am
we connect to a 3rd party ftp site, download a zip file using a http connection manager
we then have a C# script which checks the unzipping directory and clears it out, unzips the downloaded file, renames them to a specific filename
after that data flow tasks are used to upload the data into the DB
how would you call the exe in the job, by using xp_cmdshell?
we have a policy which prohibits us from using xp_cmdshell so we went for the SSIS way of doing things
February 27, 2012 at 6:25 am
Why is using the cmdshell prohibited? Is there something wrong with it?
February 27, 2012 at 6:34 am
AlexH89 (2/27/2012)
... but it would be great if the C# code could be actually created within the job itself. Is this possible?
It would be completely mad! :w00t:
But it's possible!
If you drink enough of strong stuff, you can create a SQL job which will use T-SQL to generate C# code, save it to file, call C# compiler to compile it and then execute it!
But the stuff you drink should be at least 40% and you should dring a lot of it and without food! :hehe:
T-SQL purpose is to operate with data not generate c# executables.
The use of managed .net code (eg. C#) is in writing CLR functions and stored procedures which you execute from within T-SQL.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply