VisualStudio 2010 Database Project - BCP command in Post-deployment script

Posted on 2011-10-13
Last Modified: 2012-05-12

Question 1:
I have this flatfile from which I'm loading data into a table manually using BCP command from SQL Command prompt.
Now that we have migrated our database to Visual Studio 2010, I'm wondering how should we be specifying this command in the Post deployment script, so that the data gets loaded into the appropriate table after the database and tables are created.

Currently used bcp command is mentioned below:
bcp test.dbo.tabletest in tabletest_FlatFile.txt -f tabletest_FormatFile.xml -k -T -Ujohn -Sjohn\johninstance

Open in new window

Also I currently have bunch of insert statements in post deployment script to populating lookup data in couple of tables.

The code is as follows:
USE [test]

IF (EXISTS(SELECT * FROM [dbo].[tabletest]))
DELETE FROM [dbo].[tabletest]

INSERT tabletest(testcode, testdescription) VALUES ('xxx', NULL);
INSERT tabletest(testcode, testdescription) VALUES ('yyy', NULL);

Open in new window

Question 2:

I have included the .xml and .txt file included in the above question in "Scripts" folder under the db project (C:\DB_Proj\Scripts).

When I give the project to developers, they might choose to save the project file in a path different (D:\XYZ\).

When the application developer opens the delivered DB project in her Visual studio 2010 and says deploy, will the bcp command in Post deployment script identify the .txt and .xml file in right physical location (i.e., D:\XYZ\Scripts\ folder)?

If no, is there a way to give relative path in the post deployment script, such that the bcp works fine irrespective of where the application developer chooses to save the database project I deliver?


Question by:sath350163
    LVL 9

    Expert Comment

    by:Evan Cutler
    Try's C#...

    System.Diagnostics.Process proc = new System.Diagnostics.Process();      
    proc.StartInfo.FileName = "bcp";      
    proc.StartInfo.Arguments = @"select * from dbname"" queryout C:\filename.csv -S servername\instance -U sa -P password –N";      proc.Start();

    replace Arguements with your information...
    You can insert this as a function call in your application and run it with a click or something.

    Author Comment

    Does somebody have a solution for this?


    Accepted Solution

    Here is a solution that works.
    This must be included in the post deployment script of database project.
    So once the database and the tables get created, the post deployment script in the db project kicks off executing the below code and loads the data from the flat file into the appropriate table.

    use test
    sp_configure 'xp_cmdshell', 1;
    EXEC master..xp_cmdshell 'bcp test.dbo.table1 in "C:\projects\Data_FlatFile.txt" -f "C:\projects\Data_FormatFile.xml" -k -T -Uuser12 -Suser12\TESTDBINSTANCE'
    use test
    sp_configure 'xp_cmdshell', 0;

    Open in new window

    Though this solution works, I do not want to hard code the folder path in the bcp command.
    Since the flat file and the format file mentioned above are available in the same location as the post deployment script itself (i.e, file containing the above code), what should I do to specify the relative path for the flat file and the format file in the post deployment script (i.e, replace the above hard coded path with relative path).


    Author Closing Comment

    This solution worked for me

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now