Passing Job to a JobStep

Back in the day of COM and DMO. You could create a COM component, create an SQL job, and using an 'AcitveX Script' job step pass the Job to the component.

The component could then lookup the job schedule using DMO to figure out how long it should run.

Now in the days of SMO and CLR. I want to pass the Job to a CLR Stored Procedure as part of a Transact SQL step... (without hard coding the JobId in the script)

any help is appreciated...

rich

Well, I guess I'll have to do some extra work.

I will automate the job creation and pass put the JobId into the Trasact SQL.

The reason I need this is that the sproc is long running and manages it's own schedule. After it executes it determines the next runtime and updates the schedule on the job

rich

sql

Passing data to a custom component at runtime.

I have a question about what is allowed and what isn't its related to a raneg lookup question (http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=95372) and engine threads (http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=100596).

So one answer to the range lookup is to provide a script component that does the lookup. The challenge is obtaining the data for the lookup. You could write some code that took a sql connection or a flat file connection etc and read the data but that means you are fixed to the connection. Or you could do what I am about to suggest.

If I have a custom component that has 2 inputs. one to take the lookup values and one that takes the flow that contains the values to lookup.

Now the challenge is to get the lookup array populated before processing the other flow. My suggestion is to sleep the process input thread for the main data flow until the buffers for the other input has completed. So is the following good or bad ?

bool bLookupArrayLoaded = false;

public override void ProcessInput(int inputID, PipelineBuffer buffer)
{

bool bCancel = false;

if (inputID == lookupInputID )
{
//Do we have any rows
if (!buffer.EndOfRowset)
{
//Loop through the buffer to load the lookup array
while (buffer.NextRow())
{
//Populate the llokup array
}
}
else
{
//no more rows flag the array loaded
bLookupArrayLoaded = true;
}
}
else
{
//Has the array been loaded, if not sleep
while (!bLookupArrayLoaded ) Thread.Sleep (1000);

//process rows of the main data flow
if (!buffer.EndOfRowset)
{
//Loop through the buffer
while (buffer.NextRow())
{
//do stuff
}
}

}
}

You should not do this. If you mark your output as synchronous then your second input will never be called. If you mark is as asynchronous then it may or may not be called depending on the execution plan and more likely than not it will never get called, although you could test it out for every package you use the component it. I would strongly advise against this approach since it is a deadlock waiting to happen.

Thanks,
Matt|||Thats what I thought. But it works, is suspect because the input with the lookup values isn't synchronous with any output. So a seperate thread is used for that input and another for the other input.

I understand your point, it would be good if this was guaranteed as it means the component can use the power of the SSIS to load data with out the need to understand anything but an SSIS buffer.

I suppose this needs to be one of those items for Best Practice Analyser.

Pass data in script component when no transformation needed

Hi,
I have 56 fields coming into the input of an script component, The need for script component was to just to check if one of those 56 columns has a valid date or not, If valid it will parse and put in an output date column, if not, it will put in NULL.

The 55 fields should be passed on. I dont really wanna write code and define output columns. How do I do this ?

Any input in this would be appreciated.

Thanks,As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.|||

jwelch wrote:

As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.

To expand a little, as long as you set the script component to be a "transformation," this is true.

You'll be able to get at them by using Row.XXXX

Just make sure that they are set to READWRITE in the properties when you select the columns to be used in the script component. That way you can assign a value.

The PROBLEM is that you can't change the metadata, so if all of your 55+ columns are strings, but can contain dates, you won't be able to output a datetime data type using the same column name. You'll have to create a NEW output column name, which will have to be done by creating new outputs. You can, however, convert a datetime field to a string inside the script component and will then be able to reuse the column.

Does this make sense?|||

Phil Brammer wrote:

Does this make sense?

Makes me confuse, because I am able to dominate columns by using Script component. I just check its checkbox and it appears in Input list, then I add output column with same name and it works.

Are you talking about something else ?|||

It sounds like you are using an asynchronous output, as a synchronous output throws an error if you create an output column with the same name as an input column.

You might try using a synchronous output. Go to the Inputs and Outputs page of the property dialog, and add an output. Select the new output, and in the properties set the SynchronousInputID to the input for the component. All of the input columns will be available on the synchronous output.

Pass data in script component when no transformation needed

Hi,
I have 56 fields coming into the input of an script component, The need for script component was to just to check if one of those 56 columns has a valid date or not, If valid it will parse and put in an output date column, if not, it will put in NULL.

The 55 fields should be passed on. I dont really wanna write code and define output columns. How do I do this ?

Any input in this would be appreciated.

Thanks,As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.|||

jwelch wrote:

As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.

To expand a little, as long as you set the script component to be a "transformation," this is true.

You'll be able to get at them by using Row.XXXX

Just make sure that they are set to READWRITE in the properties when you select the columns to be used in the script component. That way you can assign a value.

The PROBLEM is that you can't change the metadata, so if all of your 55+ columns are strings, but can contain dates, you won't be able to output a datetime data type using the same column name. You'll have to create a NEW output column name, which will have to be done by creating new outputs. You can, however, convert a datetime field to a string inside the script component and will then be able to reuse the column.

Does this make sense?|||

Phil Brammer wrote:

Does this make sense?

Makes me confuse, because I am able to dominate columns by using Script component. I just check its checkbox and it appears in Input list, then I add output column with same name and it works.

Are you talking about something else ?|||

It sounds like you are using an asynchronous output, as a synchronous output throws an error if you create an output column with the same name as an input column.

You might try using a synchronous output. Go to the Inputs and Outputs page of the property dialog, and add an output. Select the new output, and in the properties set the SynchronousInputID to the input for the component. All of the input columns will be available on the synchronous output.