XCopy is great but sometimes you need something a little more powerful. Powershell can give you the ability to perform more complex manipulations.
Here we’re finding the previous weekday and using it to find, copy and rename a set of files into an automated regression testing environment.
Continue reading “Simple PS script to copy files from one share to another”
A simple piece of code to help you get the execution plan and the query text for a currently running query. This is especially useful when dynamic sql is being run against your database or if DBCC InputBuffer reports that a stored procedure is being run. You can actually view the execution plan for the batch that it’s running. It works using the 2005 management views and functions.
Continue reading “Get the query and execution plan for a session”
Or more accurately: Preparing an encrypted TDE enabled database for restore on a Standard Edition SQL Server
I recently had the challenge of restoring an encrypted database on to a standard edition server to enable further development on the database code. It took some work, but to save you some time, I’ve listed the steps (and the T-SQL) in this article that you need to take in order to accomplish this.
The script task in SSIS was a fantastic improvement on DTS Active X script. The reasons for this in my view are 2 fold:-
These are both reasons in themselves that make it a compelling choice when deciding how to develop your package logic.
However, I have noticed at both client sites and on the forums, that there are those (the script task junkies!) that use the script task in almost any scenario when it is just not necessary. It makes maintenance and support tricky at best and sometimes impossible…
I don’t know about you, but one of the things that drives me to distraction when writing SSIS packages is being forced to use excel as a data source. I’d like to qualify that statement by adding that it’s the use of excel in the face of all reason, to do the job that other software would be better for. Unfortunately though, corporations and people being what they are, they are a fact of life for an ETL developer.
Amazon Link: Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
Continue reading “Excel VBA to prevent user generated errors in SSIS”
Here’s the scenario… You have a RAW file which contains data from many files.
In the subsequent dataflow you need to perform a lookup against a large reference table, however you want just a subset that reflects the period contained within your RAW file.
Question 1, how do you find out the earliest date used within your RAW file data? And question 2, how do you write it to a variable so that you can use it in the subsequent data flow?
Continue reading “SSIS – Writing to a package variable in a dataflow”
Many of you will have heard the mantra that loops are bad, set based is good. But how do you get around them?
The fact of the matter is that there are very few circumstances where a loop is the only way to achieve your objective. A look through the SQL forums will show you many a technique to turn iterative row by row processing into single transaction set based processing. I’m refering to hierarchical query techniques (see nested set theory) and of course the subject of this post, the numbers table.
I talked in a previous post about the possibility of using the format command and bulk insert task inside of a foreach loop in order to load all your tables using a single package… I don’t much like the method as it means you have to run each table load in series, and you’re not taking advantage of SSIS (high speed dataflow task, parallelism and eliminating staging with a single pass transformation).
If you are trying to execute some Jet SQL DDL like CREATE TABLE or DROP TABLE against an Excel workbook and you get this message:
Executing the query “DROP TABLE [Sheet1];” failed with the following error: “Cannot modify the design of table ‘Sheet1’. It is in a read-only database.”.
The order of the configurations used in the development studio is not the same as the order used by DTEXEC, specifically in relation to parent package variables.
Parent Package variables always have the lowest priority no matter what order you set in the configuration manager.
Microsoft have recently added a note to BOL on this subject here
Amazon Link: Microsoft SQL Server 2005 Integration Services
Continue reading “SSIS Parent Package Configuration (Gotcha!)”
