SSIS Referencing Stored Proc Counter Question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SSIS Referencing Stored Proc Counter Question

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-10-31 : 13:21:53

OK, here goes.... Why can't I reference my counter variable which is set in a Stored Proc from SSIS? Usually, this is no problem.

My issue seems to be related to my SP. I am running a bunch of xp_cmdshell commands and also setting a counter variable which I want to reference in SSIS.

SSIS seems to be getting confused, and is unable to reference the counter variable.

Below is my SP code, any ideas?

declare @cnt int -- HERE IS THE COUNTER VARIABLE

if @AAA = 'TEST'
Begin
DECLARE @vName varchar(50), @mysql varchar(500), @FileName varchar(50),@tFileName varchar(50), @bcpCommand varchar(2000)

DECLARE @cName CURSOR
exec master..xp_cmdshell 'del E:\aaa.csv'

SET @FileName = REPLACE('E:\aaa.csv','/','-')
SET @tFileName = REPLACE('E:\temp.csv','/','-')

SET @cName = CURSOR FOR
select distinct Company from MYDB..MYDATA
where XXX = 'TEST'

OPEN @cName
FETCH NEXT
FROM @cName INTO @vName

WHILE @@FETCH_STATUS = 0
BEGIN

set @cnt = @cnt + 1 -- ADD 1 TO THE COUNTER EACH TIME THROUGH THE TRIGGER. YES, I HAVE TO USE THIS TRIGGER.

SET @mysql = '"select distinct ''LEVEL0'', Company from MYDB..MYDATA where XXX = ''TEST'' and Company=''' + rtrim(@vName) +'''" '

SET @bcpCommand = 'bcp ' + @mysql +' queryout "'
SET @bcpCommand = @bcpCommand + @tFileName + '" -T -t, -c'
EXEC master..xp_cmdshell @bcpCommand
exec master..xp_cmdshell 'type E:\temp.csv >> E:\aaa.csv'
exec master..xp_cmdshell 'del E:\temp.csv'

FETCH NEXT
FROM @cName INTO @vName

END

CLOSE @cName
DEALLOCATE @cName

END

select @cnt as myCount -- REFERENCE THIS COUNTER IN SSIS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 13:29:32

did you try setting resultset to single row and mapping this mycount to variable created inside ssis and then seeing if its getting value correctly?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 13:30:57

also why are you looping and creating bcp commands like this inside ssis? if you're using ssis why not use data flow tasks to do the data transfers to file? if you're persisting with bcp statement why use ssis then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-10-31 : 13:38:09

Hi Visa, I was asked to include a call to this SP from within of of my SSIS packages. Was hoping to just use it as is.

I currently have a SQL Task set to return Single Row. "myCount" is mapped to an SSIS variable (int data type).

I keep getting "Unable to find column myCount in the result set.".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 13:49:36

quote:
Originally posted by qman

Hi Visa, I was asked to include a call to this SP from within of of my SSIS packages. Was hoping to just use it as is.

I currently have a SQL Task set to return Single Row. "myCount" is mapped to an SSIS variable (int data type).

I keep getting "Unable to find column myCount in the result set.".

then why include all code again here

why not wrap it as an SP and call it as

EXEC SPname params...
from SSIS sql task

I still feel you dont need cursor or looping logic

looks like just a set of straight forward data flow tasks is what you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-10-31 : 14:04:22

Sorry, my past post might not have been that clear.

I am currently calling the SP via a SQL task.
I am trying to reference the counter set in the SP, and assign it to an SSIS variable via the the result set.

I can't seem to get SSIS to recognize the variable "myCount". The SQL task is set to Single Row Result Set.

I get: Unable to find column myCount in the result set.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-10-31 : 14:06:32

It works when I comment out all the command line code, leaving just the counter.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources