You’re probably getting this error message in BIDS/Data Tools
Error 1 Validation error. Data Flow Task: Package1: The requested OLE DB provider Microsoft.Jet.OLEDB.12.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”. Package1.dtsx 0 0
The issue here is that your computer is 64 bit and both versions (32 bit and 64 bit) of Excel are installed. By installing Microsoft Access Database Engine 2010 Redistributable your issue should go away but this alone did not work for me.
What worked for me is to also install 2007 Office System Driver: Data Connectivity Components which corrected the Microsoft.Jet.OLEDB.12.0 issue.
File Type (extension) Extended Properties
Excel 97-2003 Workbook (.xls) “Excel 8.0”
Excel 2007 Workbook (.xlsx) “Excel 12.0 Xml”
Excel 2007 Macro-enabled workbook (.xlsm) “Excel 12.0 Macro”
Excel 2007 Non-XML binary workbook (.xlsb) “Excel 12.0”
I hope this helps solve your issue.
Image Posted on Updated on
Use Conditional Split if you need to separate the data from a source (Flat File, Excel, Table, etc.) into multiple destinations (Flat File, Excel, Table, etc.)
Step 1: Drop a Data Flow Task to the Control Flow
Step 2: Create 4 Flat File Connections. The first flat file is your source. You can use OLE, ODBC, etc.
This second flat file is for my all .txt data
This third flat file is for my all .cs data
This fourth flat file is for my all .aspx data
Step 3: Drop a Flat File Source into the Data Flow
Step 4: Double click on the Flat File Source and establish the connection by selecting your source file.
Step 5: Drop a Conditional Split into the Data Flow. Drag the arrow from the Flat File Source to the Conditional Split.
Step 6: Double click on the Conditional Split and create three conditions.
Step 7: Drop three Flat File Destination onto the Data Flow
Step 8: Drag the arrow from the Conditional Split onto one of the Flat File Destination. An Input Output Selection dialog will popup. Select one of the output.
Repeat this step for all Flat File Destination.
Step 9: Double click on the Flat File Destination and select the connection. Repeat this process to the other two Flat File Destination
Step 10: Run the SSIS package and now you’ll have all three files with specific file types in them.
In this example, I’m using Flat Files but you can use Excel, ODBC, CSV, etc. The process is the same– just the connection setup is different.
Image Posted on Updated on
When you need to read through a file directory and save all of the file names to a text file, follow these steps.
Step 1: Create a variable to hold the file name that the ForEach Loop reads
Step 2: Create a Flat File Connection
Step 3: Drop a “Foreach Loop Container” to the Control Flow
Step 4: Rename the “Name” text to something more meaningful
Step 5: In the Enumerator drop-down, select “Foreach File Enumerator” and pick the folder path. Note: If you check “Traverse subfolders”, the Foreach loop will go through all subfolders.
Step 6: We need to save our results to a variable if we want to know what files were scanned. Under the “Variable” drop-down, select “User::MyFileName”
Step 7: Drop a “Script Task” into the Foreach Loop Container.
Step 8: Double click on the “Script Task”. In the “ReadOnlyVariables”, select “User::MyFileName”. Click on “Edit Script”
Step 9: Add the following C# code
public void Main()
ConnectionManager cm = Dts.Connections[“Output”];
string content = Dts.Variables[“User::MyFileName”].Value.ToString();
string fileDirectory = cm.ConnectionString;
System.IO.File.AppendAllText(fileDirectory, content + “\r\n”);
Dts.TaskResult = (int)ScriptResults.Success;
Now run your SSIS and once completed, open up the Output.txt file to see the results.