Post reply

Can we provide username and password (to access the destination location) while exporting data to a flat file from SQL Server table

  • August 15, 2008 at 9:09 am

    #191429

    Hi all,

    Want help from you all.

    My requirements:

    1. Need to write data from a SQL 2005 database table to a flat file from a stored procedure (this stored procedure can call a SSIS package).

    Constraints

    1. Flat file is in a shared location.

    2. Flat file is not mapped to a network drive.

    3. I am logged in to the SQL Server 2005 with SQL Server Authentication.

    Problems

    1. Unable to provide access privileges to SQL Server to write to the shared folder (a predefined service ID has access), as Flat File destination doesn't provide an option.

    Please do suggest some way on how to write to the shared folder.

    Thanks,

    Sameer

  • August 15, 2008 at 9:47 am

    #858835

    There is a way you can do it with net use.

    Using cmd (or xp_cmdshell) you would run net use \\myuncsharename /password (or somethign along those lines :w00t:)

    I will look for the syntax now and post if I find it in the next 5 minutes. If not, try look on google while I wrestle the cap off my nice cold beer 😎

    It can be done with net use...

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • August 15, 2008 at 9:50 am

    #858838

    How lucky can you be...

    net use \\MyServerName\SomeShare /u:DomainName\UserName Password

    Then, anything run in the same context _should_ be able to connect.

    When you done, it's a good idea to run net use \\MyServerName\SomeShare /d

    Now, where did I put my bottle opener... Ah! There it is.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • August 15, 2008 at 9:53 am

    #858840

    Thanks Crispin,

    But................... i forgot............ there is a 4th constraint

    4. No cmd command to be used

    🙁

  • August 15, 2008 at 9:56 am

    #858844

    Use a proxy account in SQL?

    Create proxy account, use it to run job / package whatever.

    see BOL for proxy accounts.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • August 15, 2008 at 5:32 pm

    #859044

    Thanks Crispin....

    That works... You saved me a lot of time

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply