Please help. I'm completely new to SSIS, and I was hoping someone could point me in the right direction(s).
I am developing a winforms client app, and I need to be able to provide the user with the ability to import data in CSV format into our application's database.
I'd like to use SSIS if possible, as long as what I am trying to do isn't near impossible.
I'm thinking of a UI where the user can specify a flat file (CSV) to upload and be processed by an SSIS package on a remote database server.
This package will be responsible for validating the CSV file and inserting data into the database as appropriate.
Is there a way I can:
launch an SSIS package remotely, from a winforms app on the client machine that is not running SQL server (preferably asynchronously) -- and pass it some parameters, including the import file itself
(optionally) provide progress feedback to the client pc to let them know it is being processed
display a nice SSRS report on the client upon completion that tells them exactly what the success/failure of the import was (how many rows processed, which ones failed and why, etc)
Any helpful examples, links, etc would be most appreciated.
Thanks in advance!
1 - How about hosting it on the SQL Server, and start ith through T-SQL. The best way to get the asycn behaviour is to use a job.
How to: Run a Package Using a SQL Server Agent Job
(http://msdn2.microsoft.com/en-us/library/69d3958c-6abc-41ae-8428-312917867c9a.aspx)
2 - You will not get anything back from the job, as it you have no handle back to it. You could turn on logging in your package, perhaps to SQL Server, and then query the table on a polling basis to provide progress to a user.
3 - A combination of SQL logging and some custom logging within the package and you should be able to achieve this.
