Archive for December, 2011

h1

Develop Packages Faster using BIxPress Package Templates: Creating a Template

December 31, 2011

The current project I am involved with has required me to develop packages as quickly as possible while also adhering design standards. The packages had a number of things in common with each other, including data sources, variables and components used to load the packages.  This is the perfect scenario for creating a template to speed up development of SSIS packages.  To create a template you can right click on the SSIS packages folder and select the BIxPress Package Builder Wizard

image

From the wizard specify the New Template from Package option. Give the template a meaningful name for example DimensionLoadTemplate.  Note that the templates are saved as .flow file.  Select the folder you want the template to be saved in.  A great tip I’ve picked up from the other consultants here at Pragmatic Works is to select a shared folder on the network to store any user created templates so that way any developer can have access to the templates.

image

On the next screen select the package you want to create the template from and then click next.

image

The next screen allows you to select which options for every  object in the package you want to set up as configurable by default. The list of configurable objects is on the left

image

Once you select an object from the object list the configurable properties pane is populated with all of the properties for that object.  Place a check mark next to the properties you want to be able to configure when using the template to create a new package. For the OLEDB Source Component I am choosing to make the Name of the component and the SQL Command default configurable.

image

Repeat this for every object you want to configure.  Once you are done with this selection process click next and then start.  Just like that you have now created a Template that you can use over and over again to speed up your SSIS development time significantly.  Try BIxPress yourself and tell me what you think.

Advertisements
h1

Document your SSIS Packages with BI Documenter Part5: Scheduling your Documentation

December 30, 2011

The last entry in this introductory series to BI Documenter will cover how you can automate the documentation process.  The automation works by taking the existing solutions that have been created and in conjunction with Windows Scheduled Tasks sets up the documentation to be run on a schedule that you define. So lets get to it.

First select the solution you want to schedule.

image

Next select the Schedule snapshot link from the tools menu on the right hand side of the screen.

image

This will launch a new window where we will build the command line to schedule the snapshot. There are several options as well as an explanation on how to add the command line to the  Window Scheduled tasks.

image

  1. Here you can again select the solution you would like to automate.
  2. Set the server name, username (if needed), password(if needed)
  3. The name of the metabase for the solution (the default is BIDocumenter)
  4. Select the output file type (CHM, or HTML)
  5. Set the Output file path
  6. Here you have the option to append a date time stamp to the end of the snapshot.
  7. You can choose to enable logging which will create a log for the creation of the snapshot and output to a file.
  8. For the executable you need to supply the file location for the BI Documenter executable file. The default location is supplied for you, but if you changed any of the defaults when installing the program you may have to navigate to that folder on your own.
  9. The Arguments section is the fully constructed command line that you will then use to schedule your job in the Windows Scheduled Tasks. You can also test the command line here which will run a complete snapshot of you solution from the command line.
  10. This section walks you through using the Windows Scheduled Tasks to schedule your BI Documenter Snapshots.

When I ran through this wizard myself I was able to leave the defaults for just about everything.  The only thing I changed was the timestamp for my output file.

Now lets walk through adding this to Windows Scheduled Tasks.

image

I’m using Windows7 so to find the Task Scheduler I had to go to the control panel and under administrative tools I found the Task Scheduler. Once there select create Basic Task.

image

This will open a new window where you will give the task a name and a description(optional)

image

After clicking next you are presented with a list of options for when to start the task.

image

Click next and set the frequency and day of the week(if you choose weekly) to run the task.

image

Next specify what the task is to do. Select Start a Program

image

On the next screen is where the BI Documenter wizard we ran through earlier will be a huge help.

image

In box 1 enter the location of the executable for BI Documenter, and if you remember the wizard helped us find that.

In box 2 we add the arguments, which the wizard build for us after we selected all of the options.

Click next and Finish.  Now navigate to the task inside the Task Scheduler Library, right click and select run. Below you can see the newly created document.

image

This might seem a little bit too daunting if you are new to using the Task Scheduler, but trust me if I can do this then just about anyone can.

I hope you found this series useful and you’ve had a chance to download and test drive BI Documenter.  Please feel free to leave any comments and let me know what you think of BI Documenter.

h1

Document your SSIS Packages with BI Documenter Part4: Object Lineage and Impact Analysis

December 28, 2011

Sorry for the long layoff since my last post in this series but the holiday season and client commitments demanded a lot of me.  I am back now and ready to continue to share with you all.

In some of the more complex SSIS packages it can be a challenge to identify what objects in your environment will either impact your SSIS package of be impacted by your SSIS package.  This is doubly true if you didn’t design the package your self.  BI Documenter has a feature called Impact Analysis/Object Lineage that helps you get a better sense of these objects.

Lets continue with the package we’ve been using throughout these tutorials.  Select the solution from the list.

image

