EXEC Stored Procedure does not work in SSIS task

#21

OK, Help me understand this:

Your original version had a proc that, before it could do it thing, had to
call a second proc to get some values. The values from the inner proc are
passed upward to the first proc which uses them in its logic.

Yes, you may have a bit of trouble using this methodology in a Data Source.

Here is what I suggest:
Break the two operations up. Have an Execute SQL Task on the Control Flow
that has the following: "EXEC sp_GetTrafficData ?, ?"
Then use the Parameters Mapping page to supply the values it needs. Use the
Result Set page to map the return to Variables in the SSIS Package.
Now in your Source Adapter, use a statement like
"EXEC UPDW_UpdateTraffic_DataMart ?, ?" and use the Parameters button to map
the Package variables to the statement varialbes.

Keep us posted.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"praveen" wrote:

Quote:

Thanks Todd, A stored procedure UPDW_UpdateTraffic_DataMart runs in the SSIS
dataflow task, the parameters required for this procedure are provided by

------Calculate the start and the end date
EXEC sp_GetTrafficData @affiliate_ID, @weekSpan

sp_GetTrafficData is executed in UPDW_UpdateTraffic_DataMart stored
procedure and this is causing problems, if I comment Calculate the start and
the end date
code the SSIS Task Flow works fine.

Thanks
Sword

--
praveen.kumar (AT) aut (DOT) ac.nz

"Todd C" wrote:

Hello;

Do you have two Variables in your Package that hold the values for the two
input parameters?

Set up your Source adapter and put in your command as :

sp_GetTrafficDate ? ?

Then hit the Parameters button. Map the two input parameters to the two
variables as appropriate. (Note: the Parse button WILL return an error but it
should run OK)

--
Todd C

"praveen" wrote:

Hi,
I have a SSIS package
In my source "data flow" task I have a stored procedure (sp_GetTrafficData)
which gets the data and populates the table in the destination task.

The stored procedure sp_GetTrafficData executes another stored procedure the
code is as follows:,

------Calculate the start and the end date
EXEC sp_GetTrafficData @affiliate_ID, @weekSpan

I have tried this also:
EXEC sp_GetTrafficData( @affiliate_ID, @weekSpan)

When I run the SSIS package it gives this error: VS_NEEDNEWMETADATA

I am banging my head aganist the wall, there is no joy so far

Please help

Kind Regards
Swordfish8 (AT) hotmail (DOT) com

#22

OK, Help me understand this:

Your original version had a proc that, before it could do it thing, had to
call a second proc to get some values. The values from the inner proc are
passed upward to the first proc which uses them in its logic.

Yes, you may have a bit of trouble using this methodology in a Data Source.

Here is what I suggest:
Break the two operations up. Have an Execute SQL Task on the Control Flow
that has the following: "EXEC sp_GetTrafficData ?, ?"
Then use the Parameters Mapping page to supply the values it needs. Use the
Result Set page to map the return to Variables in the SSIS Package.
Now in your Source Adapter, use a statement like
"EXEC UPDW_UpdateTraffic_DataMart ?, ?" and use the Parameters button to map
the Package variables to the statement varialbes.

Keep us posted.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"praveen" wrote:

Quote:

Thanks Todd, A stored procedure UPDW_UpdateTraffic_DataMart runs in the SSIS
dataflow task, the parameters required for this procedure are provided by

------Calculate the start and the end date
EXEC sp_GetTrafficData @affiliate_ID, @weekSpan

sp_GetTrafficData is executed in UPDW_UpdateTraffic_DataMart stored
procedure and this is causing problems, if I comment Calculate the start and
the end date
code the SSIS Task Flow works fine.

Thanks
Sword

--
praveen.kumar (AT) aut (DOT) ac.nz

"Todd C" wrote:

Hello;

Do you have two Variables in your Package that hold the values for the two
input parameters?

Set up your Source adapter and put in your command as :

sp_GetTrafficDate ? ?

Then hit the Parameters button. Map the two input parameters to the two
variables as appropriate. (Note: the Parse button WILL return an error but it
should run OK)

--
Todd C

"praveen" wrote:

