Creating 'public' variable inside job
Posted: Thu Jan 21, 2010 3:53 am
Hi
i will try to explain what i am after
In our environment the writting into staging area tables, are to be partitioned (not Information server partitions)
Meaning. i have table named 'StagingData' that i want to load a new days data into.
A stored procedure has been created by the DBA that is to be called with my tablename as parameter, and it will return the name of the partition i am to use.
This partitionname will be my actual tablename in the job to use for the ODBC Connector that writes to the target.
When finished the job calls another stored procedure that swithches the partition on to the 'real' table.
All our jobs are being started by a JobSequence, in order to handle Errors causing abort and a lot of system stuff.
To be able to pass the above mentioned partitionname to use for the current run of the job, i had to add 2 stages to the sequence, first a job that calls the stored procedure, and writes the result to a file, then a 'Execute Command' stage that reads the file and use that in a parameter to the actual load job.
This works fine, but i think it is a clumpsy solution that, if more requirements of the same where i have to add 2 more stages every time, will make the sequence unmanageable.
I have been trying to find a way to instead have a dummy parameter to the load job and the updating the content of this in a pre run functionality but i can not see how i can do that
i have also tried with a DSRoutine that makes the call to the stored procedure and returns the partitionname. this routine i will then add to the 'Tablename' column in the odbc connector, but that it not possible either.
Hope my explanation of the problem was understandable, otherwise i will explain further.
regards
Peter
i will try to explain what i am after
In our environment the writting into staging area tables, are to be partitioned (not Information server partitions)
Meaning. i have table named 'StagingData' that i want to load a new days data into.
A stored procedure has been created by the DBA that is to be called with my tablename as parameter, and it will return the name of the partition i am to use.
This partitionname will be my actual tablename in the job to use for the ODBC Connector that writes to the target.
When finished the job calls another stored procedure that swithches the partition on to the 'real' table.
All our jobs are being started by a JobSequence, in order to handle Errors causing abort and a lot of system stuff.
To be able to pass the above mentioned partitionname to use for the current run of the job, i had to add 2 stages to the sequence, first a job that calls the stored procedure, and writes the result to a file, then a 'Execute Command' stage that reads the file and use that in a parameter to the actual load job.
This works fine, but i think it is a clumpsy solution that, if more requirements of the same where i have to add 2 more stages every time, will make the sequence unmanageable.
I have been trying to find a way to instead have a dummy parameter to the load job and the updating the content of this in a pre run functionality but i can not see how i can do that
i have also tried with a DSRoutine that makes the call to the stored procedure and returns the partitionname. this routine i will then add to the 'Tablename' column in the odbc connector, but that it not possible either.
Hope my explanation of the problem was understandable, otherwise i will explain further.
regards
Peter