Before Microsoft changed Access from .mdb file extensions to .accdb, SSIS would work right out of the box connecting to Access databases. Due to the new format, you now need to download additional drivers on any machine that runs the SSIS jobs. I am going to show you how to do it right now.
Tag: ssis
SSIS Add Filename During File Load
When loading files into staging tables using SSIS, it is often helpful to include the filename during the load so you can verify where the data came from. This is easily accomplished with a buried option which in my opinion should be better spotlighted called the Advanced Editor for modifying flat file sources.
SSIS Derived Column Transformation
When you create an ETL process, sometimes the source data is not in the format that you need to load into the database. Below is an example of using a derived column to format the data so it fits into the database the way you require.
SSIS: Error running Webservice
I tried creating a webservice task within SSIS, and I kept getting errors. The provider said all of my connections were setup correctly, so they did not know what was wrong. The problem turned out to be something very simple.
SSIS Conditional Split Transformation
You may have a big file or a large database table that you want to split up into different csv files. Instead of writing multiple queries for the different cases, you can use Conditional Split within SSIS to accomplish this task. There are also a multitude of other scenarios you can use it for.
SSIS ORA-00900: invalid SQL statement calling Oracle Procedure
Calling an Oracle stored procedure in SSIS is a little different than calling a SQL Server version. Today I will show you the error you get if trying the SQL Server syntax and how to fix it.
Continue reading SSIS ORA-00900: invalid SQL statement calling Oracle Procedure
Load multiple files with SSIS Foreach Loop Container
You just finished your SSIS project where you needed to load a CSV file into a Sql Server database table, but now you just found out that multiple files with the same data will now need to be loaded. Using the Foreach Loop Container in SSIS makes this relatively easy.
Continue reading Load multiple files with SSIS Foreach Loop Container
SSIS: Cannot convert between unicode and non-unicode string data types during import
Here is an issue you may receive if you try to import data from an Excel spreadsheet into a Sql Server database table:
Error at Data Flow Task [OLE DB Destination [23]]: Columns “firstname” and “FirstName” cannot convert between unicode and non-unicode string data types.
SSIS Error Code: 0xC020801C
If you have ever tried to run or create a SSIS extract package that exports to Excel, you may run into the error below:
[SSIS.Pipeline] Error: Excel Destination failed validation and returned error code 0xC020801C.
SSIS Create extract file with Date and Time for a filename
In last weeks posting, we created our first SSIS package which queried a database and saved the information to a file so we could send the information to a user. This week we are going to modify that same package to save the file with the date and time included in the filename.
Continue reading SSIS Create extract file with Date and Time for a filename