none
SSIS ETL to refresh SSAS cube using file system?

    Вопрос

  • Hi, I have SSIS 2008 R2, SSAS (analysis service) 2008 R2 cube and SQL server 2008 R2. (and I'm newbie to SSIS!)

    Analysis service Cube get data from SQL sever tables and process the dimension and cube. To design this SSIS package, I drop 
      - Execute SQL task editor to run SQL store procedure.
      - Analysis Service processing task to refresh dimension and cube.

    To connect SQL sever database, I creted SQL server connection with "Use SQL Server Authentication" and  clicked "Save my password" but every time package run, package doesn't store password. Not sure how to save password? 

    Second query I have, I'd like to store dtsx and dtsConfig file on a folder, to use "File System" type in SQL Server Agent job on reggular schedule basis. How to create dtsConfig file with password in it and how to use this in File System for SSAS cube refresh? 

    Any idea? Thanks.SSIS - SQL & SSAS connection

    11 марта 2012 г. 11:20

Ответы

  • Hi KM,

    In general the both connections should be set to Windows authentication because of security reason. Before setting up the job, creating a proxy credential with a user having permission to executing the stored proceduce on SQL Server and processing dimension and cube on Analysis Services server. Then, the credential will be selected for the "Run as" option to run the package step in the job. Of course, you also create the dtsconfig file to store the user/password for executing the sp. For processing cube, you only can use windows authentication for Analysis Services only support this kind of authentication.

    See http://msdn.microsoft.com/en-us/library/ms190703.aspx for more details about creating a Credential.

    See http://support.microsoft.com/kb/912911 for some information about how to run a SQL Server Integration Services package as a SQL Server Agent job step.

    Hope this helpfully,

    Regards,
    Jerry

    • Помечено в качестве ответа Jerry NeeModerator 22 марта 2012 г. 10:43
    16 марта 2012 г. 7:38
  • Hi, my SQL Server is on "DB_SQLSERVER1" and SSAS sever is on "SSAS_SERVER1". I am going to drop my package on \\DB_SQLSERVER1\c$\SSISPackageFolder\ . As you can see SQL and SSAS are not on the same server. How to set authentication in SQL server connection and SSAS db connection, so SSIS package can run by SQL Server agent job?  Any idea how to set authentication and create dtsConfig file? Thanks.

    SQL connection: configure your connection manager like you normally would.
    SSAS connection: this must be Windows Authentication! So make sure the SQL Server Agent account has permissions to process the cube.

    Now right click on the control flow and select package configurations in the context menu. Enable them and add one.
    Select XML and choose all the properties you wish to configure.
    In your case:

    * connection string to SQL Server
    * username and password for this connection
    * connection string to SSAS

    The XML config file will be automatically generated. Open it with a text editor. In the node for password there will be a value of ******. Replace this with the actual password.


    MCTS, MCITP - Please mark posts as answered where appropriate.

    • Помечено в качестве ответа Jerry NeeModerator 22 марта 2012 г. 10:43
    16 марта 2012 г. 13:39

Все ответы

  • FYI - using SQL  Server Authentication and then saving your user name and password in the file is not secure.  These files are nothing more than XML, which can be opened and viewed by anyone with access to the file.

    That being said, check the protection level in the property window for the package and modify the setting as needed.  Making changes there, however, may create other issues, when attempting to run the package using different security credentials.

    11 марта 2012 г. 22:47
  • Hi,

    You need to set the package ProtectionLevel to DontSaveSensitive (recommended).  This wont save your password.

    If you want to use the SQL Server Authentication, you need to create a dtsconfig file (u can use XML type).

    You can follow the steps:

    http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/66500/ 

    You can add your password property of the Connection manager and then open the dtsconfig file, add the password in the respective XML Node.

    While running the package from File System, it will access the path of the dtsconfig file.

    Let me know if you need more information.

    Thanks,

    Swapnil

    Pls mark this as answered/helpful if this has helped. :)

    • Предложено в качестве ответа SwapnilKothari 12 марта 2012 г. 3:39
    • Отменено предложение в качестве ответа KM IT 12 марта 2012 г. 22:26
    12 марта 2012 г. 3:39
  • Hi, my SQL Server is on "DB_SQLSERVER1" and SSAS sever is on "SSAS_SERVER1". I am going to drop my package on \\DB_SQLSERVER1\c$\SSISPackageFolder\ . As you can see SQL and SSAS are not on the same server. How to set authentication in SQL server connection and SSAS db connection, so SSIS package can run by SQL Server agent job?  Any idea how to set authentication and create dtsConfig file? Thanks.
    • Изменено KM IT 12 марта 2012 г. 22:36
    12 марта 2012 г. 22:29
  • Hi KM,

    In general the both connections should be set to Windows authentication because of security reason. Before setting up the job, creating a proxy credential with a user having permission to executing the stored proceduce on SQL Server and processing dimension and cube on Analysis Services server. Then, the credential will be selected for the "Run as" option to run the package step in the job. Of course, you also create the dtsconfig file to store the user/password for executing the sp. For processing cube, you only can use windows authentication for Analysis Services only support this kind of authentication.

    See http://msdn.microsoft.com/en-us/library/ms190703.aspx for more details about creating a Credential.

    See http://support.microsoft.com/kb/912911 for some information about how to run a SQL Server Integration Services package as a SQL Server Agent job step.

    Hope this helpfully,

    Regards,
    Jerry

    • Помечено в качестве ответа Jerry NeeModerator 22 марта 2012 г. 10:43
    16 марта 2012 г. 7:38
  • Hi, my SQL Server is on "DB_SQLSERVER1" and SSAS sever is on "SSAS_SERVER1". I am going to drop my package on \\DB_SQLSERVER1\c$\SSISPackageFolder\ . As you can see SQL and SSAS are not on the same server. How to set authentication in SQL server connection and SSAS db connection, so SSIS package can run by SQL Server agent job?  Any idea how to set authentication and create dtsConfig file? Thanks.

    SQL connection: configure your connection manager like you normally would.
    SSAS connection: this must be Windows Authentication! So make sure the SQL Server Agent account has permissions to process the cube.

    Now right click on the control flow and select package configurations in the context menu. Enable them and add one.
    Select XML and choose all the properties you wish to configure.
    In your case:

    * connection string to SQL Server
    * username and password for this connection
    * connection string to SSAS

    The XML config file will be automatically generated. Open it with a text editor. In the node for password there will be a value of ******. Replace this with the actual password.


    MCTS, MCITP - Please mark posts as answered where appropriate.

    • Помечено в качестве ответа Jerry NeeModerator 22 марта 2012 г. 10:43
    16 марта 2012 г. 13:39