I enjoy some great sessions and I miss the session by Chad about sql replication which I did really wanted to see. We only have around 45 minutes during presentations which is not a lot of time,but, is better than nothing :-).
Finally here is the script code for the dynamic ssis, my session was about files, however, I spend too much time on the details of a proper ETL work flow methodology.
Script to Create an SSIS Package to load a File into a Table.
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
namespace ST_94a9f0913c0a487aa85d3139c2ca8c59.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
//Declare new aplication
Application sqlSaturday168_app = new Application();
//Create package
Package ssisFile2Table = new Package();
String PackageStorage = @"C:\\temp\\SQLSaturday168\\AutoPackage";
try
{
//User::strDestDBName,User::strDestinationServer,User::strDestSchema,User::strDestTable,User::strFileDelimited,User::strFileDirectory,User::strFileName
String DatabaseName = Dts.Variables["User::strDestDBName"].Value.ToString();
String DatabaseSchema = Dts.Variables["User::strDestSchema"].Value.ToString();
String TableName = DatabaseSchema+"."+Dts.Variables["User::strDestTable"].Value.ToString();
String FileDelimited = Dts.Variables["User::strFileDelimited"].Value.ToString();
String FileDirectory = Dts.Variables["User::strFileDirectory"].Value.ToString();
String FileName = Dts.Variables["User::strFileName"].Value.ToString();
String Servername = Dts.Variables["User::strDestinationServer"].Value.ToString();
String FullFilePath = FileDirectory + "\\" + FileName;
//Assign relevant package name and description
ssisFile2Table.Name = "SQLSaturday168 Demo Using C#";
ssisFile2Table.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 appropriate name and some buffer space for processing of file
ssisFile2Table.Executables.Add("STOCK:PipelineTask");
TaskHost taskHost = ssisFile2Table.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 = ssisFile2Table.Connections.Add("FLATFILE");
//You can change this path depending on where you have stored the flat file
connectionManagerFlatFile.ConnectionString = FullFilePath;// Dts.Connections["FlatFile"].ConnectionString;
//Assign name to the flat file connection
connectionManagerFlatFile.Name = Dts.Variables["User::strDestTable"].Value.ToString();// Dts.Connections["FlatFile"].Name;
//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.
connectionManagerFlatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(connectionManagerFlatFile, Convert.ToBoolean(true));
//Get native Flat File connection
RuntimeWrapper.IDTSConnectionManagerFlatFile100 connectionFlatFile = connectionManagerFlatFile.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile100;
string line;
//Determine the number of columns by reading the sample Flat File - line by line.
using (StreamReader file = new StreamReader(FullFilePath))
{
try
{
while ((line = file.ReadLine()) != null)
{
char[] delimiters = FileDelimited.ToCharArray();
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.2";
//Insert source design-time instance and initialise component
CManagedComponentWrapper instanceSource = componentSource.Instantiate();
instanceSource.ProvideComponentProperties();
//Set source connection
componentSource.RuntimeConnectionCollection[0].ConnectionManagerID = connectionManagerFlatFile.ID;
componentSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connectionManagerFlatFile);
//Reinitialize Flat File source metadata,
instanceSource.AcquireConnections(null);
instanceSource.ReinitializeMetaData();
instanceSource.ReleaseConnections();
//Insert the SQL Server 2008 OLE-DB connection
String ConnectionString = "Data Source=" + Servername + ";Initial Catalog=" + DatabaseName + ";Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;";
ConnectionManager connectionManagerOleDb = ssisFile2Table.Connections.Add("OLEDB");
connectionManagerOleDb.ConnectionString = string.Format(ConnectionString);
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.2";
//Insert destination design-time instance and initialise component
CManagedComponentWrapper instanceDestination = componentDestination.Instantiate();
instanceDestination.ProvideComponentProperties();
//Set destination connection
componentDestination.RuntimeConnectionCollection[0].ConnectionManagerID = connectionManagerOleDb.ID;
componentDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connectionManagerOleDb);
//Indicates the name of the database object used to open a rowset
instanceDestination.SetComponentProperty("OpenRowset", TableName);
//Specifies the mode used to open the database
instanceDestination.SetComponentProperty("AccessMode", 3);
//Specifies options to be used with 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 willhave null inserted in the destination or not
//In this case, we have opted no 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 destination to select and map columns
IDTSInput100 destinationInput = componentDestination.InputCollection[0];
IDTSVirtualInput100 destinationVirtualInput = destinationInput.GetVirtualInput();
IDTSVirtualInputColumnCollection100 destinationVirtualInputColumns = destinationVirtualInput.VirtualInputColumnCollection;
//Reinitialize the metadata, generating exernal 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 running the package later
// ssisFile2Table.Execute();
//Finally, save the package - in this case, we have opted to save the package into file system
sqlSaturday168_app.SaveToXml(PackageStorage+"\\"+Dts.Variables["User::strDestTable"].Value.ToString()+".dtsx", ssisFile2Table, null);
Dts.TaskResult = (int)ScriptResults.Success;
}
catch
{
sqlSaturday168_app.SaveToXml(PackageStorage+"\\ERROR_"+Dts.Variables["User::strDestTable"].Value.ToString()+".dtsx", ssisFile2Table, null);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
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, 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();
}
}
}
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
namespace ST_94a9f0913c0a487aa85d3139c2ca8c59.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
//Declare new aplication
Application sqlSaturday168_app = new Application();
//Create package
Package ssisFile2Table = new Package();
String PackageStorage = @"C:\\temp\\SQLSaturday168\\AutoPackage";
try
{
//User::strDestDBName,User::strDestinationServer,User::strDestSchema,User::strDestTable,User::strFileDelimited,User::strFileDirectory,User::strFileName
String DatabaseName = Dts.Variables["User::strDestDBName"].Value.ToString();
String DatabaseSchema = Dts.Variables["User::strDestSchema"].Value.ToString();
String TableName = DatabaseSchema+"."+Dts.Variables["User::strDestTable"].Value.ToString();
String FileDelimited = Dts.Variables["User::strFileDelimited"].Value.ToString();
String FileDirectory = Dts.Variables["User::strFileDirectory"].Value.ToString();
String FileName = Dts.Variables["User::strFileName"].Value.ToString();
String Servername = Dts.Variables["User::strDestinationServer"].Value.ToString();
String FullFilePath = FileDirectory + "\\" + FileName;
//Assign relevant package name and description
ssisFile2Table.Name = "SQLSaturday168 Demo Using C#";
ssisFile2Table.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 appropriate name and some buffer space for processing of file
ssisFile2Table.Executables.Add("STOCK:PipelineTask");
TaskHost taskHost = ssisFile2Table.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 = ssisFile2Table.Connections.Add("FLATFILE");
//You can change this path depending on where you have stored the flat file
connectionManagerFlatFile.ConnectionString = FullFilePath;// Dts.Connections["FlatFile"].ConnectionString;
//Assign name to the flat file connection
connectionManagerFlatFile.Name = Dts.Variables["User::strDestTable"].Value.ToString();// Dts.Connections["FlatFile"].Name;
//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.
connectionManagerFlatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(connectionManagerFlatFile, Convert.ToBoolean(true));
//Get native Flat File connection
RuntimeWrapper.IDTSConnectionManagerFlatFile100 connectionFlatFile = connectionManagerFlatFile.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile100;
string line;
//Determine the number of columns by reading the sample Flat File - line by line.
using (StreamReader file = new StreamReader(FullFilePath))
{
try
{
while ((line = file.ReadLine()) != null)
{
char[] delimiters = FileDelimited.ToCharArray();
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.2";
//Insert source design-time instance and initialise component
CManagedComponentWrapper instanceSource = componentSource.Instantiate();
instanceSource.ProvideComponentProperties();
//Set source connection
componentSource.RuntimeConnectionCollection[0].ConnectionManagerID = connectionManagerFlatFile.ID;
componentSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connectionManagerFlatFile);
//Reinitialize Flat File source metadata,
instanceSource.AcquireConnections(null);
instanceSource.ReinitializeMetaData();
instanceSource.ReleaseConnections();
//Insert the SQL Server 2008 OLE-DB connection
String ConnectionString = "Data Source=" + Servername + ";Initial Catalog=" + DatabaseName + ";Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;";
ConnectionManager connectionManagerOleDb = ssisFile2Table.Connections.Add("OLEDB");
connectionManagerOleDb.ConnectionString = string.Format(ConnectionString);
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.2";
//Insert destination design-time instance and initialise component
CManagedComponentWrapper instanceDestination = componentDestination.Instantiate();
instanceDestination.ProvideComponentProperties();
//Set destination connection
componentDestination.RuntimeConnectionCollection[0].ConnectionManagerID = connectionManagerOleDb.ID;
componentDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connectionManagerOleDb);
//Indicates the name of the database object used to open a rowset
instanceDestination.SetComponentProperty("OpenRowset", TableName);
//Specifies the mode used to open the database
instanceDestination.SetComponentProperty("AccessMode", 3);
//Specifies options to be used with 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 willhave null inserted in the destination or not
//In this case, we have opted no 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 destination to select and map columns
IDTSInput100 destinationInput = componentDestination.InputCollection[0];
IDTSVirtualInput100 destinationVirtualInput = destinationInput.GetVirtualInput();
IDTSVirtualInputColumnCollection100 destinationVirtualInputColumns = destinationVirtualInput.VirtualInputColumnCollection;
//Reinitialize the metadata, generating exernal 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 running the package later
// ssisFile2Table.Execute();
//Finally, save the package - in this case, we have opted to save the package into file system
sqlSaturday168_app.SaveToXml(PackageStorage+"\\"+Dts.Variables["User::strDestTable"].Value.ToString()+".dtsx", ssisFile2Table, null);
Dts.TaskResult = (int)ScriptResults.Success;
}
catch
{
sqlSaturday168_app.SaveToXml(PackageStorage+"\\ERROR_"+Dts.Variables["User::strDestTable"].Value.ToString()+".dtsx", ssisFile2Table, null);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
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, 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();
}
}
}
No comments:
Post a Comment