Hi,
I have a SSIS package
In my source "data flow" task I have a stored procedure (sp_GetTrafficData)
which gets the data and populates the table in the destination task.

The stored procedure sp_GetTrafficData executes another stored procedure the
code is as follows:,

------Calculate the start and the end date
EXEC sp_GetTrafficData @affiliate_ID, @weekSpan

I have tried this also:
EXEC sp_GetTrafficData( @affiliate_ID, @weekSpan)

When I run the SSIS package it gives this error: VS_NEEDNEWMETADATA

I am banging my head aganist the wall, there is no joy so far

Please help

Kind Regards
Swordfish8 (AT) hotmail (DOT) com

#23

OK, Help me understand this:

Your original version had a proc that, before it could do it thing, had to
call a second proc to get some values. The values from the inner proc are
passed upward to the first proc which uses them in its logic.

Yes, you may have a bit of trouble using this methodology in a Data Source.

Here is what I suggest:
Break the two operations up. Have an Execute SQL Task on the Control Flow
that has the following: "EXEC sp_GetTrafficData ?, ?"
Then use the Parameters Mapping page to supply the values it needs. Use the
Result Set page to map the return to Variables in the SSIS Package.
Now in your Source Adapter, use a statement like
"EXEC UPDW_UpdateTraffic_DataMart ?, ?" and use the Parameters button to map
the Package variables to the statement varialbes.

Keep us posted.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"praveen" wrote:

Quote:

Thanks Todd, A stored procedure UPDW_UpdateTraffic_DataMart runs in the SSIS
dataflow task, the parameters required for this procedure are provided by

------Calculate the start and the end date
EXEC sp_GetTrafficData @affiliate_ID, @weekSpan

sp_GetTrafficData is executed in UPDW_UpdateTraffic_DataMart stored
procedure and this is causing problems, if I comment Calculate the start and
the end date
code the SSIS Task Flow works fine.

Thanks
Sword

--
praveen.kumar (AT) aut (DOT) ac.nz

"Todd C" wrote:

Hello;

Do you have two Variables in your Package that hold the values for the two
input parameters?

Set up your Source adapter and put in your command as :

sp_GetTrafficDate ? ?

Then hit the Parameters button. Map the two input parameters to the two
variables as appropriate. (Note: the Parse button WILL return an error but it
should run OK)

--
Todd C

"praveen" wrote:

Hi,
I have a SSIS package
In my source "data flow" task I have a stored procedure (sp_GetTrafficData)
which gets the data and populates the table in the destination task.

The stored procedure sp_GetTrafficData executes another stored procedure the
code is as follows:,

------Calculate the start and the end date
EXEC sp_GetTrafficData @affiliate_ID, @weekSpan

I have tried this also:
EXEC sp_GetTrafficData( @affiliate_ID, @weekSpan)

When I run the SSIS package it gives this error: VS_NEEDNEWMETADATA

I am banging my head aganist the wall, there is no joy so far

Please help

Kind Regards
Swordfish8 (AT) hotmail (DOT) com

#24

OK, Help me understand this:

Your original version had a proc that, before it could do it thing, had to
call a second proc to get some values. The values from the inner proc are
passed upward to the first proc which uses them in its logic.

Yes, you may have a bit of trouble using this methodology in a Data Source.

Here is what I suggest:
Break the two operations up. Have an Execute SQL Task on the Control Flow
that has the following: "EXEC sp_GetTrafficData ?, ?"
Then use the Parameters Mapping page to supply the values it needs. Use the
Result Set page to map the return to Variables in the SSIS Package.
Now in your Source Adapter, use a statement like
"EXEC UPDW_UpdateTraffic_DataMart ?, ?" and use the Parameters button to map
the Package variables to the statement varialbes.

Keep us posted.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"praveen" wrote:

Quote:

Thanks Todd, A stored procedure UPDW_UpdateTraffic_DataMart runs in the SSIS
dataflow task, the parameters required for this procedure are provided by

------Calculate the start and the end date
EXEC sp_GetTrafficData @affiliate_ID, @weekSpan

sp_GetTrafficData is executed in UPDW_UpdateTraffic_DataMart stored
procedure and this is causing problems, if I comment Calculate the start and
the end date
code the SSIS Task Flow works fine.

