|
|
|
date: Mon, 11 Aug 2008 21:07:01 -0700,
group: microsoft.public.sqlserver.dts
back
RE: EXEC Stored Procedure does not work in SSIS task
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@aut.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@hotmail.com
> >
> >
> >
date: Tue, 12 Aug 2008 13:49:01 -0700
author: praveen
RE: EXEC Stored Procedure does not work in SSIS task
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:
> 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@aut.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@hotmail.com
> > >
> > >
> > >
date: Wed, 13 Aug 2008 09:39:02 -0700
author: Todd C
|
|
