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
Here’s a link to some videos that might be worth checking out. To be honest I haven’t viewed one of them yet, but thought I’d share what I ran across, and maybe take a look at a couple this weekend. Looks like there is instruction on partitioning, indexing, clustering, storage, multi-server management, etc…some good stuff eh? Check ‘em out and let me know what you think – they’re geared towards the Microsoft Certified Master program, the one that used to cost 20K but has been conveniently reduced to I think 3K now. Either way, I’m probably never gonna be a candidate for the thing…I’ll leave that for all of the MVPs running around I reckon. With these vids, hey, who needs to pay 3K anyway? Thanks for visiting, Lee ----------------------------- http://technet.microsoft.com/en-us/sqlserver/ff977043.aspx?ocid=soc-n-mea-loc-services-yasserak http://www.microsoft.com/learning/en/us/certification/master-sql-path.aspx
56406662-8f57-48a5-aecf-f4e0cfc77335|0|.0
In another of my training videos, today I go over the Bitwise OR (|) operator and how it may be used in TSQL. (If you haven’t yet, please first visit the video on the Bitwise AND & operator before viewing this one). In the following I will demonstrate the operation, and then give you a simple example. While not something that you can use in your everyday programming, Bitmasking can handle problems that pop up from time-to-time. Explore this concept with me, and then come up with your own ways to incorporate this into your toolbox. I've been using this method for a number of years and think it's fascinating. Any questions on this just give me a shout via email. Thanks much, Lee Everest
e6710736-5566-4d44-9479-eff3402553d0|0|.0
In another of my training videos, today I go over the Bitwise AND (&) operator and how it may be used in TSQL. In the following I will demonstrate the operation, and then give you a simple example. While not something that you can use in your everyday programming, Bitmasking can handle problems that pop up from time-to-time. Explore this concept with me, and then come up with your own ways to incorporate this into your toolbox. I've been using this method for a number of years and think it's fascinating. Any questions on this just give me a shout via email. Good luck! Thanks much, Lee Everest --------------------- Bitwise AND __________________________________________________________________________________ Books Online - http://msdn.microsoft.com/en-us/library/ms174965.aspx Help on bitmasking - http://www.sqlservercentral.com/articles/Miscellaneous/2748/ Contents this blog - http://www.texastoo.com/page/Contents-of-this-Blog.aspx __________________________________________________________________________________
a4d5f467-1dec-4c4a-82ef-735f96e85741|0|.0
Wiring-up to Excel can be a pain. This might help. Here's a screencast that might assist you in connecting to Excel in Microsoft SQL Server Integration Services. Give it a try next time you have to go to an Excel data source. Lee ---------------------------- His name is Jason and today is his birthday. Flickr Tags: SSIS and excel missing data,excel data source in ssis missing data
Posted in:
SQL Server 2008 ,
Vids ,
SSIS Tags:
7e707190-ed7e-40df-b665-1bec9ca8a7f3|1|5.0
I was looking around the other day on Google! and elsewhere for info on SSIS transactions and MSDTC setup, but didn’t find much; actually what I found were a lot of questions around their use. Check out my screencast as I geek with them, and try to make heads or tails out of their use. Expand the embedded video and check it out!
Hopefully this will help someone down the road when setting up and configuring SSIS packages to use transactions.
Lee Everest, M.S. www.texastoo.com
------------------------
This dude chose not to watch the vid
Code: http://texastoo.com/images/ssistransactions.zip
Flickr Tags: SSIS transactions and MSDTC,MSDTC setup for SSIS,transactions in SSIS
86560c0b-ed2f-4654-948a-be39165d36cf|2|5.0
Slick new feature to make INSERT go faster.
Check out my screencast on the minimally logging using INSERT SELECT in SQL Server 2008. Click the “Fullscreen view” button at bottom right of applet to expand the video image. Code files, below.
Lee
Code: http://www.texastoo.com/images/MinLogging.zip
Flickr Tags: minimally logged insert sql server,bulk logged insert select statement
Posted in:
SQL Server 2008 ,
Vids Tags:
898a5d08-f541-4ad1-8711-381c1ad34932|0|.0