This script might be the best SQL insert generator I’ve seen yet. It’s flexible and deals quite comfortably with the majority of problems a dev can come across when attempting to write one of these. Why re-invent the wheel.
Take a look at the website, there are plenty of neat tips and tricks.
Cheers
Frank
Amazon Link: Microsoft SQL Server 2008 T-SQL Fundamentals (PRO-Developer)
I have a SQL generation package that I am using to create sql files that will later be executed against the production environment. It is necessary to do this for corporate IT policy reasons which I won’t go into, however, suffice to say it isn’t the most efficient way to get new data into the database!
The data is input into Excel by the users and then passed to me the run the SSIS package. This worked fine until recently when one of the Excel spreadsheet I recieved had cells in it that were in excess of 8000 characters wide (containing Legal T&Cs).
Amazon Link: The Rational Guide to Extending SSIS 2005 with Script (Rational Guides)
A quick nugget… If you get this error:
[DTS.Pipeline] Error: component “OLE DB Source” (79) failed the pre-execute phase and returned error code 0xC0010001
Check that your variables are mapped and that they can be read at run time. Similarly if you get the same error code with the description:
Configuration from a parent variable “XXXXX” did not occur because there was no parent variable. Error code: 0xC0010001.
Check that you have spelt and referenced the correct parent variable.
After some prompting, I decided it was about time I posted the solution to my initial post on the subject of metadata driven SSIS.
If you have read part one of this series (or other posts related to replication and sql agent), it will come as no surprise to you to know that I am a big proponent of metadata driven control in SQL Server. Whilst more complex to code, it is infinately easier to manage across environments.
I will add further explanations to this package at a later date, but for now, here is the solution and a very brief summary of what it does.
During a recent data migration from Oracle to SQL Server, I came accross the problem of trying to import Greek encoded strings into our SQL Server database. With the standard character set installed with my Oracle client, all I got was upside down question marks with the odd square and ampersand thrown in for good measure.
I can’t stress enough the importance of designing the framework for your ETL prior to getting stuck in with individual packages.
I bring this up, because I have seen that look of dread on a developers face (including my own) when they are about to deploy a package to the live environment and aren’t too sure what gremlins are hiding in the live environment that don’t exist in development. Not only this, with the proliferation of SSIS packages across the corporation, it can become extremely difficult to figure out and manage the process flow across all your servers.
It is for this reason, that I am a big fan of metadata controlled ETL. And I’m not just referring to a configuration table set up as part of an SSIS package, I’m talking about modelling a set of tables within a control database that can hold all the data that a package might need to run. I’m also talking about a generic parent package that can use this meta data to control the execution of any packages defined within the tables.
I was recently asked if you can only use the msdb.dbo.sysdtslog90 table to log SSIS package execution…
It is possible to log your SSIS execution to a database other than msdb, simply by scripting the sysdtslog90 table from the msdb database, and creating it in a new db.
Then when you configure your SQL Server log provider, use a connection manager that points to the database on which you’ve just created your table.
From time to time these days I have to put my DBA hat back on and review database schema designed by those whose focus is on application development. What I often find is that attempts to improve performance through indexing have been done in vain as the query optimizer either doesn’t use them enough to merit their creation or they have been created on the wrong columns.
Frequently, optimisation is referred to as a dark art, but here I am going to attempt to give some basic pointers to help enighten the novice database designer…
The SQL Command OLE DB component doesn’t like inline comments when using the Native SQL Server OLEDBConnection. Leave them in your SQL Code and you get the following error:-
An OLE DB error has occurred. Error code: 0×80004005. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0×80004005 Description: “Syntax error, permission violation, or other nonspecific error”.
Take them out and hey presto…
