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
Someone here at work is going to use SQL Server Compact Edition (SQL CE) to do some data storage, so I wanted to check it out as well since I haven’t used that version before. Google shows some folks having problems connecting to it and pushing/fetching data via SSIS, some links below for reference. Since Vb.Net and c# connectivity are well documented, here’s how to via SSIS using delivered tasks with no custom code.
Drag a Data Flow Task to the Control Flow tab, and then go to the Data Flow Tab. Notice that there is a SQL Server Compact Destination data flow destination, but nothing for SQL Server CE.
Image 1 - Data Flow Tasks
You can also look at the Solution Explorer, Data Sources (right-click), and see that in the .Net providers there are (in my case) three references to a SQL Server Compact Provider. I couldn’t use this method to get a connection manager to work, maybe you can.
Image 2 - New Data Source from Data Sources in Solution Explorer
Where I was able to connect was simply by right-clicking at the bottom in BIDS and creating a “New Connection”, scroll, and choose SQLMOBILE. This I think is where everyone doesn’t check, either that or they forget that SQL Mobile and SQL Server CE are pretty much the same thing I believe, although I could be wrong.
Image 3 - Create a new Connection Manager
Image 4 - Scroll to SQLMOBILE
Go to the .sdf file and add the password.
Image 5 - Connect to .sdf file
From here, it’s like any other connection. I’ve connected and sent data to SQL Server CE using this method.
Image 6 - Package running
Clicking the connection manager, note that you have to create the table in SQL Server CE first, before moving data; you can’t as far as I can tell, create the table on the fly as you can in other SSIS tasks.
Image 7 - Advanced Editor for SQL Server Compact Destination
Next time I”ll show you how to fetch data from SQL Server CE; it’s a bit different process than this one, as you’ll notice that there’s no Data Flow Source task that accompanies the SQL Server Compact Destination task.
Thanks,
Lee
------------------------------
Difficulties connecting that I found…
http://stackoverflow.com/questions/1308943/ssis-2008-how-to-read-from-sql-server-compact-edition-file
http://stackoverflow.com/questions/2181496/can-sql-server-compact-be-used-as-both-a-source-and-destination-in-ssis
http://stackoverflow.com/questions/2517035/connect-to-sql-server-compact-edition-in-ssis
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-dts/12542/SSIS-and-SQL-CE
a3837e0f-e6cb-48f4-8e1c-1ac98d15a11c|1|5.0
Just perusing the MSDN and TechNet blogs today and came across a cool blog on Securing SQL Server Integration Services in the enterprise with respect to the SSIS Engine, SSIS packages, and the SQL Engine. I’m battling dealing with security problems where I work, mostly related to what was done in the past before I joined, trying to unravel security and permissions that used to be opened up but now closed. This post looks like some good info to start, and I hope to add my own best practices to his list as well. Lee ------------------ http://blogs.technet.com/b/fort_sql/archive/2011/11/08/securing-sql-server-integration-services-ssis.aspx
4807f4ac-ac77-4d6c-b805-aa8a0bba2444|0|.0
Date fractionals in SSIS get chopped off; without constructing something in a script task, using an expression seems to be the most logical way to get a date to push to all components and multiple data flow tasks to share a common date. I start off here with the following (highlight the variable that you create and go to Properties>>Expression. Also set EvaluateAsExpression property to True): I get the fractional time (ss, ms) here as you can see. I tried every type case available and, unless I missed one, I did not get a date with enough precision to give me what I was looking for. As I did with the previous blog on decimal mapping to an SSIS variable, I mapped first to a string (in this case WSTR) with a length of 29. Note that this length has a precision to 9 places. This turned out to be a problem, because I already know that in TSQL the datetime data type goes to three, and the datetime2 goes to seven decimal places. SELECT GETDATE()
SELECT CAST(GETDATE() as datetime2)
2011-11-05 09:01:09.287
2011-11-05 09:01:09.2870000
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
I create the following:
DROP TABLE SSISDateVar
GO
CREATE TABLE SSISDateVar (Dt datetime, Dt2 datetime2)
Then I map the variables in an SSIS ExecuteSQL task. Note that I use DBDDATE, which interestingly enough does keep the precision for me.
I came up with the following to mapping, which seems to work fine. Thank goodness for datetime2, eh?
INSERT INTO SSISDateVar (Dt, Dt2)
VALUES (CONVERT(datetime,LEFT(?,23),110), ?)
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Note that I had to chop off the datetime as that won’t map unless it’s length of 23, which matches the precision down to the milliseconds acceptable.
SSIS variables of datetime seem to lose precision, so I go to a string. The ‘challenge’ is to convert them back to an acceptable date, depending on what datetime datatype that is being used. I’ve done this before sometime back in the day, surely, but couldn’t remember what I did. I’m not certain if this is the best way to do this operation, but it seems to work OK for me.
Thanks for reading,
Lee
----------------------------
Download pkg (SSIS 2008 R2)
be1d7309-bf50-4c58-b79d-151e146cff9f|0|.0
Running SSIS, you might get the following: Information: The buffer manager failed a memory allocation call for 10485288 bytes, but was unable to swap out any buffers to relieve memory pressure. 108 buffers were considered and 108 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked. There are a couple of old posts, here, and here, as well as another stab here; we ran into this last week when running an SSIS package that a developer built awhile back, and when we looked at it we immediately thought that he had too many transformations (source to destination) in his control flow task. Without cracking BOL or digging into posts on the internals or dialing up some SQL engine MVP, we did the most logical thing and removed 1/2 of the transformations and it worked fine. The error isn’t exactly an error – it’s informational, so the package continues to run. Logging into the server and looking at perfmon, it was easy to see that the process ate up too much memory (completely maxed out), so logically decreasing what the package did eliminated the memory pressure and cleaned up the informational message. Alternatively, we could have looked at buffer rows, calculating row size, etc. we were aware of the available memory on the server and knew immediately that the process was too much for the given setup. Thanks, Lee ------------------------------ http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/45ae064b-10b1-401a-b9df-3187f9a6b5ca http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/d6d52157-0270-4200-a8c2-585fa9a0eed5/ http://sqlserver-qa.net/blogs/bi/archive/2010/02/01/the-buffer-manager-failed-a-memory-allocation-call-for-20485670-bytes-but-was-unable-to-swap-out-any-buffers-to-relieve-memory-pressure.aspx
3043465d-3a74-4e85-a254-32e629a2da06|0|.0
On my little “Dynamic SSIS Packages” series, I’m getting some hits but not a lot of comments or questions/feedback. Is this concept too difficult, or did I not do a good job explaining? I think not the latter and surely not the former either, because I spent three or four posts just describing what the concept was and what we were trying to do. And, we haven’t really coded yet, but rather just completed setup and execution. The delivered stuff that I have in previous posts should load and run without too much problem, and I hope if interested that you take the time to go through this process – I’ve been working on DTS since SQL 7, and SSIS since before SQL Server 2005 came out via the earliest CTP, and I guarantee that you’ll like these packages. They’re super cool, fast, and stable. I’ve said before that I believe they are the slickest SSIS packages that I’ve ever seen, and still feel that way. For importing or exporting data, this dynamic package kicks-ass! Besides F8 “Step Into” for debugging SSIS code works great, and while our ‘infant’ package does not have any included for debugging (you have to write your own to get bugs to actually print), a neat trick to add is the Application.SaveToXml Method. This is pretty slick because if the package makes it all the way through, but then bombs on the pkg.Execute() step, you’re gold because you can take the saved package, import it into BIDS, and then go look at it to see where the problem may be. You can even run it to test what you believe you need to code to implement the fix. Here is the part to save out the package, fairly simple: ' Save the package to a .dtsx file. app.SaveToXml("C:\temp\SQLToFileX.dtsx",pkg, Nothing) Locate the app.SaveToXml call within the script task code. I can take the SQLToFileX.dtsx package, add it to Visual Studio, and inspect it more closely after the package has run. Note that for each loop in your ForEach Loop, you will get a new package, and the code will overwrite the previous. So if you have 10 rows in your configuration tables,which equals 10 exports, but the 10th fails, only the 10th package will be saved to c:\temp\SQLToFileX.dtsx, and then you can go look at that one to try and figure out what went wrong. Lee -------------
af40d90c-d1a6-4377-b5bd-298170106ca7|1|5.0
I had a post earlier for counting files in a folder, but it was DOS and could be called from a process task...that's really not the way that I'd do it I guess, so don't use that example. I see plenty of hits for folks searching and looking at that page, so I offer up this as an alternate. If I were tasked to do this in SSIS I'd probably use something like the following.
Crack open SSIS, add a Script Task to the Control Flow, create two SSIS variables, one called Folder of type string, and another named Count of type integer, and then add this code to the script task:
using System;using System.Data;using Microsoft.SqlServer.Dts.Runtime;using System.Windows.Forms;using System.IO;
namespace CountFilesInFolder{ [System.AddIn.AddIn("ScriptMain", Version="1.0",Publisher="",Description="")] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase { #region VSTA generated code enum ScriptResults { Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure }; #endregion public void Main() { string foldername; int count; foldername = Dts.Variables["Folder"].Value.ToString(); count = Directory.GetFiles(foldername).Length; MessageBox.Show(count.ToString()); Dts.Variables["Count"].Value = count; Dts.TaskResult = (int)ScriptResults.Success; } }}
Directory.GetFiles(foldername).Length will count files for you in .Net/c# probably as good as any method, so use this and not the garbage that I posted in the other blog. Remember, I geek here, so take this stuff (including this post) at face-value.
Thanks,Lee
----------------------------
MELINDA - Well, I just thought I'd give you them. I liked walkin' with you. I got a blister the size of a quarter on one heel. Well, I'll see you sometime, I guess. (She walks to the door and stops as if she expects Karl to say something.)
KARL - A blister shore can hurt.
Posted in:
.Net ,
SSIS Tags:
d4edab7e-8e1f-4038-867a-39cf2a27c419|0|.0
In the first three parts of the series, we looked at setup for dynamic packages. It is very important that you read these first before attempting to download and run this package…it won’t work for you until you have an understanding of this concept, and have the prerequisites already installed. In this post, I attach the .dtsx file for you to download and run, along with a short video to "demo" the functionality. As mentioned, suppose that you had 2000 files to import. You certainly would not want to write 2000 SSIS packages; you wouldn't want to rewrite those same packages somewhere down the road when Microsoft decides make a slight change to SSIS, either. With this package that I have here, you write only one to import, and one to export - parameters that you set up prior to executing the package directs the generic package on the fly. Simply create the drive tables with data to feed the parameters, and the package creates the sources, pipelines, and destinations in memory. To gain this type of functionality, we leverage the powerful SSIS API. As a reference, check out Building Packages Programmatically on the Microsoft site. While they don't show you how to do exactly what we do here, you can see how this package was created by combining all of the information from their pages. For watching the video below, I wanted to keep the screen resolution at full, so make sure and use full screen to view. You can also flip to HD for a clearer image. Now that I try it though, I see that it doesn’t expand to full screen here in my blog, so go ahead and just click through the image below and watch it on YouTube (you can right-click it as well and choose “Watch on Youtube”). Make sure and go to 720 HD and full screen for best viewing. Maybe someday I’ll have a good screen capture program for videos like all of the SQL Server MVPs and gurus have, but right now I have to use the freebies. Dynamic SSIS Part 4 Pretty sweet, eh? I believe that this is the slickest SSIS package that I’ve ever seen, certainly, and I hope that you like it as well. I’ve seen blogs and articles on the subject of ‘dynamic’ packages, but maybe not one to this extent. I am planning the following posts as follow-ups to this series: Import Package Combining both packages Dissecting the SSIS API and the code for each dynamic SSIS package Expanding the scope of these packages (variables, logging, debugging, error handling) Others Give me a shout if you have problems running this package, and again, if you have not read the first three parts, please do so before trying to wire this package up. Thanks for visiting, Lee --------------------------- Edit: 8/10/2011 – I’ve added the database scripts and zipped up the SSIS package here. Scripts and SSIS package Test database Configuration database Extract.dtsx
Posted in:
SSIS ,
Vids ,
SQLServerPedia Tags:
b41913a1-3804-4910-9d10-b75d5cad252f|3|5.0
Check out this blog for passing parameters for the Execute Package Task - they’ve made it a bit simpler to pass parameters from parent to child packages in SQL Server Denali CTP3. I actually never thought it was that difficult to do it with SQL Server 2008, but this does make it about a two-click step now. Take a look at this posting on the SSIS Team Blog and see what you think. Good stuff to know when we get the new version. Lee ---------------- Oh hellz yes! New Beavis and Butthead episodes coming this fall to MTV. Booyaaaaaaaaah! http://blogs.msdn.com/b/mattm/archive/2011/07/18/changes-to-the-execute-package-task.aspx
0da3e4b6-1f38-4ce2-a672-4090fef087fb|0|.0
In this post let’s load the export server and export dynamic parameter tables, along with the file delimiter mapping. Attached are three files – change the parameters (server names, file export locations) for your particular environment, paying attention to spaces, backslashes, etc. so that the package will work properly. Also included are the two following queries, the first for the server level parameters, and second for the dynamic file parameters. These queries will be placed in two separate ExecuteSQL tasks that will feed the script component. Yours should also look similar to these as well after you have inserted your records. Also, if you do not have the AdventureWorksDW2008R2 database loaded, go ahead and download this if you want to follow along with me. This is what we’ll source our example data from. Make sure that you’ve read parts 1 & 2 of this series so your down with these blogs. In the next posting, I’ll attach the package for you to wire up to. Lee --------------------- Queries for SSIS ExecuteSQLTasks – Server parameter, and then file “dynamic” parameters. SELECT ISExportSourceDBname as SourceDBName
,ISExportServername as ServerName
FROM tbl_ISExportServerParms S
SELECT
ISExportSourceTableName AS TSK_DWH_TABLE
,ISExportSourceOutfileName AS PKG_OUTFILE_NAME
,ISExportSourceOutfilePath AS PKG_OUTFILE_PATH
,ISExportSourceLogfileName AS PKG_LOGFILE_NAME
,ISExportSourceLogfilePath AS PKG_LOGFILE_PATH
,ISExportSourceExtractSELECT + ' ' + ISExportSourceTableName AS TSK_DWH_SQL_SELECT
,ISExportDelimiter
FROM tbl_ISExportDynamicParms D
INNER JOIN dbo.tbl_ISExportServerParms S
ON D.ISExportConfigurationID = S.ISExportConfigurationID
WHERE D.ISExportExtractFlag & S.ISExportExtractFlag =1
Result for SSIS ExecuteSQLTasks - yours need to look similar to these.
Files-insert data into Export tables
tbl_ISExportServerParms Server Parameters
tbl_ISExportDynamicParms Dynamic Parameters
tbl_ISFileDelimiterMap File delimiter table
59ff998f-b3e7-4f3b-9e2b-8b39a68c23de|0|.0
This series of blogs is to demonstrate a dynamic SSIS package that you can a) point to a relational table or tables to export data to a text file or files, and b) point a package to a folder or set of folders containing text files to import into a relational table, each without having to build n number of sources and destinations, and manage x number of mappings. In order to create these packages, we need to first create the database schemas that will allow for the dynamic nature of the tasks; essentially, all of the tables to make the package(s) “parameter-driven”. Part 2 covers this task. If you missed the first blog, catch up by reading Part 1. These packages are parameterized by rows in tables; simply making additions or changes to records without having to redeploy, recompile, reconfigure, and remap sources, transformations, and destinations is cool. Think of parameters at the server and the file level. Some parameters may cross into both types for manageability, so keep this in mind when you take this example and expand on it. For example, a file delimiter can be at the server level, meaning that all files in the import use the same file delimiter from that particular server, or they can be at the file level, where each file may have a different delimiter on the same server. Note that the following example schema is a minimal version of what I have used in production. You can add many different types of parameters to drive how you data is imported and exported. You can also create a far more elaborate variable design than what we do here; I have used a wide variety of inputs as well, such as configuration files, environment variables, etc. in order to make the packages even more robust, which may be important in some implementations when moving or pointing packages, sources, or destinations from one server to another. When you break this down, there are four basic types of parameter “paradigms” that we work with, needed for the configuration database. These are 1) export static, 2) export dynamic, 3) import static, and 4) import dynamic. The names and columns can change, of course, but I use these for the sake of simplicity. Let’s create the schema. I include only the essentials in the body of this blog, but file attached with all DDL. Export Server Parameters CREATE TABLE [dbo].[tbl_ISExportServerParms](
[ISExportConfigurationID] [int] NOT NULL,
[ISExportServerName] [varchar](50) NULL,
[ISExportSourceDBName] [varchar](100) NULL,
[ISExportFileDelimiter] [char](1) NULL,
[ISExportExtractFlag] [bit] NULL,
CONSTRAINT [PK_tbl_ISExportServerParms] PRIMARY KEY CLUSTERED
(
[ISExportConfigurationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
This table contains the parameters to drive the export package at the server level. They are pretty self-explanatory as you can see, and will allow us to map connections to the source SQL Server to the destination file.
Export Dynamic Parameters
CREATE TABLE [dbo].[tbl_ISExportDynamicParms](
[ISExportFileID] [int] NOT NULL,
[ISExportConfigurationID] [int] NOT NULL,
[ISExportSourceTableName] [varchar](40) NULL,
[ISExportSourceOutfileName] [varchar](40) NULL,
[ISExportSourceOutfilePath] [varchar](40) NULL,
[ISExportSourceLogfileName] [varchar](40) NULL,
[ISExportSourceLogfilePath] [varchar](40) NULL,
[ISExportSourceExtractSELECT] [varchar](4000) NULL,
[ISExportSourceExtractDELETE] [varchar](4000) NULL,
[ISExportExtractFlag] [bit] NULL,
[ISExportDelimiter] [char](1) NULL,
[ISExportNotes] [varchar](1000) NULL,
CONSTRAINT [PK_tbl_ISExportDynamicParms] PRIMARY KEY CLUSTERED
(
[ISExportFileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
This table holds the parameters to drive the export package at the table/file level. The key columns here are the ISExportSourceTableName, which is the table that you wish to point to create an extract, the ISExportSourceOutfileName, the name of the file, and ISExportSourceExtractSELECT, which is the TSQL statement that is stored in the table. Obviously all are “key” – the thing won’t work without them - but these should be noted in particular.
Import Server Parameters
CREATE TABLE [dbo].[tbl_ISInFileServerParms](
[ISServerConfigurationID] [int] NOT NULL,
[ISServerServerName] [varchar](30) NULL,
[ISServerDatabaseName] [varchar](30) NULL,
[ISServerExtractGroup] [varchar](30) NULL,
[ISServerFileDelimiter] [char](1) NULL,
[ISServerExtractFlag] [bit] NULL,
CONSTRAINT [PK_tbl_ISServerConfiguration] PRIMARY KEY CLUSTERED
(
[ISServerConfigurationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
This table holds the parameters that drive the import at the server level. So if you have a file and are importing this to server a, you simply change the ISServerServerName attribute to server b, and this will change parameters in the package to look for that server. Note that at the server level I can use the bit flag to turn off that particular server, and at the file level, there is a flag to do the same for a given file.
Import Dynamic Parameters
CREATE TABLE [dbo].[tbl_ISInFileDynamicParms](
[ISInFileConfigurationID] [int] NOT NULL,
[ISServerConfigurationID] [int] NULL,
[ISInFileDestinationTable] [varchar](50) NULL,
[ISInFileName] [varchar](50) NULL,
[ISInFilePath] [varchar](50) NULL,
[ISInFileLogID] [int] NULL,
[ISInExceptionFileID] [int] NULL,
[ISSQLLoadDelete] [varchar](100) NOT NULL,
[ISInFileExtractFlag] [bit] NULL,
[ISInFileFileDelimiter] [char](1) NULL,
[ISInFileNotes] [varchar](4000) NULL,
CONSTRAINT [PK_tbl_ISInFileConfiguration] PRIMARY KEY CLUSTERED
(
[ISInFileConfigurationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
Finally, this is table holds the dynamic parameters for file import. If you’ve looked at the previous three DDL statements, you’ve got the idea now. The tbl_ISInFileDynamicParms table will contain the import file name, paths, etc. A schema for the whole thing should look like this diagram:
In the next blog we’ll load the export parameter tables and get the export package running.
Thanks,
Lee
---------------------
Example Database Schema
5e96c5e2-7f8c-4612-8cc0-dae41091eea4|0|.0
Microsoft SQL Server Integration Services (SSIS) is really a great tool in my opinion. Based on my experience to date using it (pre-SQL 2005 release, with the first CTP in the first quarter of 2005 me and an colleague Tom Davis were doing “Tech Nights” at Software Architects, now Sogeti…wow I’ve used it six years now I guess), it’s solid, and a substantial improvement over DTS. Is it what we want it to be yet? Probably not, but it will get there someday and hopefully be as feature-rich as the other popular ETL tools. One thing that it allows the practitioner to do is various type of customization, either with the script task, script component, custom component, custom assemblies, or programming the API directly. This is a good thing and a bad thing. Bad because many experienced ETL developers subscribe to the “hands off the keyboard” approach to ETL; this rules out coding of any kind. There are various reasons for this that I will neither argue nor hash out here, but suffice it to say that traditional data warehouse folks who used the tools for many years relied many on delivered tasks and components and did not look to code and customize as a general rule. Good, on the other hand, because if the particular tool is not available…just build it!
Imagine for a moment a process that requires you to create 500 data flows, either individual text file source to database destination, or database table to text file. How would you split it up to manage it? 50 packages with ten flows? 100 packages with five? Or maybe one package with 500? You get the picture, and it’s not pretty. Not only that, but after you build it, try managing it right? Sounds like a nightmare to me. I’m going to show you a way to create all of this functionality in a single package, and you never need to map a column or drag a connection one. All of the transformations are handled, all of the data types and string lengths taken care of, all built dynamically and parameter-driven by a set of database tables. Sweet!
This series - Dynamic SSIS Packages- is about programming the SSIS Application Programming Interface, or API. I thought about sending this off to SQLServerCentral, one of these online tech magazines, or some of the other outlet, but if you follow me at all, you know that I gave up on those sites and just post my stuff here. To my knowledge, there isn’t a tutorial on this out on the web, or a series of any kind yet, so that’s one reason that I wanted to put this together…maybe there is, but I thought that I would write this nevertheless with the hopes of helping someone out as well as learning more about it myself. Thanks much to Kenny Chung – a senior database developer from Dallas who created the particular concept and code in DTS and SSIS whom I worked with at Alcon – for letting me “take over” the code so to speak. Kenny is a great programmer and a good friend and I appreciate his insight and creativeness in building the processes that I will demonstrate in this series. These packages are truly “dynamic” in nature.
Here’s what I will blog about in this series in the coming weeks:
1. Introduction (this post)
2. Database design and setup
3. Database to a flat file
4. Flat file a database
5. A in-depth look at the code
I may have more or less so we’ll see how it goes. What I want to do right off (after design and setup), however, is get the code out for you to get it installed. After we have it up and running, then we’ll look at it closer to see what’s happening. I hate to do a step-by-step but leave out being able to actually see something happen, so I’ll give this method a try and you can let me know how it works for you.
My obligatory caveat: I do not recommend using custom API packages unless 1) there is a great need for customization, or 2) it substantially makes a series of tasks easier to manage. It is also nice to have someone in the department familiar with vb.net or c# code as well; you would not be advised to code something like this without someone available to debug or modify.
About ten years ago, I worked a SQL Server gig at Jobs.com. A co-worker there mentioned something one day about “Knowledge is Power”, but a few years ago I came up with my own saying, and that is “Sharing is Power”. Jobs.com actually went out of business (gee I wonder why) and the domain is now owned by Monster I believe. With that kind of thinking in the IT shop, go figure eh? Anyway, I look forward to sharing this concept, and while it’s not proprietary or new, I hope to present in such a way that you can gain a better understanding about this great gift of the SSIS API to create your own custom packages.
Thanks for reading,Lee Everest
-------------------------------
Posted in:
SQL Server 2008 ,
SSIS Tags:
4d1989c9-3e00-4a18-a003-c468be2c971f|0|.0
A blog this week from the SQL Server Performance Team tells about a new SQL Server Integration Services (SSIS) component available for BIDS in SQL Server 2008 and SQL 2008 R2 on Windows Server 2008/R2: There is a new transform component available for SQL Server Integration Services. It’s called the Balanced Data Distributor (BDD) and the download is available here. The BDD provides an easy way to amp up your usage of multi-processor and multi-core servers by introducing parallelism in the data flow of an SSIS package. More info: Microsoft® SSIS Balanced Data Distributor (BDD) is a new SSIS transform. This transform takes a single input and distributes the incoming rows to one or more outputs uniformly via multithreading. The transform takes one pipeline buffer worth of rows at a time and moves it to the next output in a round robin fashion. It’s balanced and synchronous so if one of the downstream transforms or destinations is slower than the others, the rest of the pipeline will stall so this transform works best if all of the outputs have identical transforms and destinations. The intention of BDD is to improve performance via multi-threading. Several characteristics of the scenarios BDD applies to: 1) the destinations would be uniform, or at least be of the same type. 2) the input is faster than the output, for example, reading from flat file to OleDB. In case you haven’t geeked with a lot of hardware lately, the influx of multi-core processing has exploded; even the manufacturers are adding HyperThreading technology back into servers and laptops. These CPUs are capable of multi-threading while some of the processes that use them – namely SSIS – have a hard time keeping up with the capability of parallelism. It looks like this demand has prompted this mid-release of a component to help us since components in the current and latest version of SSIS doesn’t support this type of parallel processing. You can read about it here download it here. Links are below to check out. I’ll have to fire up a couple of examples and see what’s up. Oh, about the download…you download a Word document from the download page, enable editing if necessary, and then click on a button at the bottom, which opens links for x32 and x64. Thanks for reading, Lee --------------------- http://blogs.msdn.com/b/sqlperf/archive/2011/05/25/the-balanced-data-distributor-for-ssis.aspx http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ea0a1544-5ee4-4ad4-9d76-296d0632f162 http://www.microsoft.com/downloads/en/confirmation.aspx?FamilyID=ea0a1544-5ee4-4ad4-9d76-296d0632f162
aa501448-0dc2-4d38-84da-104235301a3e|0|.0
Since most of the hits that I get on this blog are centered around SSIS, I thought I’d share this great post by Cathi Mi. If you watch the vid, you should be able to handle the DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER error next time that it pops up. Oh, by the way, I have a series on the greatest SSIS packages ever created coming soon. I’ll be posting a series of about 5 of them and they rule! Lee ------------------- WTF happened to the font on this blog posting??? http://blogs.msdn.com/b/cdm/archive/2011/05/11/why-can-t-my-package-acquire-a-connection-from-its-connection-manager.aspx
8fce7f49-ff86-4ac7-9177-ec89cc2c44f6|0|.0
BUG! The following code has a bug. Add sb.length=0 so the StringBuilder resets. See Post with correction. This code is now producing 150-200 files/second. Going from best backwards, I took the code from yesterday and threw it into a script task (SQL Server 2008 R2). Code is below, and surprisingly, running out of debug mode, it ran very closely to the c# console application. I had to make only a few changes when migrating it to BIDS, so check it out. Come up with anything faster than mine yet??? Lee Everest --------------------- Script Task code - c# using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.IO; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; namespace ST_eabdae14a6634c11b8193c743bc05b6a.csproj { [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase { StringBuilder sb = new StringBuilder(); #region VSTA generated code enum ScriptResults { Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure }; #endregion public void SaveToFile(string fileName) { System.IO.TextWriter w = new System.IO.StreamWriter(@"c:\temp\files\" + fileName + ".dat"); w.Write(sb.ToString()); w.Flush(); w.Close(); } public void Main() { SqlConnection sqlConnection = null; int maxvalue=151000; string dbconn = "Application Name=SqlTest;Data Source=SQLVM1;Trusted_Connection=yes;database=tempdb"; using(sqlConnection = new SqlConnection(dbconn)) { sqlConnection.Open(); SqlParameter param = new SqlParameter("@id", SqlDbType.Int, 2); SqlDataReader reader; using (SqlCommand cmd = new SqlCommand(null, sqlConnection)) { param.Value = 1; cmd.CommandType = CommandType.Text; param.Direction = ParameterDirection.Input; cmd.Parameters.Add(param); while ((int)param.Value<maxvalue) { cmd.CommandText = "SELECT id FROM deleteTablexus WHERE idx = @id"; reader = cmd.ExecuteReader(); while (reader.Read()) sb.Append(reader[0].ToString() + "\r\n"); SaveToFile(param.Value.ToString()); reader.Close(); param.Value = (int)param.Value + 1; } } } Dts.TaskResult = (int)ScriptResults.Success; } } } Almost forgot – the code to create the table. Use this to generate the data, in case you decide to take on my challenge. L8rs. TSQL to Gen the data... DROP TABLE deleteTablexus GO SELECT a1.idx, a1.idx + x.xx as id INTO deleteTablexus FROM ( SELECT 1 AS ID UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ) a0 CROSS JOIN (SELECT DISTINCT number AS idx FROM master..spt_values WHERE number BETWEEN 1 AND 2070 UNION ALL SELECT DISTINCT number+2070 AS idx FROM master..spt_values WHERE number BETWEEN 1 AND 1699 ) a1 , (SELECT TOP 10 CAST(RAND() AS decimal (10,2)) as xx from sys.syscolumns)x ORDER BY 1,2 GO CREATE NONCLUSTERED INDEX IDX ON [dbo].[deleteTablexus] ([idx]) GO
Posted in:
SSIS ,
Etc. (Off-topic) Tags:
8abfb5b2-29f9-4e53-999c-d0ec516809ab|0|.0
If you’re trying to wire up to Excel using a file extension other than .xls or .xlsx, you may get one or more of the the following errors: File path contains invalid Excel file. Please provide file with .xls or .xlsx extension. Test connection failed because of an error initializing provider. Could not find installable ISAM. Test connection failed because of an error in initializing provider. Unrecognized database format ‘filename’ When I tried for the first time to connect I was here: Simply connect to an OLE DB source and not an Excel file, and set a couple of properties and you should be gold. If you reverse the order of the HDR and the Excel 12.0 format in the “Extended Properties” box, it gives you an error, so do it in the order that I have, above. Thanks for reading, Lee Everest --------------------------- The SQL Police should have no problem with this post…I think.
140024d5-1fc1-495b-a681-32093786dded|0|.0
You might get the following error after trying to run a long or I/O intensive SSIS package while using the VS 2008 IDE, and starting the package by doing a Debug>>Start Debugging. EventType clr20r3, P1 devenv.exe, P2 9.0.30729.1, P3 488f2b50, P4 system.windows.forms, P5 2.0.0.0, P6 4889dee7, P7 601e, P8 0, P9 system.outofmemoryexception, P10 NIL. Two options here – either fix the package, or try running by doing Debug>>Start Without Debugging. (That’s where the DOS window pops up). Using this method it won’t run your IDE out of memory. Lee Everest ---------------------- I’m going to try option one starting tomorrow. It’s a real jim-dandy right now.
5ca15141-c3d8-40e6-8026-8f7e8db45e6b|0|.0