When mainframes or partners are uploading a file, you don't want to process the file until you're certain the file is fully uploaded. One way to do this is with a Script Task in SSIS. There is no magic property to determine in the operating system to see if the file is in use though so you'll have to essentially try to trigger an error and then gracefully trap it and then set a variable that can read. In an SSIS Script Task, you can use similar VB.NET code as what you see below to do this:...
Read More
Thanks to all who came to today's session on Introduction to SSIS. Tomorrow's session is on SSIS for the DBA. If you have not registered, I'm afraid we've maxed out the conference system at this point with 3200 people spread across Wednesday's sessions and we're expecting the typical webinar drop off, which should still be a large number. Hopefully on Wednesday we don't "stress test" the Gotowebinar system like today, bringing it to its knees :). The Wednesday session has a number of e...
I was recently working with FoxPro for Unix as a data source using ODBC in SSIS (story for another day) and found some interesting scenarios inside of it. We wanted to pull the table name dynamically, thus changing the SELECT statement inside the SSIS Data Reader Source. Whilst this is quite easy in OLE DB Sources, there's no obvious way to do this in the Data Reader Source for ODBC or legacy connections as you can see in the below screenshot. There's still a workaround, which feels like a hack ...
I had been working in SQL Server 2008 for a number of months while working on the upcoming 2008 books and never had experienced this issue until just today. In hindsight, I can't imagine how I didn't experience sooner. The behavior change is when you save a table in Management Studio that requires the table be dropped and recreated behind the scenes, the change will fail by default with the below warning. In SQL Server, tables are dropped, recreated and reloaded automatically for you without hav...
Embarrassingly enough, since SQL Server 2005 came out, I've made myself get in the habit of typing localhost instead of (local) because (local) seemed to time out on my laptop. I never really cared enough to research why localhost worked for my server name but (local) did not. In SQL Server 2000, I had written tons of DTS packages to communicate to (local) and once converted, they timed out for some mysterious reason. Well finally curiosity took me and I did some research. It turns out (as...
Yesterday I finished up my Professional SQL Server 2008 Administration book at long last, which makes my 9th book. I've never tried to write two books at the same time but I can tell you I'll never do it again! Writing is always like a bad hangover where you wake up the next day saying, "I will never, ever do that again" and then two weeks passes and you're back with a beer in front of you. Regardless of the pain, I learned some interesting new things in this book. My most challenging ...
In DTS if you want to process files in a specified folder then you have to do good amount of work. But If you want to implement same in SSIS then you don't have to do much work. You can use "Foreach Loop Container" in SSIS to loop through files in a specified folder. The following sample SSIS Package shows how to process each file (Nightly_*.txt) in "C:\SSIS\NightlyData". After each file is processed its moved to Archive folder. Click here to Download sample package Sa...
Data-Connectivity Providers and SSIS Note: The following is not a definitive list of all data providers, and not all data providers on this list have been tested by Microsoft with SSIS. Only current Beta or shipping products are listed. Information about third-party products was provided by the product vendors and could not be independently verified. Source : Microsoft.com [ http://technet.microsoft.com/en-us/library/bb332055.aspx ] Last Updated : 4/1/2008 Data source Released by Data-acce...
Here is the list of couple of major changes in SSIS 2008. New Script Environment: Script Task now supports VB.net and C# both languages. In the previous version of SSIS only VB.net support was there. Enhanced SQL Statements: Transact-SQL supports the use of a MERGE operation in an SQL statement. The MERGE operation enables you to express multiple INSERT, UPDATE, and DELETE operations in a single statement against a specified target table. Change Data Capture (CDC): Integration Services includes ...
One of the most frustrating learning curves for a DBA going to SSIS is the case sensitivity of the environment. Many developers are quite used to this due to option explicit .NET programming languages. The case sensitivity can in some cases create behavior that is not expected and may give you bad results if you're not careful. One such example is with the Lookup Transform, where comparisons against the cache are case sensitive. If you do not expect this, you may have a miss in a match that is a...
Well I feel like a college professor that shows the student a five page math problem and then follows up with a single button you could hit in your calculator to do the same thing. I used to hate that and now I'm that guy :). Anyways, recently I showed how to do string conversions with the proper case (first letter in every word capitalized) by using regular expressions. While that was a pretty elongate way to do it, you can perform the same function with a simple built-in function called VbStrC...
DDL triggers are a fantastic way to prvent DDL events in a production database. In the following database triggers, you prevent any type of DDL event, like altering a sproc in production. If a statement is issued, the statement is logged, rolled back and a user receives a message. Databae triggers can be raised at nearly any level. The below example captures any database-level event. There are also server DDL triggers that captures server events like creating logins or changing the configuration...
By default, SSIS files in development are encypted to prevent an unauthorized person from seeing your SSIS package. The type of encyrption is seamless behind the scene and is at a workstation and user level. So, if you were to send a package that you're developing to another developer on your team, he would not be able to open it by default. The same would apply if you logged in with a different user. You would receive the below error: There were errors while the package was being loaded. The pa...
SSIS has the ability to do very detailed logging that puts DTS Package Logs to shame. In the past, you would have to had to write customized logging routines to do what SSIS does out-of-the-box. SSIS offers a number of logging providers that you can select by going to SSIS menu and selecting Logging. You must then chose what type of provider you wish to use for logging. Which provider you chose depends on your goals: SQL Server Profiler create a profiler trace file - This option gives you some i...
Using a Script transformation is a handy way to extend what transforms are available to you. For example, picture social security data that goes into your Script transformation unencrypted and then out encrypted. Typically, you can also use this type of custom script to extend what a Derived Column task can do. If you have the choice between the two though, always choose a Derived Column. A good extension of a Derived Column transformation into a Script transformation would be where you need to ...
The OnPipelineRowsSent event functions in the data flow to log how many rows go between step to step in the pipeline (or from input to output). Once you have a data flow task, you can right-click in the design pane and select Logging. Configure the logging provider and now notice in detail you have OnPipelineRowsSent. This will now create a record for each transform (source or destination) that the data goes through to show how many rows were sent through the pipeline. It writes this though in a...
One of the key reasons that you use the script task is to change the value of a variable at runtime. There is a lot of real-world scenarios that you would use this for. If you're reading this you have already probably thought of a few. To set a variable in the script task, there are two main methods you can use. Method 1 This method involves using the LockOneForWrite method in the VariableDispenser class. The advantage to this method is it allows for you to read and write to the variables at run...
Templates in SSIS provide a great way to create standards across your company or enterprise. For example, you may want to enforce a standard annotation set of notes at the top of each package or have each package come with a standard set of connections or error handlers to speed up development. To use a template, first create a package just as you would want to see it as a template. Add all the connections, tasks, comments or log providers. After the package meets your needs, copy it to the foll...
After a lot of work, I'm excited to release the SSIS cheat sheet. This was co-written with Dustin Ryan on our team. In it, you can find the common SSIS problems and how to solve them in SSIS expressions. Enjoy! http://www.pragmaticworks.com/cheatsheet/