Abstract
This article demonstrates creating a SQL Server Integration Services package that imports a text file into SQL Server database table using a Script Task component.
Requirements
- Microsoft Visual Studio 2008
- SQL Server 2005 (or later editions)
- create_table_script.txt
- flat_src.txt
- selectSIFISO_Flat_File_Source_into_OLE_DB_Destination_Using_CSharp.txt
Article
We begin by launching Microsoft Visual Studio and creating a new Integration Services Project which is located under Business Intelligence Projects category.
After you have assigned a project name, proceed to click and drag the Script Task in the Control Flow pane of the package’s toolbox.
Right-click the script task and click on “Edit”
Under the Script Task Editor change the “ScriptLanguage” to “Microsoft Visual C# 2008”.
In Project Explorer, ensure the following references are added:
Microsoft.SqlServer.Dts.Design;
Microsoft.SqlServer.DTSPipelineWrap;
Microsoft.SQLServer.DTSRuntimeWrap;
Microsoft.SqlServer.ManagedDTS;
Microsoft.SqlServer.ScriptTask;
System;
System.AddIn;
System.Data;
System.Windows.Forms;
System.Xml;
Back to the code window, ensure that the following namespaces are declared:
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Dts.Tasks;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
After the above declarations, proceed to create a new application instance:
Application selectSIFISO_app = new Application();
Create package:
Package sS_pkg = new Package();
Assign relevant package name and description:
sS_pkg.Name = “Load Flat File Source into OLE DB Destination Using C#”;
sS_pkg.Description = “Programmatically create an SSIS 2008 package that loads a Flat File Source into OLE DB Destination Using Script Task’s C# language”;
Insert the Data Flow Task with an appropriate name and some buffer space for processing of file (the last part is optional – you can also use default buffer allocation):
sS_pkg.Executables.Add(“STOCK:PipelineTask”);
TaskHost taskHost = sS_pkg.Executables[0] as TaskHost;
MainPipe dataFlowTask = (MainPipe)taskHost.InnerObject;
taskHost.Name = “Dynamic Data Flow Task”;
taskHost.Properties[“DefaultBufferMaxRows”].SetValue(taskHost, “1000000”);
Insert the Flat File connection:
ConnectionManager connectionManagerFlatFile = sS_pkg.Connections.Add(“FLATFILE”);
You can change this path depending on where you have stored the flat file (ensure you download the attached file, see “Requirements” section above):
connectionManagerFlatFile.ConnectionString = @”C:\Temp\flat_src.txt”;
Assign a name to the flat file connection:
connectionManagerFlatFile.Name = “TXT_FlatFile”;
Indicate that the flat file is delimited:
connectionManagerFlatFile.Properties[“Format”].SetValue(connectionManagerFlatFile, “Delimited”);
Indicate whether the source file has column headings or not – in this case, our sample data has column headings, hence – true:
connectionManagerFlatFile.Properties[“ColumnNamesInFirstDataRow”].SetValue(connectionManagerFlatFile, Convert.ToBoolean(true));
Get a native Flat File connection:
RuntimeWrapper.IDTSConnectionManagerFlatFile100 connectionFlatFile = connectionManagerFlatFile.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile100;
Declare the local string variable that will be used as part of reading the text file:
string line;
Determine the number of columns by reading the sample Flat File – line by line:
using (StreamReader file = new StreamReader(@"C:\Temp\flat_src.txt"))
{
try
{
while ((line = file.ReadLine()) != null)
{
char[] delimiters = new char[] { '|' };
string[] parts = line.Split(delimiters, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < parts.Length; i++)
{
RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 flatFileCol
= connectionFlatFile.Columns.Add() as RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100;
sS_AssignColumnProperties(flatFileCol, parts[i], "|");
}
//Exit file after reading the first line
break;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
file.Close();
}
}Edit the last Flat File column delimiter into NewLine instead of a Comma:
connectionFlatFile.Columns[connectionFlatFile.Columns.Count – 1].ColumnDelimiter = Environment.NewLine;
Insert Flat File source component:
IDTSComponentMetaData100 componentSource = dataFlowTask.ComponentMetaDataCollection.New();
componentSource.Name = “FlatFileSource”;
componentSource.ComponentClassID = “DTSAdapter.FlatFileSource”;
Insert source design-time instance and initialize component:
CManagedComponentWrapper instanceSource = componentSource.Instantiate();
instanceSource.ProvideComponentProperties();
Set source connection:
componentSource.RuntimeConnectionCollection[0].ConnectionManagerID = connectionManagerFlatFile.ID;
componentSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerFlatFile);
Reinitialize Flat File source metadata:
instanceSource.AcquireConnections(null);
instanceSource.ReinitializeMetaData();
instanceSource.ReleaseConnections();
Insert the SQL Server 2008 OLE-DB connection:
ConnectionManager connectionManagerOleDb = sS_pkg.Connections.Add(“OLEDB”);
connectionManagerOleDb.ConnectionString = string.Format(“Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;”, “localhost”, “AdventureWorks”);
connectionManagerOleDb.Name = “OLEDB”;
connectionManagerOleDb.Description = “OLEDB Connection”;
Insert OLE-DB destination:
IDTSComponentMetaData100 componentDestination = dataFlowTask.ComponentMetaDataCollection.New();
componentDestination.Name = “OLEDBDestination”;
componentDestination.Description = “OLEDB Destination for the Flat File data load”;
componentDestination.ComponentClassID = “DTSAdapter.OLEDBDestination”;
Insert destination design-time instance and initialize component:
CManagedComponentWrapper instanceDestination = componentDestination.Instantiate();
instanceDestination.ProvideComponentProperties();
Set destination connection:
componentDestination.RuntimeConnectionCollection[0].ConnectionManagerID = connectionManagerOleDb.ID;
componentDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerOleDb);
Indicates the name of the database object used to open a rowset:
instanceDestination.SetComponentProperty(“OpenRowset”, “[dbo].[sS_flatfileLoad]”);
Specifies the mode used to open the database:
instanceDestination.SetComponentProperty(“AccessMode”, 3);
Specifies options to be used with the fast load. Applies only if fast load is turned on:
instanceDestination.SetComponentProperty(“FastLoadOptions”, “TABLOCK,CHECK_CONSTRAINTS”);
Indicates whether the values supplied for identity columns will be copied to the destination or not
In this case, we have set this property to false:
instanceDestination.SetComponentProperty(“FastLoadKeepIdentity”, false);
Indicates whether the columns containing null will have null inserted in the destination or not
In this case, we have opted not to insert nulls:
instanceDestination.SetComponentProperty(“FastLoadKeepNulls”, false);
Specifies the column code page to use when code page information is unavailable from the data source
In this case, we used the default – 1252:
instanceDestination.SetComponentProperty(“DefaultCodePage”, 1252);
Specifies when commits are issued during data insertion
In this case, we have opted for the default size which is set to 2147483647:
instanceDestination.SetComponentProperty(“FastLoadMaxInsertCommitSize”, 2147483647);
Indicates the number of seconds before a command times out
In this case, we have opted for the default value of 0 which indicates an infinite time-out:
instanceDestination.SetComponentProperty(“CommandTimeout”, 0);
Indicates the usage of DefaultCodePage property value when describing the character data
In this case, we have opted for the default value of false:
instanceDestination.SetComponentProperty(“AlwaysUseDefaultCodePage”, false);
Connect the Flat File source to the OLE DB Destination component:
dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(componentSource.OutputCollection[0]
,componentDestination.InputCollection[0]);
Get input and virtual input for the destination to select and map columns:
IDTSInput100 destinationInput = componentDestination.InputCollection[0];
IDTSVirtualInput100 destinationVirtualInput = destinationInput.GetVirtualInput();
IDTSVirtualInputColumnCollection100 destinationVirtualInputColumns = destinationVirtualInput.VirtualInputColumnCollection;
Reinitialize the metadata, generating external columns from flat file columns:
instanceDestination.AcquireConnections(null);
instanceDestination.ReinitializeMetaData();
instanceDestination.ReleaseConnections();
Select and map destination columns:
foreach (IDTSVirtualInputColumn100 virtualInputColumn in destinationVirtualInputColumns)
{
// Select column, and retain new input column
IDTSInputColumn100 inputColumn = instanceDestination.SetUsageType(destinationInput.ID,destinationVirtualInput, virtualInputColumn.LineageID, DTSUsageType.UT_READONLY);
// Find external column by name
IDTSExternalMetadataColumn100 externalColumn = destinationInput.ExternalMetadataColumnCollection[inputColumn.Name];
// Map input column to external column
instanceDestination.MapInputColumn(destinationInput.ID, inputColumn.ID, externalColumn.ID);
}
Execute the package or disable the below code if you intend to run the package later:
sS_pkg.Execute();
Finally, save the package – in this case, we have opted to save the package into the file system:
selectSIFISO_app.SaveToXml(@”E:\newArticle.dtsx”, sS_pkg, null);
Dts.TaskResult = (int)ScriptResults.Success;
}
In addition to the above code, you will notice that some part of the code references the below function. This function is used to assign DTS column properties:
private static void sS_AssignColumnProperties(RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 flatFileCol, string getColName, string getDelim)
{
Assign delimiter:
flatFileCol.ColumnType = “Delimited”;
flatFileCol.ColumnDelimiter = getDelim;
Indicate column data type – in this case, all the source columns will be set to String Data Type:
flatFileCol.DataType = RuntimeWrapper.DataType.DT_STR;
Indicate column width – in this case, the width of all source columns will be set to a length of 100:
flatFileCol.ColumnWidth = 100;
Assign column name:
RuntimeWrapper.IDTSName100 columnName = flatFileCol as RuntimeWrapper.IDTSName100;
columnName.Name = getColName.ToString();
}
Conclusion
It’s that simple!
You can now execute your script task and the package will be created in the location you specified.
For the complete C# solution to this article, see ” Requirements ” section for a file name called “selectSIFISO_Flat_File_Source_into_OLE_DB_Destination_Using_CSharp.txt”.
Thanks.
Sifiso.
0 Comments