select SIFISO

Abstract

This article is in response to a request that was sent by one of my blog’s subscribers regarding scripting an SSIS package with derived columns that sources text file and writes data into another text file. Thus, this article demonstrates creating a SQL Server Integration Services package that imports a text file with derived column transformation into flat file destination using a Script Task component.

Requirements

• Microsoft Visual Studio 2008
• SQL Server 2005 (or later editions)
• flat_src
• selectSIFISO_Flat_File_Source_into_Derived_then_FlatFile_Destination_Using_CSharp

Article

We begin by launching Microsoft Visual Studio and create 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 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 creating an instance of application:
Application selectSIFISO_app = new Application();

The next step is to create an SSIS package object:
Package sS_pkg = new Package();

Assign relevant package name and description:
sS_pkg.Name = “Load Flat File Source into Flat File Destination Using C#”;
sS_pkg.Description = “Programmatically create an SSIS 2008 package that loads a Flat File Source into Flat File Destination Using Script Task’s C# language”;

Insert the Data Flow Task with 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
connectionManagerFlatFile.ConnectionString = @”C:\Temp\flat_src.txt”;

Assign 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.
connectionManagerFlatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(connectionManagerFlatFile, Convert.ToBoolean(true));

Get native Flat File connection
RuntimeWrapper.IDTSConnectionManagerFlatFile100 connectionFlatFile = connectionManagerFlatFile.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile100;

Declare local variable that will be used in this demonstration:
string line;
int k =0; ;

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 initialise 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();

Derived Column
IDTSComponentMetaData100 componentDerivedTransform = dataFlowTask.ComponentMetaDataCollection.New();
componentDerivedTransform.Name = “Derived Col Transform”;
componentDerivedTransform.ComponentClassID = “DTSTransform.DerivedColumn”;
CManagedComponentWrapper DesignDerivedTransformColumns = componentDerivedTransform.Instantiate();
DesignDerivedTransformColumns.ProvideComponentProperties();

design time
componentDerivedTransform.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = false;
componentDerivedTransform.InputCollection[0].HasSideEffects = false;

Create the path from source to derived columns, further insert two derived columns; the first one displays a full name whilist the other derived column insert a date to record when the file/record was loaded.
IDTSPath100 SourceToDerivedPath = dataFlowTask.PathCollection.New();
SourceToDerivedPath.AttachPathAndPropagateNotifications(componentSource.OutputCollection[0], componentDerivedTransform.InputCollection[0]);

IDTSInput100 DerivedColumnInput = componentDerivedTransform.InputCollection[0];
IDTSVirtualInput100 DerivedColumnVirtualInput = DerivedColumnInput.GetVirtualInput();
IDTSVirtualInputColumnCollection100 DerivedColumnVirtualInputColumns = DerivedColumnVirtualInput.VirtualInputColumnCollection;

Added the below to validate input columns
foreach (IDTSVirtualInputColumn100 virtualInputColumnDT in DerivedColumnVirtualInputColumns)
{
// Select column, and retain new input column
IDTSInputColumn100 inputColumn = DesignDerivedTransformColumns.SetUsageType(DerivedColumnInput.ID, DerivedColumnVirtualInput, virtualInputColumnDT.LineageID, DTSUsageType.UT_READONLY);
}

Declare new StreamWriter object and create a text file that will be used for output – provided it does not exists.

StreamWriter SW;
if (!File.Exists(“E:\\MyTextFile.txt”))
{
//Create output text file
SW = File.CreateText(“E:\\MyTextFile.txt”);
//Insert column row seperated by pipe
SW.WriteLine(“EmployeeKey|FirstName|LastName|Title|BirthDate|HireDate|FullName|Loaddate”);
//Close the file
SW.Close();
}

Insert the Flat File Destination connection
ConnectionManager connectionManagerFlatFileDestionation = sS_pkg.Connections.Add(“FLATFILE”);

