Archive for the ‘SSIS’ Category
Two-character month and day in Expression Builder
I needed to set a variable to YYYYMMDD.
When using the Month() and Day() function in Expression Builder, it returned a single character back when the value was less than 10. So the value I was getting back was YYYYMD using the following expression:
(DT_WSTR, 4) YEAR( GETDATE() ) + (DT_WSTR,2)DatePart("m", getdate()) + (DT_WSTR,2)DatePart("d", getdate())
The workaround is to use the RIGHT() function:
(DT_WSTR, 4) YEAR( GETDATE() ) + RIGHT("0" + (DT_WSTR,2)DatePart("m", getdate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("d", getdate()), 2)
The above will return the right format: YYYYMMDD.
SSIS 2008 Error – The Microsoft ACE.OLEDB.12.0 provider is not registered on the local machine
I recently upgraded one of my Data Import SSIS 2005 package to 2008. That package ran fine in VS2005. Visual Studio 2008 happily upgraded it to the latest version with the usual Provider upgrade warnings, Provider=SQLNCLI10.1; there was no particular mention of the above error.
The package contains a connection to a 2007 excel file, here is how it looks like:
package
So here is the error popup I got when I clicked the preview button, the Excel Connection Manager was failing to connect to the file:
Microsoft.ACE.OLEDB.12.0 is not registered
Solution:
Download the latest “2007 Office System Driver: Data Connectivity Components” from here and install it.
After the install, I could happily preview the data in the excel file.
Hope this helps.