I need to pass a parameter to a job. It is my understanding the best way to do this is to create a table that stores the parameter and then call on that to create the parameter in the job.
I have an Access front end that runs a stored procedure. Can I have that stored procedure create a temporary table to save the data in? Or is the table only available until the stored procedure is finished executing?
Maybe I can start the job from the stored procedure and use the parameter during the execution of the stored procedure?
So I thought I could do this through the set values tab of the job. But I am not sure how I get the filename value that I wrote to the temp table into the ssis package.
I have the variable path in the set values tab but I am not sure how I specify the value. I can't do a select from there.
I am still interested to hear if there is another way to handle this but I ended up creating an Execute SQL Task in my package that assigns the value in the temp table to my package variable.
A stored procedure can return one value. However, that one value can be a table. A global temp table is accessible to everyone and defined as ##tableName
I may have spoke too soon. The data source saved in my job step was still an value in my package. I had removed the value but didn't re-import the package to SQL. Now that I did that it is not importing anything at all.
In that case, you may want to do it as a scalar function. A scalar function can be called within SQL from where-ever you are, but I am unsure if or how Access implements them. Scalar means returning a single value. I use them for simple row driven things like formatting a phone number or creating a formatted name from pieces. Since you can use it in a case statement, you only want to do simple, quick things. Larger things being called per row will bog you down dramatically.