[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 751
  • Last Modified:

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

Hello,

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]
GO

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

BEGIN TRANSACTION;
GO
INSERT tabletest(testcode, testdescription) VALUES ('xxx', NULL);
INSERT tabletest(testcode, testdescription) VALUES ('yyy', NULL);
GO
COMMIT TRANSACTION;
GO

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?

Thanks!

 
0
sath350163
Asked:
sath350163
  • 3
1 Solution
 
Evan CutlerCommented:
Try this...it'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.
0
 
sath350163Author Commented:
Does somebody have a solution for this?

Thanks!
0
 
sath350163Author Commented:
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
go
sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
go
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'
GO
use test
go
sp_configure 'xp_cmdshell', 0;
GO
RECONFIGURE;
GO

Open in new window


Question:
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).


Thanks!
0
 
sath350163Author Commented:
This solution worked for me
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now