Then click on the link to launch the Impact Analysis/Object Lineage wizard.

image

Choose the Packages Tab and then right click on the package you want to investigate. You’ll have two options at this point.  You can render it as a diagram or render it as a list.

image

If you select the first option the diagram will look something like this.

image

The diagram shows that the package accessing two different tables.  On top of that is clearly shows you which tables are providing the data for the package and which table is consuming the data.

The list gives a slightly different view of the tables but still just as useful.

image

It lists them by objects used and objects using the package. Once you have select the version that suits you, you can choose to save the image or print it.  For my money I would choose to save it as part of the solution and have it available in the complete document.  It can also be saved as a separate file if you choose.

I suggest you test out BI Documenter for you self and test this and any of the other features I’ve talked about in my last few blogs.

h1

SSIS Error Trying to insert Null Values

December 1, 2011

I was running into an issue today that was driving me a bit crazy.  I was trying to load a table that had about 35 columns, some set as NULL, others as NOT NULL.  On all of the NOT NULL columns there were default values set for them. So when I mapped all of the columns from the source to the destination, there were a few of the NOT NULL Columns that didn’t have a source, but since they had default values I wasn’t worried about them at all.  Now this was on a client machine, so I can’t show you exactly what happened, but here is an example I came up with.

It’s a simple name table from a flat file source.

image

I went and added the Sex column to the destination table, set it to not allow nulls and made the default value ‘F’.  In the source there is no column for Sex.  Now at this point I believed that I had left all other values at their default setting, and that was my mistake.  But before we get to that lets see what was happening to me and see if you can figure it out.

[OLEDB_DST Name List [55]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: “Microsoft SQL Server Native Client 10.0”  Hresult: 0x80004005  Description: “The statement has been terminated.”.
An OLE DB record is available.  Source: “Microsoft SQL Server Native Client 10.0”  Hresult: 0x80004005  Description: “Cannot insert the value NULL into column ‘Sex’, table ‘Demo.dbo.NameList’; column does not allow nulls. INSERT fails.”.
This made no sense to me at all.  I knew the column wasn’t allowing nulls, but I wasn’t mapping anything directly to that column and it had a default value set, so why were nulls still trying to ruin my day.  To the forums I went, and I found this topic http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/4554075c-5f68-4e59-9f7c-8d46c05e8ec6  and it a little reading but a reply by Hunchback hit the nail on the head.  Somehow, for reasons I can’t remember I checked the box next to keep nulls and that was causing all of my issues.

image

As soon as I unchecked that box, my problem was solved and the table loaded just fine.  I hope this helps somebody out there.  At least now I know I won’t take as long to correct that mistake the next time I make it.  I can’t promise my self I won’t do it again, because I am notorious, at least in my own mind, for accidentally checking boxes I didn’t mean to check.

h1

Document your SSIS Packages with BI Documenter Part3: Comparing Snapshots

December 1, 2011

In the last two posts we?ve discussed how to use BI Documenter to document your SSIS packages and reviewed the documents that is generated.  Another one of the very useful features of BI Documenter is the Snapshot Comparison. Let us suppose that you have scheduled your snapshots to run on a regular basis, and now you want to compare the different snapshots to view the changes that have been made to your document.  Doing this with BI Documenter is quick and easy.

First thing is to make sure you have 2 snapshots of the same object to compare.  I covered creating snapshots in the first blog of this series check it out here.  For this first comparison I actually didn?t change anything inside the package because I wanted to see what it looked like without any change.  To compare the snapshots launch BI Documenter and choose the appropriate solution.  From inside the solution click on the hyperlink “Compare Snapshots?

image

 

 

The new screen will pop up and you will tell the wizard which snapshots you want to compare and simply click the start button and your comparison will begin.

 

image

 

BI Documenter makes the comparison very easy to read.  As you can see above the color coding on the compared objects makes it easy to quickly identify which objects are New, Modified, have been deleted etc. . . Even though I didn?t change anything inside the package I still received a Modified indicator between the two snapshots on my StartTIme Variable.  Lets take a closer look at that.

image

 

The first thing item indicated as modified is the variable objects, and if we drill down into the variables we see that only one variable has been modified, the strStartTime variable.  We can drill down into the variable and see that the Variable value has been changed.  Lets take a look at why the variable value has been modified.  I didn?t change the expression at all, but the expression uses a system variable that captures the start date of the package, and since that is evaluated when the snapshot was taken the actual value was different for the two snapshots.

Now lets see what happens when I intentionally modify the package.  Lets delete one of the connection managers.

image

Just like that the new snapshot comparison makes it easy to differentiate the objects that have been changed or deleted from the ones that have gone untouched.  Go ahead and test out this feature by downloading the software here.  Tell me what you think of the software.  Be sure to check out my next blog entry when I review the impact analysis/object lineage features of BI Documenter.