Thanks
Sword

--
praveen.kumar (AT) aut (DOT) ac.nz

"Todd C" wrote:

Hello;

Do you have two Variables in your Package that hold the values for the two
input parameters?

Set up your Source adapter and put in your command as :

sp_GetTrafficDate ? ?

Then hit the Parameters button. Map the two input parameters to the two
variables as appropriate. (Note: the Parse button WILL return an error but it
should run OK)

--
Todd C

"praveen" wrote:

Hi,
I have a SSIS package
In my source "data flow" task I have a stored procedure (sp_GetTrafficData)
which gets the data and populates the table in the destination task.

The stored procedure sp_GetTrafficData executes another stored procedure the
code is as follows:,

------Calculate the start and the end date
EXEC sp_GetTrafficData @affiliate_ID, @weekSpan

I have tried this also:
EXEC sp_GetTrafficData( @affiliate_ID, @weekSpan)

When I run the SSIS package it gives this error: VS_NEEDNEWMETADATA

I am banging my head aganist the wall, there is no joy so far

Please help

Kind Regards
Swordfish8 (AT) hotmail (DOT) com

#25

OK, Help me understand this:

Your original version had a proc that, before it could do it thing, had to
call a second proc to get some values. The values from the inner proc are
passed upward to the first proc which uses them in its logic.

Yes, you may have a bit of trouble using this methodology in a Data Source.

Here is what I suggest:
Break the two operations up. Have an Execute SQL Task on the Control Flow
that has the following: "EXEC sp_GetTrafficData ?, ?"
Then use the Parameters Mapping page to supply the values it needs. Use the
Result Set page to map the return to Variables in the SSIS Package.
Now in your Source Adapter, use a statement like
"EXEC UPDW_UpdateTraffic_DataMart ?, ?" and use the Parameters button to map
the Package variables to the statement varialbes.

Keep us posted.

--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"praveen" wrote:

Quote:

Thanks Todd, A stored procedure UPDW_UpdateTraffic_DataMart runs in the SSIS
dataflow task, the parameters required for this procedure are provided by

------Calculate the start and the end date
EXEC sp_GetTrafficData @affiliate_ID, @weekSpan

sp_GetTrafficData is executed in UPDW_UpdateTraffic_DataMart stored
procedure and this is causing problems, if I comment Calculate the start and
the end date
code the SSIS Task Flow works fine.

Thanks
Sword

--
praveen.kumar (AT) aut (DOT) ac.nz

"Todd C" wrote:

Hello;

Do you have two Variables in your Package that hold the values for the two
input parameters?

Set up your Source adapter and put in your command as :

sp_GetTrafficDate ? ?

Then hit the Parameters button. Map the two input parameters to the two
variables as appropriate. (Note: the Parse button WILL return an error but it
should run OK)

--
Todd C

"praveen" wrote:

Hi,
I have a SSIS package
In my source "data flow" task I have a stored procedure (sp_GetTrafficData)
which gets the data and populates the table in the destination task.

The stored procedure sp_GetTrafficData executes another stored procedure the
code is as follows:,

------Calculate the start and the end date
EXEC sp_GetTrafficData @affiliate_ID, @weekSpan

I have tried this also:
EXEC sp_GetTrafficData( @affiliate_ID, @weekSpan)

When I run the SSIS package it gives this error: VS_NEEDNEWMETADATA

I am banging my head aganist the wall, there is no joy so far

Please help

Kind Regards
Swordfish8 (AT) hotmail (DOT) com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

EXEC Stored Procedure does not work in SSIS task

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

RE: EXEC Stored Procedure does not work in SSIS task - 08-13-2008 , 11:39 AM

RE: EXEC Stored Procedure does not work in SSIS task - 08-13-2008 , 11:39 AM

RE: EXEC Stored Procedure does not work in SSIS task - 08-13-2008 , 11:39 AM

RE: EXEC Stored Procedure does not work in SSIS task - 08-13-2008 , 11:39 AM

RE: EXEC Stored Procedure does not work in SSIS task - 08-13-2008 , 11:39 AM