CODEWITHSUNDEEP
sqlsql-serverssisetl
Nuno Silva
Nuno Silva

Reputation: 83

SSIS - OLEDB Source - how to use sql query with variables?

I have this query that I want to export its contents to a table:

DECLARE @bdname VARCHAR(50)
SET @bdname = ?

SELECT 
    @bdname, 
    CONVERT(CHAR(30), dp2.name), 
    CONVERT(CHAR(20), dp.name) 
FROM  
    sys.database_principals AS dp 
INNER JOIN
    sys.database_role_members AS drm ON dp.principal_id = drm.role_principal_id 
INNER JOIN
    sys.database_principals AS dp2 ON drm.member_principal_id = dp2.principal_id
WHERE  
    (dp2.principal_id > 4) AND (dp2.type <> 'R')

If I put this as part of a Execute SQL Task, it goes ok, but I can't (don't know how) then map the results into something that I can export to a SQL table.

Can anyone guide me on how can I put the results of this query into a table? It's because if I use dataflow and ole db source, it does not accept this type of query.

Any help would be much appreciated.

Thanks

Upvotes: 2

Views: 4646

Answers (1)

Hadi
Hadi

Reputation: 37358

Add a dataflow task, inside it add an Oledb source and an Oledb destination.

Add a variable of type string , set Evaluate as expression to true and assign the following expression:

"SELECT '" + @[User::bdname] + "' , convert(char(30),dp2.name), convert(char(20),dp.name) 
FROM  sys.database_principals AS dp INNER JOIN
sys.database_role_members AS drm ON dp.principal_id = drm.role_principal_id 
INNER JoIN
sys.database_principals AS dp2 ON  drm.member_principal_id = dp2.principal_id
WHERE  (dp2.principal_id > 4) AND (dp2.type <> 'R')"

(Assuming bdname value is stored in user::bdname variable)

In the Oledb source set the source type to Sql command from variable and choose the created variable

Map the oledb source to the oledb destination

UPDATE 1

After reading your comment "...This foreach loop will query the sys.databases for all the user databases, and it is supposed to iterate that variable, to get the permissions".

To iterate over databases you have to add an Execute SQL Task with an SQL Command

SELECT name from sys.databases

and set the Result Set to Full Result Set , and assign the Result Set to a variable of type Object

Link this Execute SQL Task to the ForEach Loop Container

In the foreach Loop Container change the enumerator type to Ado enumerator and choose the object variable as source

enter image description hereenter image description here

Also don't forget to set the Dataflow task Delay Validation property to True

UPDATE 2 (workaround)

Try joining your query with sys.databases instead of using Foreach loop container (i don't know if it achieve what you want to do)

SELECT db.name, convert(char(30),dp2.name), convert(char(20),dp.name) 
FROM  sys.database_principals AS dp INNER JOIN
sys.database_role_members AS drm ON dp.principal_id = drm.role_principal_id 
INNER JoIN
sys.database_principals AS dp2 ON drm.member_principal_id = dp2.principal_id
INNER JOIN sys.databases as db ON db.owner_sid = dp2.sid
WHERE  (dp2.principal_id > 4) AND (dp2.type <> 'R')

Upvotes: 3

Related Questions