can we declare variables in Dataflow ?

Question

can we declare variables in Dataflow ?

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 1

Hi Sam!

I have a quick question to this topic...If i need to loop the query on different servers, is it possible? as we are keeping the retain same connection to true and the connection is not changed in run time if we need to loop other servers also , So is there any way to resolve the issue to loop to all servers list? for this topic solution . thanks in advance .

sam 55243 SSCrazy Points: 2697 More actions · Answer 2

Hi Steve,

No i don't think tht option is feasible because the connection string for a OLEDB source is embedde within the component and u can't set it throught an expresion

Sam

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 3

Hi !

I run the below query with SSIS on sybase DBMS but i was getting the error message stating that "No disconnected record set is available for the specified SQL statement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly." and this query works fine in Sybase .So using the cursors will not work in SSIS .If so can some one guide me to make sure this query works so that i can loop with all the Sybase servers using the SSIS .Thanks in advance 🙂

Query :-

/* CREATE #AUDIT_PRIV_IDS TABLE */

use master

go

SET NOCOUNT ON

DECLARE @Temphost varchar (15)

DECLARE @Host varchar (15)

DECLARE @Instance varchar(32)

select @Temphost = address_info from master..syslisteners

select @Host = str_replace(@Temphost, '.' , ' ')

select @Instance =srvname from master..sysservers where srvid = 0

CREATE TABLE #AUDIT_PRIV_IDS

(

Host varchar(16)

,Instance varchar(32)

,DBName varchar(32)

,DBType varchar(8)

,LoginId varchar(20) NULL

,UserId varchar(20)

,Privilege varchar(20)

)

/* SELECT MEMBERS OF SERVER FIXED ROLES */

INSERT INTO #AUDIT_PRIV_IDS

SELECT @Host AS [Host]

,@Instance AS [Instance]

,'master' AS [DBName]

,'Sybase' AS [DBType]

,suser_name(suid) AS [LoginId]

,suser_name(suid) AS [UserId]

,role_name(srid) AS [Privilege]

FROM master..sysloginroles

go

/* SELECT MEMBERS OF EACH DATABASE ROLES */

declare get_db_names cursor for

select name from master..sysdatabases where name != 'master'

go

DECLARE @dbname varchar(32)

DECLARE @sqlstring varchar(512)

OPEN get_db_names

FETCH get_db_names into @dbname

while (@@sqlstatus=0)

begin

SET NOCOUNT ON

SELECT @sqlstring = 'INSERT INTO #AUDIT_PRIV_IDS

SELECT @Host AS Host,

@Instance AS Instance,

@dbname AS DBName,

@DBType AS DBType,

m.name AS LoginId,

u.name AS UserId,

g.name AS Privilege

FROM ' + @dbname + '..sysusers u, ' + @dbname + '..sysusers g, master..syslogins m

where u.suid *= m.suid

and u.gid *= g.uid

and ((u.uid < @@mingroupid and u.uid != 0)

or (u.uid > @@maxgroupid))'

DECLARE @Temphost varchar (15)

DECLARE @Host varchar (15)

DECLARE @Instance varchar(32)

DECLARE @DBType varchar(8)

select @Temphost = address_info from master..syslisteners

select @Host = str_replace(@Temphost, '.' , ' ')

select @Instance =srvname from master..sysservers where srvid = 0

set @DBType = 'Sybase'

execute (@sqlstring)

FETCH get_db_names into @dbname

end

CLOSE get_db_names

DEALLOCATE CURSOR get_db_names

go

select * from #AUDIT_PRIV_IDS

go

drop table #AUDIT_PRIV_IDS

go

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Run that query from a query window. I think you'll find it returns more than one result set, and that's why your task is failing.

John

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 5

when i run the query i get only 1 result set .and i am still not able to figure out where i am going wrong .so we can use cursors right ?

Thanks in advance

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

OK, have you done a search on the error message you get? Here's one link that may help, but you'll find loads more.

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/5e045aaf-2e1a-425b-8e73-b623843cd0ad/

John

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 7

In this link he has changed the result set to 0 but in my case i am bringing the data and assigning to some variable and i was using the OLEDB destination .I was able to split the query to 2 parts and defined the retain same connection to true and it works but when i need to loop to several servers the retain same connection will make to connection to be same for all the servers i loop

Chad Downey SSC Enthusiast Points: 126 More actions · Answer 8

Chad E. Downey, CDMP - Certified Data Management Professional
Consultant - SQL Server, SSIS, SSRS, SSAS