In the other post I showed one way to insert data into SQL Server CE; here I’m going to extract data from it using only the SSIS delivered tasks. Why you ask? If you’ve consulted as I have for several years, you know that in some shops, notably BI shops, that the true hardcore BI folks do not do custom .Net coding – they use the delivered functionality only because they’re BI types and not app/devs or coders. I’ve mentioned before…they oftentimes prescribe to the “hands off the keyboard” mentality associated with data warehousing, and drag and drop while developing as a general rule. They also may or may not have resident .Net skills within the BI shop, and if they did, not everyone there might be equipped to code or change .Net code inside of SSIS. Should the vb.net or c# resource leave the team…you get the picture. Sending data to and from SQL Server CE via .Net is all over Google – here I’m trying a different route while avoiding the script task and script component.
First step to extract, wire-up to the Northwind.sdf file. It’s in the samples folder under SQL Server x86. I’ll fetch a row or two from the Employees table:
Image 1 – Employees table in Northwind
Create a connection manager in SSIS
Image 1.5 – Create the connection manager for SQL Server CE
Set the properties like I have here
Image 2 – Exec SQL Task source - SQLMOBILE type waiting there for you.
Configure the rest of the properties:
Image 3 – Exec SQL Task source properties
Write a TSQL statement as shown
Image 4 – Exec SQL Task TSQL statement
In the result set, create a variable called rsEmployees. It will be one of those Object types to hold a recordset.
Image 5 – Configure result set in Exec SQL Task
While your at it, configure all of the variables that we’ll need. Note that I’m fetching only three attributes from the table for this test.
Image 6 – Configure variables in SSIS
Drop a ForEeachLoop to SSIS. Crack it open and add the rsEmployees variable that you already created as the source variable. Configure as shown.
Image 7 – ForEach Loop Editor
Configure variable mappings – these will map data from the rsEmployees variable to individual variables that will feed our INSERT statement into our destination.
Image 8 – For Each mappings
Drop another Exec SQL Task to SSIS, and drag in inside of the ForEach container. Configure parameters for it as shown.
Image 9 – Mappings for the destination
Configure the Exec SQL Task as I have here, and write an INSERT statement as shown. The ? placeholders will facilitate mapping the variables to our INSERT.
Image 10 – Configure the Exec SQL Task and write INSERT statement
Run the package – I get data:
Image 11 – Select from destination
There’s a bit of creativity needed to move data back and forth from SQL Server CE. I have not done any performance testing or anything of that nature, but maybe I’ll do some next time. I’d be interested to see what SQL Server CE can handle, transaction-wise.
Thanks,
Lee
-----------------------------
4fbf5298-134c-4bf5-92ca-97a3467dc140|0|.0