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

Question

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

Hadi · Accepted Answer · 2017-05-14 06:55:42Z

3

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

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')

edited May 14, 2017 at 6:55

answered May 13, 2017 at 22:25

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Nuno Silva Over a year ago

It doesn't accept that I use the variables in the begining... Already did that :/

Nuno Silva Over a year ago

@Filburt, how do I do that? I followed a tutorial, actually, to try using from variable, but it didn't work. Maybe I did it wrong. Thanks!

Nuno Silva Over a year ago

Trying to implement it. I'm gonna have to convert that var to user:bdname, but when I try to do it, it says it is not supported. Maybe because it is an object, because that var contains a list of databases

Hadi Over a year ago

Didn't understand that, you was using declare @bdname varchar(50) how it could be of type object?

Nuno Silva Over a year ago

Ok, Hadi, I've managed to use the solution you provided, but not fully. still don't know exactly am I going to assign the multiple values of the databse names to that variable, because this piece of code is part of a Foreach Loop container. 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.

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related