S
stopiamwarren
Guest
Hello there, I am trying to write an SSIS package that reads a pipe delimited file, that has no headers. All of my code works, except the part that writes the data into the table (the else statement). I don't know how to write different data types at the same time.
Any help would be huge!
Thanks!!!
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion
using System.IO;
using System.Data.SqlClient;
namespace ST_5222ae320a6f43978ea55b1821615f1c
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
try
{
//Declare Variables
string SourceFolderPath = Dts.Variables["User::SourceFolder"].Value.ToString();
string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
string ArchiveFolder = Dts.Variables["User::ArchiveFolder"].Value.ToString();
string ColumnsDataType = Dts.Variables["User::ColumnsDataType"].Value.ToString();
string SchemaName = Dts.Variables["User::SchemaName"].Value.ToString();
//string ColumnList = "";
//Reading file names one by one
string[] fileEntries = Directory.GetFiles(SourceFolderPath, "*" + FileExtension);
foreach (string fileName in fileEntries)
{
SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)
(Dts.Connections["DB_Conn_Research"].AcquireConnection(Dts.Transaction) as SqlConnection);
//Writing Data of File Into Table
string TableName = "";
int counter = 0;
string line;
string ColumnList = "";
//MessageBox.Show(fileName);
System.IO.StreamReader SourceFile =
new System.IO.StreamReader(fileName);
while ((line = SourceFile.ReadLine()) != null)
{
if (counter == 0)
{
ColumnList = "[Column 1],[Column 2],[Column 3],[Column 4],[Column 5],[Column 6],[Column 7],[Column 8],[Column 9],[Column 10],[Column 11],[Column 12],[Column 13],[Column 14],[Column 15],[Column 16],[Column 17],[Column 18],[Column 19],[Column 20],[Column 21],[Column 22],[Column 23],[Column 24],[Column 25],[Column 26],[Column 27],[Column 28],[Column 29],[Column 30],[Column 31],[Column 32],[Column 33],[Column 34],[Column 35],[Column 36],[Column 37],[Column 38],[Column 39],[Column 40],[Column 41],[Column 42],[Column 43],[Column 44],[Column 45],[Column 46],[Column 47],[Column 48],[Column 49],[Column 50],[Column 51],[Column 52],[Column 53],[Column 54],[Column 55],[Column 56],[Column 57],[Column 58],[Column 59],[Column 60],[Column 61],[Column 62],[Column 63],[Column 64],[Column 65],[Column 66],[Column 67],[Column 68],[Column 69],[Column 70],[Column 71],[Column 72],[Column 73],[Column 74],[Column 75],[Column 76],[Column 77],[Column 78],[Column 79],[Column 80],[Column 81],[Column 82],[Column 83],[Column 84],[Column 85],[Column 86],[Column 87],[Column 88],[Column 89],[Column 90],[Column 91],[Column 92],[Column 93],[Column 94],[Column 95],[Column 96],[Column 97],[Column 98],[Column 99],[Column 100],[Column 101],[Column 102],[Column 103],[Column 104],[Column 105],[Column 106],[Column 107],[Column 108],[Column 109],[Column 110],[Column 111],[Column 112],[Column 113],[Column 114],[Column 115],[Column 116],[Column 117],[Column 118],[Column 119],[Column 120],[Column 121],[Column 122],[Column 123],[Column 124],[Column 125],[Column 126],[Column 127],[Column 128],[Column 129],[Column 130],[Column 131],[Column 132],[Column 133],[Column 134],[Column 135],[Column 136],[Column 137],[Column 138],[Column 139],[Column 140],[Column 141],[Column 142],[Column 143],[Column 144],[Column 145],[Column 146],[Column 147],[Column 148],[Column 149],[Column 150],[Column 151],[Column 152],[Column 153],[Column 154],[Column 155],[Column 156],[Column 157],[Column 158],[Column 159],[Column 160],[Column 161],[Column 162],[Column 163],[Column 164],[Column 165],[Column 166],[Column 167],[Column 168],[Column 169],[Column 170],[Column 171],[Column 172],[Column 173],[Column 174],[Column 175],[Column 176],[Column 177],[Column 178],[Column 179],[Column 180],[Column 181],[Column 182],[Column 183],[Column 184],[Column 185],[Column 186],[Column 187],[Column 188],[Column 189],[Column 190],[Column 191]";
TableName = (((fileName.Replace(SourceFolderPath, "")).Replace(FileExtension, "")).Replace("\\", ""));
string CreateTableStatement = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[" + SchemaName + "].";
CreateTableStatement += "[" + TableName + "]')";
CreateTableStatement += " AND type in (N'U'))DROP TABLE [" + SchemaName + "].";
CreateTableStatement += "[" + TableName + "] Create Table " + SchemaName + ".[" + TableName + "]";
CreateTableStatement += "([Column 1] varchar(50),[Column 2] datetime,[Column 3] char(5),[Column 4] char(7),[Column 5] char(14),[Column 6] char(20),[Column 7] varchar(10),[Column 8] char(7),[Column 9] char(30),[Column 10] int,[Column 11] decimal(11, 2),[Column 12] decimal(13, 4),[Column 13] int,[Column 14] char(1),[Column 15] char(3),[Column 16] decimal(9, 2),[Column 17] char(5),[Column 18] char(18),[Column 19] varchar(6),[Column 20] char(2),[Column 21] decimal(3, 0),[Column 22] char(22),[Column 23] char(30),[Column 24] char(30),[Column 25] char(20),[Column 26] char(2),[Column 27] char(9),[Column 28] char(4),[Column 29] varchar(1),[Column 30] varchar(1),[Column 31] varchar(1),[Column 32] varchar(1),[Column 33] char(1),[Column 34] char(10),[Column 35] varchar(3),[Column 36] varchar(12),[Column 37] decimal(5, 0),[Column 38] varchar(20),[Column 39] varchar(50),[Column 40] varchar(155),[Column 41] varchar(50),[Column 42] varchar(50),[Column 43] varchar(50),[Column 44] varchar(50),[Column 45] varchar(50),[Column 46] varchar(50),[Column 47] varchar(50),[Column 48] varchar(50),[Column 49] varchar(250),[Column 50] varchar(150),[Column 51] varchar(50),[Column 52] varchar(50),[Column 53] varchar(50),[Column 54] varchar(50),[Column 55] varchar(150),[Column 56] varchar(50),[Column 57] varchar(50),[Column 58] varchar(50),[Column 59] varchar(50),[Column 60] varchar(50),[Column 61] varchar(50),[Column 62] varchar(50),[Column 63] varchar(150),[Column 64] varchar(50),[Column 65] varchar(50),[Column 66] varchar(50),[Column 67] varchar(50),[Column 68] varchar(150),[Column 69] varchar(50),[Column 70] varchar(50),[Column 71] varchar(50),[Column 72] varchar(50),[Column 73] varchar(50),[Column 74] varchar(50),[Column 75] varchar(50),[Column 76] varchar(250),[Column 77] varchar(500),[Column 78] varchar(50),[Column 79] varchar(50),[Column 80] varchar(50),[Column 81] varchar(50),[Column 82] varchar(50),[Column 83] varchar(8),[Column 84] varchar(5),[Column 85] varchar(3),[Column 86] varchar(5),[Column 87] varchar(5),[Column 88] varchar(22),[Column 89] varchar(30),[Column 90] varchar(30),[Column 91] varchar(20),[Column 92] varchar(2),[Column 93] varchar(9),[Column 94] varchar(10),[Column 95] varchar(10),[Column 96] varchar(20),[Column 97] datetime,[Column 98] varchar(3),[Column 99] varchar(20),[Column 100] char(30),[Column 101] char(10),[Column 102] int,[Column 103] varchar(1),[Column 104] decimal(11, 2),[Column 105] int,[Column 106] varchar(1),[Column 107] int,[Column 108] varchar(1),[Column 109] varchar(1),[Column 110] decimal(11, 3),[Column 111] char(5),[Column 112] varchar(50),[Column 113] varchar(12),[Column 114] varchar(6),[Column 115] varchar(25),[Column 116] char(30),[Column 117] varchar(16),[Column 118] varchar(10),[Column 119] varchar(11),[Column 120] varchar(12),[Column 121] varchar(1),[Column 122] decimal(5, 0),[Column 123] char(1),[Column 124] char(15),[Column 125] char(3),[Column 126] char(15),[Column 127] char(2),[Column 128] char(15),[Column 129] char(1),[Column 130] char(3),[Column 131] char(15),[Column 132] char(15),[Column 133] char(15),[Column 134] varchar(25),[Column 135] varchar(1),[Column 136] varchar(25),[Column 137] varchar(50),[Column 138] varchar(50),[Column 139] varchar(50),[Column 140] int,[Column 141] varchar(50),[Column 142] varchar(10),[Column 143] varchar(10),[Column 144] char(1),[Column 145] bigint,[Column 146] char(1),[Column 147] char(1),[Column 148] bigint,[Column 149] varchar(128),[Column 150] bigint,[Column 151] bigint,[Column 152] datetime,[Column 153] varchar(30),[Column 154] varchar(50),[Column 155] varchar(50),[Column 156] varchar(3),[Column 157] varchar(50),[Column 158] varchar(50),[Column 159] varchar(10),[Column 160] varchar(60),[Column 161] varchar(50),[Column 162] varchar(245),[Column 163] varchar(50),[Column 164] varchar(60),[Column 165] varchar(50),[Column 166] varchar(5),[Column 167] date,[Column 168] varchar(200),[Column 169] varchar(1),[Column 170] varchar(5),[Column 171] varchar(6),[Column 172] char(30),[Column 173] varchar(20),[Column 174] varchar(15),[Column 175] varchar(15),[Column 176] varchar(15),[Column 177] varchar(15),[Column 178] decimal(11, 2),[Column 179] varchar(1),[Column 180] varchar(1),[Column 181] decimal(11, 3),[Column 182] varchar(50),[Column 183] varchar(50),[Column 184] varchar(1),[Column 185] varchar(1),[Column 186] varchar(1),[Column 187] varchar(1),[Column 188] varchar(1),[Column 189] varchar(10),[Column 190] varchar(1),[Column 191] varchar(1))";
SqlCommand CreateTableCmd = new SqlCommand(CreateTableStatement, myADONETConnection);
CreateTableCmd.ExecuteNonQuery();
// MessageBox.Show(CreateTableStatement);
}
else
{
string query = "Insert into " + SchemaName + ".[" + TableName + "] (" + ColumnList + ") ";
query += "VALUES('" + line.Replace(FileDelimiter, "','") + "')";
// MessageBox.Show(query.ToString());
SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);
myCommand1.ExecuteNonQuery();
}
counter++;
}
SourceFile.Close();
//move the file to archive folder after adding datetime to it
File.Move(fileName, ArchiveFolder + "\\" + (fileName.Replace(SourceFolderPath, "")).Replace(FileExtension, "") + "_" + datetime + FileExtension);
Dts.TaskResult = (int)ScriptResults.Success;
}
}
catch (Exception exception)
{
// Create Log File for Errors
using (StreamWriter sw = File.CreateText(Dts.Variables["User::LogFolder"].Value.ToString()
+ "\\" + "ErrorLog_" + datetime + ".log"))
{
sw.WriteLine(exception.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
#region ScriptResults declaration
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
Continue reading...
Any help would be huge!
Thanks!!!
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion
using System.IO;
using System.Data.SqlClient;
namespace ST_5222ae320a6f43978ea55b1821615f1c
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
try
{
//Declare Variables
string SourceFolderPath = Dts.Variables["User::SourceFolder"].Value.ToString();
string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
string ArchiveFolder = Dts.Variables["User::ArchiveFolder"].Value.ToString();
string ColumnsDataType = Dts.Variables["User::ColumnsDataType"].Value.ToString();
string SchemaName = Dts.Variables["User::SchemaName"].Value.ToString();
//string ColumnList = "";
//Reading file names one by one
string[] fileEntries = Directory.GetFiles(SourceFolderPath, "*" + FileExtension);
foreach (string fileName in fileEntries)
{
SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)
(Dts.Connections["DB_Conn_Research"].AcquireConnection(Dts.Transaction) as SqlConnection);
//Writing Data of File Into Table
string TableName = "";
int counter = 0;
string line;
string ColumnList = "";
//MessageBox.Show(fileName);
System.IO.StreamReader SourceFile =
new System.IO.StreamReader(fileName);
while ((line = SourceFile.ReadLine()) != null)
{
if (counter == 0)
{
ColumnList = "[Column 1],[Column 2],[Column 3],[Column 4],[Column 5],[Column 6],[Column 7],[Column 8],[Column 9],[Column 10],[Column 11],[Column 12],[Column 13],[Column 14],[Column 15],[Column 16],[Column 17],[Column 18],[Column 19],[Column 20],[Column 21],[Column 22],[Column 23],[Column 24],[Column 25],[Column 26],[Column 27],[Column 28],[Column 29],[Column 30],[Column 31],[Column 32],[Column 33],[Column 34],[Column 35],[Column 36],[Column 37],[Column 38],[Column 39],[Column 40],[Column 41],[Column 42],[Column 43],[Column 44],[Column 45],[Column 46],[Column 47],[Column 48],[Column 49],[Column 50],[Column 51],[Column 52],[Column 53],[Column 54],[Column 55],[Column 56],[Column 57],[Column 58],[Column 59],[Column 60],[Column 61],[Column 62],[Column 63],[Column 64],[Column 65],[Column 66],[Column 67],[Column 68],[Column 69],[Column 70],[Column 71],[Column 72],[Column 73],[Column 74],[Column 75],[Column 76],[Column 77],[Column 78],[Column 79],[Column 80],[Column 81],[Column 82],[Column 83],[Column 84],[Column 85],[Column 86],[Column 87],[Column 88],[Column 89],[Column 90],[Column 91],[Column 92],[Column 93],[Column 94],[Column 95],[Column 96],[Column 97],[Column 98],[Column 99],[Column 100],[Column 101],[Column 102],[Column 103],[Column 104],[Column 105],[Column 106],[Column 107],[Column 108],[Column 109],[Column 110],[Column 111],[Column 112],[Column 113],[Column 114],[Column 115],[Column 116],[Column 117],[Column 118],[Column 119],[Column 120],[Column 121],[Column 122],[Column 123],[Column 124],[Column 125],[Column 126],[Column 127],[Column 128],[Column 129],[Column 130],[Column 131],[Column 132],[Column 133],[Column 134],[Column 135],[Column 136],[Column 137],[Column 138],[Column 139],[Column 140],[Column 141],[Column 142],[Column 143],[Column 144],[Column 145],[Column 146],[Column 147],[Column 148],[Column 149],[Column 150],[Column 151],[Column 152],[Column 153],[Column 154],[Column 155],[Column 156],[Column 157],[Column 158],[Column 159],[Column 160],[Column 161],[Column 162],[Column 163],[Column 164],[Column 165],[Column 166],[Column 167],[Column 168],[Column 169],[Column 170],[Column 171],[Column 172],[Column 173],[Column 174],[Column 175],[Column 176],[Column 177],[Column 178],[Column 179],[Column 180],[Column 181],[Column 182],[Column 183],[Column 184],[Column 185],[Column 186],[Column 187],[Column 188],[Column 189],[Column 190],[Column 191]";
TableName = (((fileName.Replace(SourceFolderPath, "")).Replace(FileExtension, "")).Replace("\\", ""));
string CreateTableStatement = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[" + SchemaName + "].";
CreateTableStatement += "[" + TableName + "]')";
CreateTableStatement += " AND type in (N'U'))DROP TABLE [" + SchemaName + "].";
CreateTableStatement += "[" + TableName + "] Create Table " + SchemaName + ".[" + TableName + "]";
CreateTableStatement += "([Column 1] varchar(50),[Column 2] datetime,[Column 3] char(5),[Column 4] char(7),[Column 5] char(14),[Column 6] char(20),[Column 7] varchar(10),[Column 8] char(7),[Column 9] char(30),[Column 10] int,[Column 11] decimal(11, 2),[Column 12] decimal(13, 4),[Column 13] int,[Column 14] char(1),[Column 15] char(3),[Column 16] decimal(9, 2),[Column 17] char(5),[Column 18] char(18),[Column 19] varchar(6),[Column 20] char(2),[Column 21] decimal(3, 0),[Column 22] char(22),[Column 23] char(30),[Column 24] char(30),[Column 25] char(20),[Column 26] char(2),[Column 27] char(9),[Column 28] char(4),[Column 29] varchar(1),[Column 30] varchar(1),[Column 31] varchar(1),[Column 32] varchar(1),[Column 33] char(1),[Column 34] char(10),[Column 35] varchar(3),[Column 36] varchar(12),[Column 37] decimal(5, 0),[Column 38] varchar(20),[Column 39] varchar(50),[Column 40] varchar(155),[Column 41] varchar(50),[Column 42] varchar(50),[Column 43] varchar(50),[Column 44] varchar(50),[Column 45] varchar(50),[Column 46] varchar(50),[Column 47] varchar(50),[Column 48] varchar(50),[Column 49] varchar(250),[Column 50] varchar(150),[Column 51] varchar(50),[Column 52] varchar(50),[Column 53] varchar(50),[Column 54] varchar(50),[Column 55] varchar(150),[Column 56] varchar(50),[Column 57] varchar(50),[Column 58] varchar(50),[Column 59] varchar(50),[Column 60] varchar(50),[Column 61] varchar(50),[Column 62] varchar(50),[Column 63] varchar(150),[Column 64] varchar(50),[Column 65] varchar(50),[Column 66] varchar(50),[Column 67] varchar(50),[Column 68] varchar(150),[Column 69] varchar(50),[Column 70] varchar(50),[Column 71] varchar(50),[Column 72] varchar(50),[Column 73] varchar(50),[Column 74] varchar(50),[Column 75] varchar(50),[Column 76] varchar(250),[Column 77] varchar(500),[Column 78] varchar(50),[Column 79] varchar(50),[Column 80] varchar(50),[Column 81] varchar(50),[Column 82] varchar(50),[Column 83] varchar(8),[Column 84] varchar(5),[Column 85] varchar(3),[Column 86] varchar(5),[Column 87] varchar(5),[Column 88] varchar(22),[Column 89] varchar(30),[Column 90] varchar(30),[Column 91] varchar(20),[Column 92] varchar(2),[Column 93] varchar(9),[Column 94] varchar(10),[Column 95] varchar(10),[Column 96] varchar(20),[Column 97] datetime,[Column 98] varchar(3),[Column 99] varchar(20),[Column 100] char(30),[Column 101] char(10),[Column 102] int,[Column 103] varchar(1),[Column 104] decimal(11, 2),[Column 105] int,[Column 106] varchar(1),[Column 107] int,[Column 108] varchar(1),[Column 109] varchar(1),[Column 110] decimal(11, 3),[Column 111] char(5),[Column 112] varchar(50),[Column 113] varchar(12),[Column 114] varchar(6),[Column 115] varchar(25),[Column 116] char(30),[Column 117] varchar(16),[Column 118] varchar(10),[Column 119] varchar(11),[Column 120] varchar(12),[Column 121] varchar(1),[Column 122] decimal(5, 0),[Column 123] char(1),[Column 124] char(15),[Column 125] char(3),[Column 126] char(15),[Column 127] char(2),[Column 128] char(15),[Column 129] char(1),[Column 130] char(3),[Column 131] char(15),[Column 132] char(15),[Column 133] char(15),[Column 134] varchar(25),[Column 135] varchar(1),[Column 136] varchar(25),[Column 137] varchar(50),[Column 138] varchar(50),[Column 139] varchar(50),[Column 140] int,[Column 141] varchar(50),[Column 142] varchar(10),[Column 143] varchar(10),[Column 144] char(1),[Column 145] bigint,[Column 146] char(1),[Column 147] char(1),[Column 148] bigint,[Column 149] varchar(128),[Column 150] bigint,[Column 151] bigint,[Column 152] datetime,[Column 153] varchar(30),[Column 154] varchar(50),[Column 155] varchar(50),[Column 156] varchar(3),[Column 157] varchar(50),[Column 158] varchar(50),[Column 159] varchar(10),[Column 160] varchar(60),[Column 161] varchar(50),[Column 162] varchar(245),[Column 163] varchar(50),[Column 164] varchar(60),[Column 165] varchar(50),[Column 166] varchar(5),[Column 167] date,[Column 168] varchar(200),[Column 169] varchar(1),[Column 170] varchar(5),[Column 171] varchar(6),[Column 172] char(30),[Column 173] varchar(20),[Column 174] varchar(15),[Column 175] varchar(15),[Column 176] varchar(15),[Column 177] varchar(15),[Column 178] decimal(11, 2),[Column 179] varchar(1),[Column 180] varchar(1),[Column 181] decimal(11, 3),[Column 182] varchar(50),[Column 183] varchar(50),[Column 184] varchar(1),[Column 185] varchar(1),[Column 186] varchar(1),[Column 187] varchar(1),[Column 188] varchar(1),[Column 189] varchar(10),[Column 190] varchar(1),[Column 191] varchar(1))";
SqlCommand CreateTableCmd = new SqlCommand(CreateTableStatement, myADONETConnection);
CreateTableCmd.ExecuteNonQuery();
// MessageBox.Show(CreateTableStatement);
}
else
{
string query = "Insert into " + SchemaName + ".[" + TableName + "] (" + ColumnList + ") ";
query += "VALUES('" + line.Replace(FileDelimiter, "','") + "')";
// MessageBox.Show(query.ToString());
SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);
myCommand1.ExecuteNonQuery();
}
counter++;
}
SourceFile.Close();
//move the file to archive folder after adding datetime to it
File.Move(fileName, ArchiveFolder + "\\" + (fileName.Replace(SourceFolderPath, "")).Replace(FileExtension, "") + "_" + datetime + FileExtension);
Dts.TaskResult = (int)ScriptResults.Success;
}
}
catch (Exception exception)
{
// Create Log File for Errors
using (StreamWriter sw = File.CreateText(Dts.Variables["User::LogFolder"].Value.ToString()
+ "\\" + "ErrorLog_" + datetime + ".log"))
{
sw.WriteLine(exception.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
#region ScriptResults declaration
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
Continue reading...