· 6 years ago · Jun 16, 2019, 10:24 AM
1#region Namespaces
2using System;
3using System.Data;
4using Microsoft.SqlServer.Dts.Runtime;
5using System.Windows.Forms;
6using System.Data.SqlClient;
7using System.IO;
8//using CsvHelper.Configuration;
9#endregion
10
11namespace ST_7ce5ad6fbc104157b534f4eb484a4417
12{
13 /// <summary>
14 /// ScriptMain is the entry point class of the script. Do not change the name, attributes,
15 /// or parent of this class.
16 /// </summary>
17 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
18 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
19 {
20 #region Help: Using Integration Services variables and parameters in a script
21 /* To use a variable in this script, first ensure that the variable has been added to
22 * either the list contained in the ReadOnlyVariables property or the list contained in
23 * the ReadWriteVariables property of this script task, according to whether or not your
24 * code needs to write to the variable. To add the variable, save this script, close this instance of
25 * Visual Studio, and update the ReadOnlyVariables and
26 * ReadWriteVariables properties in the Script Transformation Editor window.
27 * To use a parameter in this script, follow the same steps. Parameters are always read-only.
28 *
29 * Example of reading from a variable:
30 * DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
31 *
32 * Example of writing to a variable:
33 * Dts.Variables["User::myStringVariable"].Value = "new value";
34 *
35 * Example of reading from a package parameter:
36 * int batchId = (int) Dts.Variables["$Package::batchId"].Value;
37 *
38 * Example of reading from a project parameter:
39 * int batchId = (int) Dts.Variables["$Project::batchId"].Value;
40 *
41 * Example of reading from a sensitive project parameter:
42 * int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
43 * */
44
45 #endregion
46
47 #region Help: Firing Integration Services events from a script
48 /* This script task can fire events for logging purposes.
49 *
50 * Example of firing an error event:
51 * Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
52 *
53 * Example of firing an information event:
54 * Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
55 *
56 * Example of firing a warning event:
57 * Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
58 * */
59 #endregion
60
61 #region Help: Using Integration Services connection managers in a script
62 /* Some types of connection managers can be used in this script task. See the topic
63 * "Working with Connection Managers Programatically" for details.
64 *
65 * Example of using an ADO.Net connection manager:
66 * object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
67 * SqlConnection myADONETConnection = (SqlConnection)rawConnection;
68 * //Use the connection in some code here, then release the connection
69 * Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
70 *
71 * Example of using a File connection manager
72 * object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
73 * string filePath = (string)rawConnection;
74 * //Use the connection in some code here, then release the connection
75 * Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
76 * */
77 #endregion
78
79
80 /////// <summary>
81 /////// This method is called when this script task executes in the control flow.
82 /////// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
83 /////// To open Help, press F1.
84 /////// </summary>
85 public void Main()
86 {
87 // TODO: Add your code here
88
89
90 string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
91 try
92 {
93
94 //Declare Variables
95 string SourceFolderPath = Dts.Variables["User::SourceFolder"].Value.ToString();
96 string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
97 string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
98 string ArchiveFolder = Dts.Variables["User::ArchiveFolder"].Value.ToString();
99 string ColumnsDataType = Dts.Variables["User::ColumnsDataType"].Value.ToString();
100 string SchemaName = Dts.Variables["User::SchemaName"].Value.ToString();
101
102 SqlConnection myADONETConnection = new SqlConnection();
103 myADONETConnection = (SqlConnection)
104 (Dts.Connections["moviesdb"].AcquireConnection(Dts.Transaction) as SqlConnection);
105
106 //Reading file names one by one
107 string[] fileEntries = Directory.GetFiles(SourceFolderPath, "*" + FileExtension);
108 foreach (string fileName in fileEntries)
109 {
110 //Writing Data of File Into Table
111 string TableName = "";
112 int counter = 0;
113 string line;
114 string ColumnList = "";
115 //MessageBox.Show(fileName);
116
117 System.IO.StreamReader SourceFile =
118 new System.IO.StreamReader(fileName);
119 while ((line = SourceFile.ReadLine()) != null)
120 {
121 if (counter == 0)
122 {
123 ColumnList = "[" + line.Replace(""", "").Replace(FileDelimiter, "],[") + "]";
124
125 //MessageBox.Show(ColumnList);
126
127 //"[" + line.Replace(FileDelimiter, "],[") + "]";
128
129 TableName = (((fileName.Replace(SourceFolderPath, "")).Replace(FileExtension, "")).Replace("\", ""));
130 string CreateTableStatement = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[" + SchemaName + "].";
131 CreateTableStatement += "[" + TableName + "]')";
132 CreateTableStatement += " AND type in (N'U'))DROP TABLE [" + SchemaName + "].";
133 CreateTableStatement += "[" + TableName + "] Create Table " + SchemaName + ".[" + TableName + "]";
134 CreateTableStatement += "([" + line.Replace(""", "").Replace(FileDelimiter, "] " + ColumnsDataType + ",[") + "] " + ColumnsDataType + ")";
135 SqlCommand CreateTableCmd = new SqlCommand(CreateTableStatement, myADONETConnection);
136 CreateTableCmd.ExecuteNonQuery();
137
138 //MessageBox.Show(CreateTableStatement);
139
140 }
141
142 else
143 {
144 string query = "Insert into " + SchemaName + ".[" + TableName + "] (" + ColumnList + ") ";
145 //query += "VALUES('" + line.Replace(FileDelimiter, "','").Replace(""", "") + "')";
146 //query += "VALUES('" + line.Replace(FileDelimiter, "','").Replace(""", "").Replace(""'"", "") + "')";
147
148 query += "VALUES('" + line.Replace("'", "").Replace(FileDelimiter, "','").Replace(""", "") + "')";
149
150
151 // MessageBox.Show(query.ToString());
152
153 SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);
154 myCommand1.ExecuteNonQuery();
155
156 }
157
158 counter++;
159 }
160
161 SourceFile.Close();
162 //move the file to archive folder after adding datetime to it
163 File.Move(fileName, ArchiveFolder + "\" + (fileName.Replace(SourceFolderPath, "")).Replace(FileExtension, "") + "_" + datetime + FileExtension);
164 Dts.TaskResult = (int)ScriptResults.Success;
165 }
166 }
167 catch (Exception exception)
168 {
169 // Create Log File for Errors
170 using (StreamWriter sw = File.CreateText(Dts.Variables["User::LogFolder"].Value.ToString()
171 + "\" + "ErrorLog_" + datetime + ".log"))
172 {
173 sw.WriteLine(exception.ToString());
174 Dts.TaskResult = (int)ScriptResults.Failure;
175 }
176
177 }
178
179 }
180 #region ScriptResults declaration
181 /// <summary>
182 /// This enum provides a convenient shorthand within the scope of this class for setting the
183 /// result of the script.
184 ///
185 /// This code was generated automatically.
186 /// </summary>
187 enum ScriptResults
188 {
189 Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
190 Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
191 };
192 #endregion
193
194}