| |
|
Books Written by Brian Nantz |
|
|
|
|
|
| |
|
Converting from MDF (SQL Server 2005 data file) to SDF (SQL Server Compact data file) |
|
|
Location: Blogs Right To Left |
|
| Posted by: host |
7/21/2007 |
It is confusing enough that SQL Server Compact has gone through a few name changes! That definitely detracts from its Google juice. I just wanted a quick and dirty way to take an existing single table database and convert it to an SDF for testing on a mobile device.
I expected that this would be supported under the Import / Export wizard of SQL Server 2005, but it is not (http://ssis.wik.is/SSIS_Components). If you have the SQL Server Compact tools installed, you can access sdf from SQL Server Management Studio just like another connection:
You can also issue Queries against a Mobile Database:
Although you can have both connections open in Management Studio you can not copy data between then because SQL Compact does not support select into. It is interesting that you can not browse the table data:
Of course you can issue TSQL select commands against it. Another way to connect to the sdf Compact database is by using the Visual Studio Server Explorer:
As you can see this way does allow browsing of data.
After much Googling, I found that I had a couple of options. First I could buy one of a couple of products that did this. I am cheap. I would code it before I would buy it! Then I ran across this forum post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112111&SiteID=1). So the second choice was to write a simple SSIS package. I can do that! Or so I thought. First I created my own Integration Services Package Project (http://msdn2.microsoft.com/en-us/library/ms170057.aspx) using SQL Server Business Intelligence Development Studio. I found an interesting article on how to do this (http://msdn2.microsoft.com/en-us/library/bb332055.aspx). But I could not find those dialogs! This is because I naturally assumed that I should create the Data Sources first, which turned out to be not the simplest approach:
I then found the dataflow page. If you right click at the bottom pane you can add new connections. I added an ADO.Net connection with and configured the correct sql select string.
Then I chose new connections and finally found the dialog from the article!
I chose the SQLMOBILE option and browsed to the file. From the toolbox I chose a datareader source which is self-explanatory. Then I chose a SQL Mobile destination! Then I had to associate the appropriate connections with the destination and source. Then final step is to remove the id column from the mappings so that Mobile can created is own autogen’d ids.
I ran it in the debugger and it worked! Not very hard if you know where to look!
|
|
| Permalink |
Trackback |
|
|
|
|
You must be logged in and have permission to create or edit a blog.
|
|
|