October 23, 2009 at 6:48 am
Hi All,
I have to send data from a flat file to a sql server table using SSIS package. The problem is that the flat files are in an unshared folder on the server and I don't want the clients to have access to that folder.
If I run the package from my VB .Net solution, it will only be able to access the text files if they are on the client or in a shared folder on server. So I want to run SSIS package from the sql server, so it can access the folder on itself.
I think that the only way is using sp_start_job, because I don't have permissions to run cmp_shell.
If I'm right, please tell me how to do that and how pass parameters like the source file name to the package using stored procedure.
Thanks a lot,
Simin
November 16, 2009 at 10:03 pm
Couple thoughts..
1. You can share a folder and control who has access to it, so you can access it and the server can access it but others can't..
2. You don't need to call xp_cmdshell to start an SSIS package, SQL Agent can do it for you.
3. I'm not sure why you would run a package from a .net solution, it would be better to reach out to SQL and start a job to start the SSIS..
CEWII
November 16, 2009 at 10:34 pm
Have to agree with Elliot on this one..
Have your server/network engineers assist you with the permissions on the folder, rather than making it more complicated for youself.
Also consider hosting the file on a FTP; using ftp connect with ID and Passw to retrieve the file?
...
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
November 17, 2009 at 10:05 am
Henrico Bekker (11/16/2009)
Also consider hosting the file on a FTP; using ftp connect with ID and Passw to retrieve the file?
I see ftp as more of a hinderance than a solution. If you don't have any other options then ok, but internally between systems that support it, reference the files by UNC..
CEWII
November 17, 2009 at 10:53 am
Thanks a lot Elliot and Henrico,
I agree with you on running the package from SQL Server by starting the job,
but I'm in trouble for passing parameters to it.
Because the source text file name is not fixed.
If you have a solutiond I'd be glad hearing from you.
November 17, 2009 at 1:59 pm
simin811 (11/17/2009)
but I'm in trouble for passing parameters to it.Because the source text file name is not fixed.
Ok, valid question.. I will offer a solution that is dependent on the package only running a single copy at a time.
You have four choices as I see it (off the top of my head).
You could manipulate the job for each run using the sp_update_jobstep stored procedure to manipulate the command executed. I don't like this option because of security concerns.
The second option is to configure the package to read the filename from a table and then use that in an expression to feed the flat-file source connection string.
A third option is to write a dtsConfig file that contains the filename and the package would use the config file to set the filename, this one is less friendly because you have to write a file.
The fourth option is predicated on there either being only a single file with a known filename pattern or some ability to keep track of filenames you have already processed, you could use a ForEachLoop container to walk through the files in the directory and decide whether or not you are going to process them. I don't like this one for several reasons, the biggest is that is not particularly efficient and much harder to implement.
Short answer I like Option 2.
If you'd like more personal help you can message me directly.
CEWII
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply