|
|
|
Get Your Own Blog
If you would like to blog on SQLServerCentral.com then send an email to
webmaster@sqlservercentral.com.
Contact the author
All Blogs
Feeds
Archives
for this blog
I'll warn you straight off that this is an advanced technique for a fairly small use case - it is NOT a "try this for all data flow performance problems" type of technique. (Much like the typical "adjust your buffer size" useless advice.) I ran into errors and warnings in my data… Read more
Reading Excel data isn't easy. It should be, but there's a lot of unfortunate barriers in the way that I've discussed before. They boil down to one main reason: Excel wasn't created for IT, it was created for business users. We're quite obviously an afterthought. That said, we're much more… Read more
There are some things that we do in one SQL Server tool, but can't seem to figure out how to do in another. One of those for you might be the LIKE keyword in T-SQL. LIKE is fairly powerful in T-SQL - it does rudimentary regular expressions. How do you do that in… Read more
An recurring issue with Integration Services packages that read or write to file systems is permissions issues. It's quite difficult to have a package run properly when it can't see the source files, or can't write results to a particular network share. But it's insanely hard to get a package… Read more
In some database designs, you may encounter SQL Server columns with a TIMESTAMP or ROWVERSION data type. What are these, and how do you deal with them in SQL Server Integration Services? Time? Version?First, let's talk semantics. There are several unfortunately bad aspects to these names. First, the name "TIMESTAMP"… Read more
Excel 2010 (and presumably the next version of Excel) have taken some security precautions with regards to macros in Excel files. If a spreadsheet has macros in it, you can't save it as an XLSX. Excel forces you to save it with an extension of XLSM, indicating that the file… Read more
Dr. DeWitt has spokento the PASS Summit for the past two years – turning what was otherwise a veryforgettable “day 3” keynote slot into a must-see event for anyone going to theSummit.The past two years Dr. DeWitttook us through the internals of columnstore databases and queryoptimization. They were…Read more
This week, I attended the Microsoft SharePoint Conference 2011 (#SPC11) in Anaheim. Not my usual beat, to be sure, but our company is starting to use SharePoint more, and we have no internal resources trained on it. We have a "wild west" set of sites already, plan to add more… Read more
It's a great day for me and Moulding and Millwork (the company I work for). A few weeks back, I let you all know that I'd been given the green light on finding someone to help me out with Data Warehousing. One of the first people I turned to for… Read more
It's quite common to have parts of your Data Flow that are slow, and there are some techniques you can use to improve performance of those parts. One of them is to try to parallelize the slow operation - but that only works for operations that are parallelizable. Sort isn't… Read more
Hopefully this crowd knows a little about what they're getting into! John Jakubowski ( blog| twitter) has graciously asked me to present on upsert strategies with SSIS. What are "upserts"? A not-so-fancy mashup of the words "update" and "insert". If you struggle with incremental table loading performance -… Read more
First - I'll get the "what's a pipeline got to do with Integration Services" question out of the way. The "pipeline" I'm referring to here is the word commonly used to describe the flow of data in the Data Flow Task. That term doesn't seem to show up in any… Read more
I was recently tweaking a few Data Flows, and settled on a very small, but very useful script to help record execution timing inside the flow. Decomposition Is Time Consuming You may know that I do recommend the decomposition technique to help diagnose performance problems in SSIS data flows. That's… Read more
There are lots of scenarios in SSIS where you'd want to use a connection within a Script Task. Unfortunately, the closest help documentation - the comments inside the Script Task itself - give you exceptionally poor advice: To use the connections collection use something like the following:
ConnectionManager cm =…Read more
Every once in a while, you'll have a slightly more complex UPDATE statement in an OLE DB Command or Destination. You'll use an UPDATE statement or a destination table that needsto use data from one column in your data flow several times. A typical example is a range update: UPDATE table1
SET column1 =…Read more
In developing my data warehouse ETL processes, there are some packages and SQL Agent jobs that are both necessary to the process of deploying a new version of the ETL system - and yet are extremely dangerous. These packages "reset" certain control infomation to a known state, which is a destructive… Read more
Just recently a question was posed in the MSDN SSIS Forums about how to create a "generic" script component that would read all columns for each row passed through it in the Data Flow. The standard impetus for this kind of behaviour is a logging or auditing step in the Data… Read more
The format of sysssislog in SSIS 2005 and 2008 isn't great for human consumption, so what follows is an attempt to reformat it for easier digestion. It isn't perfect - and here's hoping that the new Denali (the next version of SQL Server) server-side toolset will improve the situation it as… Read more
A while back, I ran a contest to find a new name for the SCD component hosted on CodePlex. The results were the best "lemons into lemonade" situation I could have expected. Now I've got some other news I can share that should extend the reach and capabilities of every Data… Read more
Older posts
|
Subscribe to this blog
