locked
SSIS package - dynamic query, how to set database name dynamic ? RRS feed

  • Question

  • Hi below is my query - need to set up dynamic values for the below in SSIS package:

    for SIT it should be SIT, UAT it should be UAT and PROD should be PROD. How can Iachieve this in SSIS package ?

    select t2.USER_,T5.NetWorkAlias, T5.Name,T5.Company,t5.ENABLE,T1.AOTNAME, t1.NAME RoleName,
             T4.CREATEDBY, T4.CREATEDDateTime, T6.SUPERVISOR, T3.DATAAREA RoleLegalEntity 
                  from  AX_UAT_model.dbo.SECURITYROLE T1
           join AX_UAT.dbo.SECURITYUSERROLE T2
                  on T1.recid = T2.SECURITYROLE
           left outer join AX_UAT.dbo.SECURITYUSERROLECONDITION T3
                  on t3.SECURITYUSERROLE = t2.RECID
           join SYSUSERINFO T4
                  on T2.USER_ = T4.Id
           join UserInfo T5
                  on T5.Id = T4.Id
           left outer join BSRSUPERVISOR T6
                  on T6.USERINFO = T5.Id
    order by t2.user_
    


    MBH

    Friday, February 12, 2016 4:38 PM

Answers

  • Build a dynamic query in a user variable with Expressions by passing a package variable value which can have either SIT, UAT, or PROD. Then within the OLE DB source choose 'SQL command from variable' and choose the desired variable.
    Friday, February 12, 2016 6:36 PM
  • Hi,

    Here are some steps:

    1. Create some variables:

    a. vDatabaseName, type "object"

    b. vSQL, type "String"

    2. Using Execute SQL Task to get the database names in a object variable, use this SQL:

    SELECT
         TableName = 'AX_SIT'
    Union
    SELECT
         TableName = 'AX_UAT'
    Union
    SELECT
         TableName = 'AX_PROD'

    Make sure to set the following in the SQL task editor

    --"Result set to "Full result set" 

    --In Result Set tab: Variable Name: vDatabaseName

    2. Get a Foreach loop and set it up like below:

    --Collection tab:

    Enumerator: Foreach ADO Enumerator

    Enumerator configuration: drop down list and choose your Object variable-- vDatabaseName

    3. For the value of vSQL variable click the elipses button under the Expression and input this sql:

    "select t2.USER_,T5.NetWorkAlias, T5.Name,T5.Company,t5.ENABLE,T1.AOTNAME, t1.NAME RoleName,
             T4.CREATEDBY, T4.CREATEDDateTime, T6.SUPERVISOR, T3.DATAAREA RoleLegalEntity
                  from " +vDatabaseName+ ".dbo.SECURITYROLE T1
           join" +vDatabaseName+ ".dbo.SECURITYUSERROLE T2
                  on T1.recid = T2.SECURITYROLE
           left outer join" +vDatabaseName+".dbo.SECURITYUSERROLECONDITION T3
                  on t3.SECURITYUSERROLE = t2.RECID
           join SYSUSERINFO T4
                  on T2.USER_ = T4.Id
           join UserInfo T5
                  on T5.Id = T4.Id
           left outer join BSRSUPERVISOR T6
                  on T6.USERINFO = T5.Id
    order by t2.user_"

    3. Get a Data flow task, inside it get a source component, in the OLEDB source editor Data access mode select "SQL Command from variable" . In the variable name select vSQL

    4. Get a destination and map your columns.

    Warning: the variable names would be different, e.g [USER::vSQL].

    Thanks,


    Please mark as Answer if this answers your question ...Thanks, Sary Awwad

    Friday, February 12, 2016 7:53 PM

All replies

  • Hi jaguarjags,

    you need to use a package variable as a source and assign it the dynamic SQL via an expression


    Arthur

    MyBlog


    Twitter

    Friday, February 12, 2016 4:41 PM
  • but under OLE DB Source, under SQL Command how can I set this up dynamically ?

    the query should take AX_SIT_model should be changed dynamically in the query to AX_UAT_model


    MBH

    Friday, February 12, 2016 4:58 PM
  • The server name should be parametreized via the connection manage, see

    http://blogs.msdn.com/b/meer_alam/archive/2014/08/11/ssis-data-source-connection-information-parameterization-with-environment-variable.aspx


    Arthur

    MyBlog


    Twitter

    Friday, February 12, 2016 6:02 PM
  • Build a dynamic query in a user variable with Expressions by passing a package variable value which can have either SIT, UAT, or PROD. Then within the OLE DB source choose 'SQL command from variable' and choose the desired variable.
    Friday, February 12, 2016 6:36 PM
  • Hi,

    Here are some steps:

    1. Create some variables:

    a. vDatabaseName, type "object"

    b. vSQL, type "String"

    2. Using Execute SQL Task to get the database names in a object variable, use this SQL:

    SELECT
         TableName = 'AX_SIT'
    Union
    SELECT
         TableName = 'AX_UAT'
    Union
    SELECT
         TableName = 'AX_PROD'

    Make sure to set the following in the SQL task editor

    --"Result set to "Full result set" 

    --In Result Set tab: Variable Name: vDatabaseName

    2. Get a Foreach loop and set it up like below:

    --Collection tab:

    Enumerator: Foreach ADO Enumerator

    Enumerator configuration: drop down list and choose your Object variable-- vDatabaseName

    3. For the value of vSQL variable click the elipses button under the Expression and input this sql:

    "select t2.USER_,T5.NetWorkAlias, T5.Name,T5.Company,t5.ENABLE,T1.AOTNAME, t1.NAME RoleName,
             T4.CREATEDBY, T4.CREATEDDateTime, T6.SUPERVISOR, T3.DATAAREA RoleLegalEntity
                  from " +vDatabaseName+ ".dbo.SECURITYROLE T1
           join" +vDatabaseName+ ".dbo.SECURITYUSERROLE T2
                  on T1.recid = T2.SECURITYROLE
           left outer join" +vDatabaseName+".dbo.SECURITYUSERROLECONDITION T3
                  on t3.SECURITYUSERROLE = t2.RECID
           join SYSUSERINFO T4
                  on T2.USER_ = T4.Id
           join UserInfo T5
                  on T5.Id = T4.Id
           left outer join BSRSUPERVISOR T6
                  on T6.USERINFO = T5.Id
    order by t2.user_"

    3. Get a Data flow task, inside it get a source component, in the OLEDB source editor Data access mode select "SQL Command from variable" . In the variable name select vSQL

    4. Get a destination and map your columns.

    Warning: the variable names would be different, e.g [USER::vSQL].

    Thanks,


    Please mark as Answer if this answers your question ...Thanks, Sary Awwad

    Friday, February 12, 2016 7:53 PM