You can change this path depending on where you have stored the flat file
connectionManagerFlatFileDestionation.ConnectionString = “E:\\MyTextFile.txt”;

Assign name to the flat file connection
connectionManagerFlatFileDestionation.Name = “Sample_TXT_FlatFile_Des”;

Indicate that the flat file is delimited
connectionManagerFlatFileDestionation.Properties["Format"].SetValue(connectionManagerFlatFileDestionation, “Delimited”);

Indicate whether the source file has column headings or not – in this case, our sample data has column headings.
connectionManagerFlatFileDestionation.Properties["ColumnNamesInFirstDataRow"].SetValue(connectionManagerFlatFileDestionation, Convert.ToBoolean(true));

Get native Flat File connection
RuntimeWrapper.IDTSConnectionManagerFlatFile100 connectionFlatFileDestionation = connectionManagerFlatFileDestionation.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile100;

Determine the number of columns by reading the sample Flat File – line by line.
using (StreamReader file = new StreamReader(“E:\\MyTextFile.txt”))
{
try
{
while ((line = file.ReadLine()) != null)
{
char[] delimiters = new char[] { ‘|’ };
string[] parts = line.Split(delimiters, StringSplitOptions.RemoveEmptyEntries);

for (int j = 0; j < parts.Length; j++)
{
RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 flatFileCol =
connectionFlatFileDestionation.Columns.Add() as RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100;
sS_AssignColumnProperties(flatFileCol, parts[j], "|");
k++;
}
//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
connectionFlatFileDestionation.Columns[connectionFlatFileDestionation.Columns.Count - 1].ColumnDelimiter = Environment.NewLine;

Insert Flat File Destination component
IDTSComponentMetaData100 componentDestination = dataFlowTask.ComponentMetaDataCollection.New();
componentDestination.Name = “FlatFileDestination”;
componentDestination.ComponentClassID = “DTSAdapter.FlatFileDestination”;

Insert source design-time instance and initialise component
CManagedComponentWrapper instanceDestination = componentDestination.Instantiate();
instanceDestination.ProvideComponentProperties();

Set source connection
componentDestination.RuntimeConnectionCollection[0].ConnectionManagerID = connectionManagerFlatFileDestionation.ID;
componentDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerFlatFileDestionation);

Reinitialize Flat File source metadata,
instanceDestination.AcquireConnections(null);
instanceDestination.ReinitializeMetaData();
instanceDestination.ReleaseConnections();

Connect the Flat File source to the OLE DB Destination component
dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(componentDerivedTransform.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;

Declare local integer variable that will be used for looping and map transformed columns to destination columns

int new_i=0;
foreach (IDTSVirtualInputColumn100 virtualInputColumn in destinationVirtualInputColumns)
{
if (new_i < k)
{
// 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);

new_i++;
}
}

Reinitialize Derived Column Transformation component’s metadata
DesignDerivedTransformColumns.AcquireConnections(null);
DesignDerivedTransformColumns.ReinitializeMetaData();
DesignDerivedTransformColumns.ReleaseConnections();

Execute the package or disable the below code if you intend running the package later
sS_pkg.Execute();

Finally, save the package – in this case, we have opted to save the package into file system
selectSIFISO_app.SaveToXml(@”E:\newFFArticle.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 to 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, 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 location you specified.

For the complete C# solution to this article, see “Requirements” section for a file name called “selectSIFISO_Flat_File_Source_into_Derived_then_FlatFile_Destination_Using_CSharp.txt”.

Thanks.

Sifiso.

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 create 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 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 creating 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 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 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 native Flat File connection:
RuntimeWrapper.IDTSConnectionManagerFlatFile100 connectionFlatFile = connectionManagerFlatFile.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile100;

Declare 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 initialise 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 initialise 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 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:
sS_pkg.Execute();

Finally, save the package - in this case, we have opted to save the package into 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 to 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, 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 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.

Abstract

This article explains how to Programmatically download SQL Server Integration Services (SSIS) packages from an instance of SQL Server 2008 and save them into an XML file using a Microsoft’s SSIS Script Task component.

Requirements

• Microsoft Visual Studio 2008 (or later editions)
• SQL Server 2005 (or later editions)

Article

If the above requirements are all met, we will begin by launching Microsoft Visual Studio edition.

Create a new project Integration Services Project which is located under Business Intelligence Projects.

After you have named the new project, proceed to click and drag the script task in Control Flow pane of the new package.

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 import relevant references and ensure that you have declared namespaces as below:

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Dts.Tasks;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Collections.Generic;
using System.Collections.Specialized;

After declarations, create instances of application and SSIS package:

Application SIFISO_app = new Application();
Package p = new Package();

Create local variables that will be used store sql server instance name and database. (For the purposes of this discussion, the database name refers to msdb):

string set_server = “your_sql_server_instance_name”;
string config_database = “msdb”;

The below script will continue to demonstrate creating and establishing an OLE DB connection as below (please note: folderid – ’00000000-0000-0000-0000-000000000000′ – is the default root folder):

SqlConnection connectiont = new SqlConnection(
string.Format(“Data Source={0};Initial Catalog={1};Integrated Security=SSPI;”, set_server, config_database));
SqlCommand commandt = new SqlCommand(
“select * from msdb.dbo.sysssispackages where folderid =’00000000-0000-0000-0000-000000000000′”, connectiont);
connectiont.Open();

catch
{
commandt = new SqlCommand("insert into [SIFISO_TEST].[dbo].[not_copied_tables] values('" + ds.Tables[0].Rows[intCount].ItemArray[0].ToString() + "')", connectiont);
commandt.ExecuteNonQuery();
intCount++;
}
}
}

We then save the package into a file system.

Dts.TaskResult = (int)ScriptResults.Success;
SIFISO_app.SaveToXml(“C:\\TEMP\\pkg_ArchiveToXML_Csharp.dtsx”, p, null);

Conclusion

It’s that simple!

You can now execute your script task and your packages will be saved into the file system specified.

Abstract

This article explains how to Programmatically create an SQL Server Integration Services (SSIS) package with a Script Task component.

Requirements

• Microsoft Visual Studio 2008 (or later editions)
• SQL Server 2005 (or later editions)
• AdventureWorks2008 database (downloadable database file available here)

Article

If the above requirements are all met, we will begin by launching Microsoft Visual Studio edition.

Create a new project Integration Services Project which is located under Business Intelligence Projects.

After you have named the new project, proceed to click and drag the script task in Control Flow pane of the new package.

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 import relevant references and ensure that you have declared namespaces as below:

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

After declarations, create an instance of application, workbook, workshet and range:

public void Main()
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;

range = xlWorkSheet.UsedRange;

Establish a connection to your SQL Server instance:

for (rCnt = 2; rCnt {
for (cCnt = 1; cCnt 1)
{
if (str.Contains(“‘”))
{
INS_str = INS_str + “‘” + str.Replace(“‘”, “””) + “‘,”;
}
else
{
INS_str = INS_str + “‘” + str + “‘,”;
}
}
else
{
if (str.Contains(“‘”))
{
INS_str = “‘” + str.Replace(“‘”, “””) + “‘,”;
}
else
{
INS_str = “‘” + str + “‘,”;
}
}
}

Exception handling:

catch (Exception exc)
{
if (exc.Message.Contains(“”))
{
str2 = Convert.ToDouble((range.Cells[rCnt, cCnt] as Excel.Range).Value2);
str3 = Convert.ToString(str2);
}
}

Dts.TaskResult = (int)ScriptResults.Success;
SIFISO_app.SaveToXml(“C:\\TEMP\\pkg_Read_Excel_Into_Csharp.dtsx”, p, null);

Conclusion

It’s that simple!

You can now execute your script task and the data will be inserted into your destination table.