locked
pass database details as parameter in ssis RRS feed

  • Question

  • Hi,

    I am using sql server 2008 and I have created an ssis package to transfer data from flat file to database. It is working fine. I used the below command to execute it

    DTEXEC.EXE /F "C:\BulkInsert\BulkInsertTask.dtsx"

    I also managed to pass the filepath of the text file dynamically like

    DTEXEC.EXE /F "C:\BulkInsert\BulkInsertTask.dtsx" /set \package.variables[filepath].Value;"C:\test.txt"

    Now I want to pass the server name, Database name, userid and password as a parameter to the package. I think we need to create variables for this and then map it using expressions (using connection manager). But can anybody give me the exact syntax for this?

    Thanks,
    Rakesh.

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Wednesday, February 6, 2013 10:15 AM

Answers

  • It would look something like this





    • Proposed as answer by Russ Loski Wednesday, February 6, 2013 2:22 PM
    • Edited by Satheesh Variath Wednesday, February 6, 2013 2:36 PM
    • Unproposed as answer by Iam_Rakesh Wednesday, February 6, 2013 2:40 PM
    • Marked as answer by Iam_Rakesh Thursday, February 7, 2013 4:35 AM
    Wednesday, February 6, 2013 12:58 PM
  • Create a new connection manager pointing to the server and the database.

    Then click ok.  Select the connection manager you just created and hit F4.  Look for the properties window (the same properties window you would have created expressions for).  Near the top is a connection string property.  View that.


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Iam_Rakesh Thursday, February 7, 2013 4:35 AM
    Wednesday, February 6, 2013 3:09 PM

