Category Archives: SSIS

Pragmatic Works Now Offering New Service Migrating From Informatica To SSIS

Today for the first time, Brian Knight, founder of Pragmatic Works, announced that Pragmatic Works will be offering a new service aimed at migrating their clients from Informatica to SSIS!

This is a very exciting offering for Pragmatic Works and especially those companies running Informatica and looking to break away from the expensive licensing model. Check out this video with Pragmatic Works experts Brian Knight and Devin Knight discuss some of the differences between Informatica and SSIS. Stay until the end of the clip to catch the announcement of the new service!

For more information on migrating from Informatica to SSIS, head to http://www.PragmaticWorks.com or call (904) 638-5743.

Create XML Files With SSIS

imageIf you’ve ever tried to write data to an XML file with SSIS you know there’s not a slick, easy way to do it. You could use a Script Component in a Data Flow Task, as illustrated by fellow Pragmatic Works employee, Jason Strate. But there is an easier way. In comes Task Factory with its new XML Generator Transform Data Flow component.

image

The Task Factory XML Transform component allows you to specify the XSD file, which contains the schema information for the XML data to be output. Once you’ve selected the XSD file, any column from the source data that should be included in the XML document needs to be mapped to a matching field from the XSD file.

image

Next, you need to specify the relationship between the parent and child elements. Then map the field to establish the relationship.

Send the data to a Flat File Destination pointed at an XML file and you’re in business. Download the free trial of Task Factory to check out the XML Generator Transform and the 36 other tasks and transforms.

Import Native SSIS Performance Data With BI xPress

imageOne of the most powerful features of BI xPress is the Auditing Framework, which allows you to apply a standardized and robust auditing framework to multiple packages in just a few clicks. I’ve blogged about the BI xPress Auditing Framework before because its an extremely impressive tool that has saved me and the teams I’ve worked with countless hours. But now that SQL Server 2012 has been released, we have a whole slew of execution and performance data available to us natively within the 2012 SSIS Catalog. Wouldn’t it be great if we could view the native execution and performance data within the BI xPress Monitoring Console even if the BI xPress Auditing Framework has not been applied?

Your prayers have been answered. If you’re running SSIS 2012, you can now easily import the native execution and performance data into the BI xPress database for even more in depth reporting on the execution of your SSIS packages. Of course, this feature only works if you’re running 2012 :).

To import the SSIS Catalog data into the BI xPress database, click the SSIS Catalog Import button located on the Pragmatic Workbench home screen.

image

Next specify the location of the BI xPress database that is your target for the import. Then add the SSIS Catalog as the source for the import.

image

imageAnd the really nice part is that you can turn on Auto Importing so that the native performance and execution data will be automatically imported into the BI xPress database!

Now that the data has been imported into the BI xPress database, any native SSIS 2012 package stored in the SSIS Catalog can now monitored and measured in the BI xPress Monitoring Console. Head over to PragmaticWorks.com for more information and a trial download.

Migrating From Informatica To SSIS?

image

If you’re even considering migrating your Informatica Workflows to SSIS packages, you’re going to need a clear understanding of the level of difficulty you’re going to be up against. That’s where Workflow Profiler comes in.

The Workflow Profiler allows you to gather a complete list of of repository objects at the most granular level with the goal of determining their reusability. The Workflow Profilers also identifies connections utilized within the Workflows. Once the workflows have been documented, detailed reports are generated detailing the objects contained in the Informatica workflows.

First, identify the repository connection.

Then select the Workflows you want to document.

Next, you can select the type of report you’d like to generate. You can also specify if you would like to filter out certain types of objects.

And you’re report is ready for viewing and exporting to Excel, Word, or PDF formats.

Head over to PragmaticWorks.com and download the free trial of Workflow Profiler. If you’re considering migrating your ETL to SSIS, this is a tool you don’t want to miss out on.

Notes From SQL Saturday 168: Introduction To SSIS 2012

Thank you to everyone who attended my session, “SSIS 2012 In Yo’ Face: Introduction to SSIS”! I had a great time presenting and it was a whole lot of fun. Also, a big thank you to my co-presenter, Mitchell Pearson (blog | twitter)!

Also, thanks to everyone who attended my second bonus session, “What’s New In SSIS 2012”.

For those of you interested, here is the package we created in “SSIS 2012 In Yo’ Face”. Simple place the content of the zip file in the C:\Projects\SQLSaturday168 folder and unzip the SSIS project to the directory of your choice. If you have any questions, please feel free to post them here or send me a message on Twitter (@SQLDusty).

Again, thanks to everyone who came to my sessions! I hope you enjoyed the sessions and maybe even learned a little something.

I’m Speaking at SQL Saturday #168: BI Edition!

imageSQL Saturday #168: BI Edition is coming up on November 17th in Tampa, Florida and yours truly will be presenting! This SQL Saturday event will be all Business Intelligence focused! All the sessions will either be ETL, analytics, or reporting related with a miscellaneous track focusing on professional development and DBA topics. Get registered ASAP for this awesome BI focused SQL Saturday!

I’ll be co-presenting with Mitchell Pearson (blog | twitter) on an introduction to SSIS 2012 called SSIS 2012 In Yo’ Face: Introduction to SSIS. We’re going to cover commonly used SSIS tasks and transforms, the SSIS expression language, and how to create powerfully dynamic packages.

So head over to the SQL Saturday #168 site and get registered. I look forward to seeing everyone in our session! It’s going to be a blast!

Generating Junk/Test Data For SSIS

Everyone once in a while I’ll run across a requirement for me to generate a bunch of junk data for the sole purpose of performance testing or benchmarking. There are plenty of ways that I could generate my test data, including clever TSQL scripts or a Data Flow Script Component as a source. And those methods work well, but are somewhat limited if I need to generate unicode string data for testing. But that’s where Task Factory comes in.

imageIncluded in the latest release of Task Factory is the Placeholder Source. The beauty of the Place Holder Source is that I can quickly and easily generate test data for my package.

Here you can see the editor for the Placeholder Source. On the Component Properties tab, I can specify how many rows of junk data I’d like to generate. The default is 10,000 rows, but I can set it to whatever I like. I’ve tested the source component with 100 million records before with no problem.

image

On the Input and Output Properties tab is where you specify which columns and data types you’d like to include in your junk data.

image

By default, the Placeholder Source creates 5 output columns of various data type, but you can add more fields of whatever data type you like. Just click Add Column and then select the data type you want. Here I’ve added an additional column and selected Integer as the data type.

All that’s left to do is wire up my package and click execute. Instantly tons of junk data is created for us to test or play with. If you’re interested in expanding your SSIS arsenal, head over to PragmaticWorks.com and download Task Factory. The best part is that Task Factory is free to download. A license is only required to run the Task Factory components remotely. So why not check it out?