0

There is one package that all it does is move files from one location to another.

Now there are parameters which is stored in a SQL Server table which identifies different locations.

The goal is that there will be different SQL Server Agent jobs that will be scheduled and run the same SSIS package. But the key here is that depending on the parameters that are used as inputs based on which SQL Server Agent job runs the parameters will have a different value which will update the SSIS package to move files to the correct location.

For example: there is a variable called Location which can hold parameter values depending on the type of job that is run. Let's say there are 2 values right now first and second.

When you run agentjobfirst, you will only get the parameter which in this case is "first" which will be stored in the Location variable.

Then it will run the job and move firstFile to drop/first/firstFile.txt

When you run agentjobsecond, you will only get the parameter which in this case is "second" which will be stored in the Location variable.

Then it will run the job and move secondFile to drop/second/secondFile.txt

The agentjobfirst cannot call secondFile since it's not the right parameter value.

How do I do this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Junsh
  • 37
  • 5
  • What have you tried so far, and/or what is not working? This question is very much a "how do I use SSIS?" question, which is not really what StackOverflow is intended for. Having variables within an SSIS package that you can either set within the package, set as parameters of the SQL Agent Job, or set via values returned from a SQL query, is pretty standard SSIS stuff that you can read about in Microsoft's own docs (https://learn.microsoft.com/en-us/sql/integration-services/integration-services-ssis-variables?view=sql-server-ver16). I'd suggest you start there – Craig Aug 23 '23 at 22:43
  • I already have a package that is running properly when passing parameters. And having one job runs that package which passes those parameters but the problem is that those parameters are accessed through a loop. What I am trying to do is based on which agent job I run it will only get one parameter and will run the package with that singular parameter. That is what i don't know how to do. Is there a way to set parameters on a sql agent job scheduler? – Junsh Aug 23 '23 at 22:56
  • 2
    when you setup the SQL Agent Job and add an "SSIS Package" step, there is a "Configuration" tab, where you can define parameter values that will apply when that SQL Agent job step executes the package (ie. you could have multiple steps that execute the same package, but each step passes different parameter values). https://learn.microsoft.com/en-us/sql/integration-services/packages/sql-server-agent-jobs-for-packages?view=sql-server-ver16 – Craig Aug 23 '23 at 23:35
  • 1
    Sounds like you just hard code the particular parameter inside the particular job using this method https://stackoverflow.com/questions/48511517/is-it-possible-to-pass-parameters-to-a-dtsx-package-on-the-command-line – Nick.Mc Aug 24 '23 at 02:35
  • _the problem is that those parameters are accessed through a loop_ if you have five sets if parameters and five jobs then it makes sense to hard code those parameters in each job. Alternatively create a master package that loops around your parameters and calls the child package and call it with one job – Nick.Mc Aug 24 '23 at 11:22
  • That worked configuration tab. Didn't know about this before hand. Thanks – Junsh Aug 24 '23 at 18:39

0 Answers0