You should consider doing this using a script component, keep in mind that when in data flow task you cannot debug directly but you can use mbox snipped to check results. Also keep in mind that excel will always try to suppose your column data types automatically, for example when you try to import a file from excel that one of its columns starts with a number but in the row 3455 there's a character, it will import the column as a number and you will lose the char value, you will find it as null in your database.
I will give you some code to construct the file you need programmatically, maybe it can give you an idea. (This example reads a file as one column, then it will split in as if you chose fixed with delimited values in excel and will output in a csv file.
/* Microsoft SQL Server Integration Services Script Component
* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/
using System;
using System.IO;
using System.Linq;
using System.Text;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
#region Variables
private string _jumexDailyData;
private string[] _jumexValues;
private string[] _jumexWidthValues;
#endregion
/// <summary>
/// Default constructor
/// </summary>
public ScriptMain()
{
this._jumexValues = new string[22];
}
public override void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
}
public override void PostExecute()
{
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}
public override void JumexDailyData_ProcessInput(JumexDailyDataBuffer Buffer)
{
while (Buffer.NextRow())
JumexDailyData_ProcessInputRow(Buffer);
}
public override void JumexDailyData_ProcessInputRow(JumexDailyDataBuffer Row)
{
this._jumexDailyData = Row.JumexDailyData;
if (this._jumexDailyData != null)
{
this._jumexWidthValues = this.Variables.JUMEXLOADSALESATTACHMENTFILEWIDTHVALUES.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
if (this._jumexWidthValues != null && this._jumexWidthValues.Count() > 0)
for (int i = 0; i < this._jumexWidthValues.Count(); i++)
{
this._jumexValues[i] = this._jumexDailyData.Substring(0, int.Parse(this._jumexWidthValues[i])).Trim();
this._jumexDailyData = this._jumexDailyData.Substring(int.Parse(this._jumexWidthValues[i]), (this._jumexDailyData.Length - int.Parse(this._jumexWidthValues[i])));
}
if (string.IsNullOrEmpty(this._jumexValues[3].Trim()) == false &&
string.IsNullOrEmpty(this._jumexValues[17].Trim()) == false &&
!this._jumexValues[3].Contains("---") &&
!this._jumexValues[17].Contains("---") &&
!this._jumexValues[3].Trim().ToUpper().Contains("FACTURA") &&
!this._jumexValues[17].Trim().ToUpper().Contains("PEDIDO"))
using (StreamWriter streamWriter = new StreamWriter(this.Variables.JUMEXFULLQUALIFIEDLOADSALESATTACHMENTFILENAME.Replace(".TXT", ".CSV"), true, Encoding.Default))
{
streamWriter.WriteLine(string.Join("|", this._jumexValues));
}
}
}
}