All replies

  • Wednesday, February 6, 2013 11:13 AM
  • A couple links:  http://www.simple-talk.com/sql/ssis/working-with-property-expressions-in-sql-server-integration-services/, http://consultingblogs.emc.com/jamiethomson/archive/2006/03/11/SSIS-Nugget_3A00_-Setting-expressions.aspx


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, February 6, 2013 11:14 AM
  • ok, I understand that the connection string to be used is of below format,

    "Data Source=abc;Initial Catalog=FinancialProcessing;Integrated Security=True;"

    But my db details are as follows,

    servername = abc

    dbname = FinancialProcessing

    username = xyz

    password = 123456

    In this case how should I configure my connection string???


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Wednesday, February 6, 2013 12:32 PM
  • You can have the connection string expression as
    Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;
    and the values can come from varriables

    Satheesh

    Wednesday, February 6, 2013 12:45 PM
  • Data Source=abc;Initial Catalog=FinancialProcessing;User ID=xyz;Password=123456;Provider=SQLNCLI11.1

    Note that the provider is one that I got for a connection from SSIS 2012 to SQL 2012.  You may need a different provider.


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, February 6, 2013 12:46 PM
  • It would look something like this





    • Proposed as answer by Russ Loski Wednesday, February 6, 2013 2:22 PM
    • Edited by Satheesh Variath Wednesday, February 6, 2013 2:36 PM
    • Unproposed as answer by Iam_Rakesh Wednesday, February 6, 2013 2:40 PM
    • Marked as answer by Iam_Rakesh Thursday, February 7, 2013 4:35 AM
    Wednesday, February 6, 2013 12:58 PM
  • Below is how I had mapped the variables

    InitialCatalog - [User::database]
    ServerName - [User::servername]
    UserName - [User::username]

    For password there is no mapping option in the expressions, so I just did the below mapping

    user - [User::password]

    and then I tried

    DTEXEC.EXE /F "C:\BulkInsert\BulkInsertTask.dtsx" /set \package.variables[filepath].Value;"C:\test.txt" /set \package.variables[servername].Value;"abc" /set \package.variables[database].Value;"FinancialProcessing"  /set \package.variables[username].Value;"xyz" /set \package.variables[password].Value;"xyz"

    I got the below error


    Error: 2013-02-06 19:41:09.46
       Code: 0xC020801C
       Source: Data Flow Task OLE DB Destination [9]
       Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAG
    ER.  The AcquireConnection method call to the connection manager "123456"
     failed with error code 0xC0209302.  There may be error messages posted before t
    his with more information on why the AcquireConnection method call failed.
    End Error
    Error: 2013-02-06 19:41:09.48
       Code: 0xC0047017
       Source: Data Flow Task SSIS.Pipeline
       Description: component "OLE DB Destination" (9) failed validation and returne
    d error code 0xC020801C.
    End Error
    Progress: 2013-02-06 19:41:09.48
       Source: Data Flow Task
       Validating: 100% complete
    End Progress
    Error: 2013-02-06 19:41:09.48
       Code: 0xC004700C
       Source: Data Flow Task SSIS.Pipeline
       Description: One or more component failed validation.

    I THINK ISSUE WITH THE PASSWORD. PLEASE HELP GUYS...


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Wednesday, February 6, 2013 2:18 PM
  • I think that this is why I don't map the parts of the connection string (ie. InitialCatalog, ServerName).

    Have you tried Satheesh's method?


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, February 6, 2013 2:28 PM

  • >>For password there is no mapping option in the expressions, so I just did the below mapping

    Did you add the option "connectionstring" in the expression window? 


    Satheesh

    Wednesday, February 6, 2013 2:30 PM
  • Hi,

    Sathesh, yes I have set the connection string as,

    "Data Source=abc;Initial Catalog= FinancialProcessing; User ID=xyz; Password=123456; Provider=SQLNCLI11.1;"

    But when I run the package I am getting the error,

    TITLE: Package Validation Error
    ------------------------------

    Package Validation Error

    ------------------------------
    ADDITIONAL INFORMATION:

    Error at Data Flow Task [OLE DB Destination [9]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "123456" failed with error code 0xC0209302.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    Error at Data Flow Task [SSIS.Pipeline]: component "OLE DB Destination" (9) failed validation and returned error code 0xC020801C.

    Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

    Error at Data Flow Task: There were errors during task validation.

    Error at txt_to_table [Connection manager "123456"]: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR.  The requested OLE DB provider  is not registered. Error code: 0x00000000.
    An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".

     (Microsoft.DataTransformationServices.VsIntegration)

    Thanks,

    Rakesh.


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.


    • Edited by Iam_Rakesh Wednesday, February 6, 2013 2:43 PM
    Wednesday, February 6, 2013 2:39 PM
  • Next, check the Provider.  The provider SQLNCLI11.1 is what I get with SSIS 2012 connecting to SQL 2012.  However, if you are using SSIS 2008 you will need another provider.  Create a connection to your database by hand.  Then look at the connection string to see what Provider is used there.

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, February 6, 2013 2:58 PM
  • Russ, I am using sql server 2008 r2 and I am able to connect to my database by hand. How and where do I check the provider in my connection string ??

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Wednesday, February 6, 2013 3:02 PM
  • Create a new connection manager pointing to the server and the database.

    Then click ok.  Select the connection manager you just created and hit F4.  Look for the properties window (the same properties window you would have created expressions for).  Near the top is a connection string property.  View that.


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Iam_Rakesh Thursday, February 7, 2013 4:35 AM
    Wednesday, February 6, 2013 3:09 PM
  • Guys,

    Thanks for your response. I have one question though. As per this thread, I have implemented the below connection string,

    "Data Source="+@[User::servername]+";Initial Catalog= "+@[User::database]+"; User ID="+@[User::username]+"; Password="+@[User::password]+"; Provider=SQLNCLI10.1;"

    In which,

    InitialCatalog - [User::database]
    ServerName - [User::servername]
    UserName - [User::username]

    user - [User::password]

    This is working fine. But I think this is not the right way to map the password, as it might be visible inside the package. Any ideas guys?



    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Thursday, February 7, 2013 9:02 AM
  • You could store this password in a database and then retrieve it when you excute the package. You can then restrict the read access to the database/table so it's only the executing user (or who ever you will allow to see it) that can read from the table.

    You can find some tips on how to do it here -

    http://msdn.microsoft.com/en-us/library/ms141682(v=sql.105).aspx

    http://msdn.microsoft.com/en-us/library/ms141132(v=sql.105).aspx

     

    Steen Schlüter Persson (DK)

    Monday, February 11, 2013 2:30 PM