September 16, 2015 at 2:38 am
Hi All,
I have a folder which has mutiple excel sheets with different formats. Can we dynamically create table in SQL server and load the excel data. If there is a way please do let me know.
Thanks
Dhana
September 16, 2015 at 9:20 am
Are there any similarities in the formats so that they can be tested and grouped into different processes?
For example if you have 20 spreadsheets.
-3 of them have the same 10 columns
-12 of them have the same 13 columns
-5 of them have the same 6 columns
Or, are they all completely random with respect to the other spreadsheets?
September 16, 2015 at 1:03 pm
dhanasekar.palani (9/16/2015)
Hi All,I have a folder which has mutiple excel sheets with different formats. Can we dynamically create table in SQL server and load the excel data. If there is a way please do let me know.
Thanks
Dhana
Not without some scripting.
SSIS cannot handle this out of the box.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 17, 2015 at 12:57 pm
I'm pretty sure you can do this with CozyRoc's Data Flow Plus task.
I've managed to make tasks that read webservices for their metadata, maintain tables locally and then synchronize data down into them
http://www.cozyroc.com/ssis/data-flow-task
September 17, 2015 at 1:05 pm
You could use the Import/Export Wizard to dynamically create the package to handle all the sheets in the workbook and create the tables.
What seems weird is that you mention different sheets in a folder. Are you talking about different files?
September 18, 2015 at 5:32 am
I assume you might want to deal with multiple tabs that have the same makeup but differ from others.
You can generate the ssis with the import export task but you'll still need a script task to filter
I'm currently working on something similar, first I got a for each loop to loop through all the excel files in a share folder (which is annoying since the package fails without a seed file on a location predetermined) and then I have a for each loop with an ADO.net to loop through the tabs of the excel file.
The first step is an script task that does the following:
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using System.IO;
namespace ST_07800ccc496c4462821923dbb764836a.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
string sheetName = Dts.Variables["User::SheetName"].Value.ToString();
string pattern = Dts.Variables["User::PatternToMatch"].Value.ToString();
Regex rgx = new Regex(pattern, RegexOptions.IgnoreCase);
Match match = Regex.Match(sheetName, pattern);
bool a_1 = Regex.IsMatch(sheetName, @"[0-9]");
if (match.Success)
{
if (a_1 == true)
{
Dts.Variables["User::ProcessTheSheet"].Value = true;
Dts.Variables["User::SheetName"].Value = sheetName.Replace("'", "");
}
else
{
Dts.Variables["User::ProcessTheSheet"].Value = false;
}
}
else
{
Dts.Variables["User::ProcessTheSheet"].Value = false;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
This is in C# by the way,this matches the sheetname which it gets for the ADO for each loop with what's in a pattern variable,it also matches if there numbers in the tabname, if the tabname matches the pattern & it contains numbers the ProcessTheSheet is set to true.
I then use a constraint on the flow from the script task so that it will only do the next step when ProcessTheSheet is true.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply