March 31, 2014 at 5:03 pm
In a few months I will be installing a SQL Server package at a client's office. I've been doing SQL Server work for 20 years but this is a new thing for me as far as doing SSIS work, so I'm feeling my way around here and thinking ahead for how this will all need to work once I get everything installed at project completion.
The main user likely has SQL Server Management Studio installed on his desktop PC, but the actual SQL Server installation is on a separate machine.
I'll have a job setup that will be scheduled to run automatically each evening at 7 p.m. to kick off the SSIS package.
The package will basically search for new Excel files that have been placed out in a specific folder, move those files to a different folder to get them ready for processing, then the data in those files will be moved into SQL Server.
Seems to complicate things though with the fact that the files that SSIS will be searching for will reside in a folder on the one individual's desktop PC, as they are automatically synchronized down from a Sharefile location online. I'm curious how the job that will run on the SQL Server will access the files that reside on the one guy's desktop PC that's separate from the machine running SQL Server and move the files to another location on the one guy's machine.
This may all be simple but I'm having trouble processing the need for interaction between the Excel files on the guy's machine and the SSIS job that will reside on the separate SQL Server machine and the fact that the SSIS job will be needing to read/write to files on the separate machine.
Thanks for anything that can be communicated to me to clarify my thoughts here.
April 1, 2014 at 10:26 am
It can be run. But that's not a good approach.
Instead of having the dependencies on the client desktops, use a network drive for storing those input and output files.
If that's not possible, create a shared folder on those 2 desktops and grant the account running SSIS package access to those shared folders. But for this to work, those 2 desktops have to be up & running every time when the job runs.
--
SQLBuddy
April 1, 2014 at 2:45 pm
Which version of SSIS are you using? Prior to SSIS 2012 (project deployment model), the SSIS package is executed on the machine that calls the package. In this case, the client machine. You just need to make sure DTEXEC is installed on that machine.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply