I created a parameterized SSIS package to load .csv file data into SQL Server table. The parameter holds the path and file name of the .csv flat file. I would like to create a SQL Agent Job to execute the package and like to call the job through stored procedure. Please any one let me know how I can pass package parameter value (path/filename.csv) during runtime. I can not call SSIS package directly through stored procedure using DTEXEC/SQ because I do not have privileges on XP_CMDSELL.

The stored proc can insert the path into a table (or queue) and then start the job. The package can then retrieve the value from the table. Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Hi There You can either use the suggestion given by Dan or you can use xml configuration for your SSIS package which is very common approach for storing package configuration(the path of your variable etc.) For more info please have a look http://www.mssqltips.com/sqlservertip/1434/using-xml-package-configurations-with-integration-services-ssis/ http://www.simple-talk.com/sql/ssis/xml-configuration-files-in-sql-server-integration-services/ If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

Hi Dan, Thank you very much for the help. I did as you advised and it is working fine. Now I got one more issue. I inserted multiple records in that table and calling the job using loop . In loop first jog is getting started successfully and for next job I am getting following error. Is there any way to call the same job while multiple times at the same time? Job 'spXfer_SRI_LoadFilesProcessData' started successfully. Msg 22022, Level 16, State 1, Line 0 SQLServerAgent Error: Request to run job spXfer_SRI_LoadFilesProcessData (from User A.TM1PLNRev) refused because the job is already running from a request by User A.TM1PLNRev.1

Hi Syad, Thanks for the info. I am new to SSIS and also I do not know XML. So I did as Dan advised and it is working fine Now I got one more issue. I inserted multiple records in that table and calling the job using loop to process row byrow. In loop first jog is getting started successfully and for next job I am getting following error. Is there any way to call the same job while multiple times at the same time? please help me if you have Idea on this. Job 'spXfer_SRI_LoadFilesProcessData' started successfully. Msg 22022, Level 16, State 1, Line 0 SQLServerAgent Error: Request to run job spXfer_SRI_LoadFilesProcessData (from User A.TM1PLNRev) refused because the job is already running from a request by User A.TM1PLNRev.1

You cannot execute multiple instances of a SQL job at the same time.Chuck Pedretti | Magenic North Region | magenic.com

Hi Chuck Pedretti, How can we execute multiple instance of the same job at the same time. Please give me example T-SQL code

You can't What you could do is dynamically create jobs that delete themselves after execution Place to start reading http://msdn.microsoft.com/en-us/library/ms181153(v=sql.105).aspx Chuck Pedretti | Magenic North Region | magenic.com

Instead of starting the job from the proc, you could just insert the rows and run the job on a reoccurring schedule. You can then add a for each loop in the package to process all pending files. As an alternative to a job, you could use the External Activator to run the package whenever files are ready. See thread http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/84b727ad-197b-421d-9478-829fa75c47dd. Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/