Programmatically creating SSIS package

Hi guys,

I was intended to write a program that will create a SSIS package which will import data from a CSV file to the SQL server 2005. But I did not find any good example for this into the internet. I found some example which exports data from SQL server 2005 to CSV files. And following those examples I have tried to write my own. But I am facing some problem with that. What I am doing here is creating two connection manager objects, one for Flat file and another for OLEDB. And create a data flow task that has two data flow component, one for reading source and another for writing to destination. While debugging I can see that after invoking the ReinitializedMetaData() for the flat file source data flow component, there is not output column found. Why it is not fetching the output columns from the CSV file? And after that when it invokes the ReinitializedMetaData() for the destination data flow component it simply throws exception.

Can any body help me to get around this problem? Even can anyone give me any link where I can find some useful article to accomplish this goal?

I am giving my code here too.

I will appreciate any kind of suggestion on this.

Code snippet:

public void CreatePackage()

{

string executeSqlTask = typeof(ExecuteSQLTask).AssemblyQualifiedName;

Package pkg = new Package();

pkg.PackageType = DTSPackageType.DTSDesigner90;

ConnectionManager oledbConnectionManager = CreateOLEDBConnection(pkg);

ConnectionManager flatfileConnectionManager =

CreateFileConnection(pkg);

// creating the SQL Task for table creation

Executable sqlTaskExecutable = pkg.Executables.Add(executeSqlTask);

ExecuteSQLTask execSqlTask = (sqlTaskExecutable as Microsoft.SqlServer.Dts.Runtime.TaskHost).InnerObject as ExecuteSQLTask;

execSqlTask.Connection = oledbConnectionManager.Name;

execSqlTask.SqlStatementSource =

"CREATE TABLE [MYDATABASE].[dbo].[MYTABLE] \n ([NAME] NVARCHAR(50),[AGE] NVARCHAR(50),[GENDER] NVARCHAR(50)) \nGO";

// creating the Data flow task

Executable dataFlowExecutable = pkg.Executables.Add("DTS.Pipeline.1");

TaskHost pipeLineTaskHost = (TaskHost)dataFlowExecutable;

MainPipe dataFlowTask = (MainPipe)pipeLineTaskHost.InnerObject;

// Put a precedence constraint between the tasks.

PrecedenceConstraint pcTasks = pkg.PrecedenceConstraints.Add(sqlTaskExecutable, dataFlowExecutable);

pcTasks.Value = DTSExecResult.Success;

pcTasks.EvalOp = DTSPrecedenceEvalOp.Constraint;

// Now adding the data flow components

IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();

sourceDataFlowComponent.Name = "Source Data from Flat file";

// Here is the component class id for flat file source data

sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}";

CManagedComponentWrapper managedInstance = sourceDataFlowComponent.Instantiate();

managedInstance.ProvideComponentProperties();

sourceDataFlowComponent.

RuntimeConnectionCollection[0].ConnectionManagerID = flatfileConnectionManager.ID;

sourceDataFlowComponent.

RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(flatfileConnectionManager);

managedInstance.AcquireConnections(null);

managedInstance.ReinitializeMetaData();

managedInstance.ReleaseConnections();

// Get the destination's default input and virtual input.

IDTSOutput90 output = sourceDataFlowComponent.OutputCollection[0];

// Here I dont find any columns at all..why?

// Now adding the data flow components

IDTSComponentMetaData90 destinationDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();

destinationDataFlowComponent.Name =

"Destination Oledb compoenent";

// Here is the component class id for Oledvb data

destinationDataFlowComponent.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}";

CManagedComponentWrapper managedOleInstance = destinationDataFlowComponent.Instantiate();

managedOleInstance.ProvideComponentProperties();

destinationDataFlowComponent.

RuntimeConnectionCollection[0].ConnectionManagerID = oledbConnectionManager.ID;

destinationDataFlowComponent.

RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oledbConnectionManager);

// Set the custom properties.

managedOleInstance.SetComponentProperty("AccessMode", 2);

managedOleInstance.SetComponentProperty("OpenRowset", "[MYDATABASE].[dbo].[MYTABLE]");

managedOleInstance.AcquireConnections(null);

managedOleInstance.ReinitializeMetaData(); // Throws exception

managedOleInstance.ReleaseConnections();

// Create the path.

IDTSPath90 path = dataFlowTask.PathCollection.New(); path.AttachPathAndPropagateNotifications(sourceDataFlowComponent.OutputCollection[0],

destinationDataFlowComponent.InputCollection[0]);

// Get the destination's default input and virtual input.

IDTSInput90 input = destinationDataFlowComponent.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the virtual input column collection.

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

managedOleInstance.SetUsageType(

input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

DTSExecResult res = pkg.Execute();

}

public ConnectionManager CreateOLEDBConnection(Package p)

{

ConnectionManager ConMgr;

ConMgr = p.Connections.Add("OLEDB");

ConMgr.ConnectionString =

"Data Source=VSTS;Initial Catalog=MYDATABASE;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;";

ConMgr.Name = "SSIS Connection Manager for Oledb";

ConMgr.Description = "OLE DB connection to the Test database.";

return ConMgr;

}

public ConnectionManager CreateFileConnection(Package p)

{

ConnectionManager connMgr;

connMgr = p.Connections.Add("FLATFILE");

connMgr.ConnectionString = @."D:\MyCSVFile.csv";

connMgr.Name = "SSIS Connection Manager for Files";

connMgr.Description = "Flat File connection";

connMgr.Properties["Format"].SetValue(connMgr, "Delimited");

connMgr.Properties["HeaderRowDelimiter"].SetValue(connMgr, Environment.NewLine);

return connMgr;

}

And my CSV files is as follows

NAME, AGE, GENDER

Jon,52,MALE

Linda, 26, FEMALE

Thats all. Thanks.

As mentioned earlier, this CSV to OLEDB package generator is not far from working.

First, the AccessMode on the OLEDB destination is set to the constant 2, which the "Sql Command" constant. Change that back to 0 ("Table or View").

FYI, the OLEDB destination's Access Mode enumeration is undocumented, but nevertheless can be found in the following dll: Microsoft SQL Server\DTS\PipelineComponents\OleDbDest.dll using a program like "PE Explorer".

Enum AccessMode;

AM_OPENROWSET = 0
AM_OPENROWSET_VARIABLE = 1
AM_SQLCOMMAND = 2
AM_OPENROWSET_FASTLOAD = 3
AM_OPENROWSET_FASTLOAD_VARIABLE = 4

Next, the target table doesn't exist , yet ReinitializeMetadata() for the OLEDB destination component will by default attempt to retrieve the target table's meta data. The table isn't there, so an exception is thrown. Therefore, as part of the driving program, you may wish to create the table temporarily (this table create is separate from the create in the Execute SQL Task), so that there is meta-data to reinitialize.

That should eliminate exceptions, but it doesn't mean the package will validate or execute successfully, just that you could save it to xml.

Then, add source columns for your flat file. SSIS does not materialize them for you (at least as far as I know), so the user needs to do perform that programmatically.|||

Hello jaegd,

At last I made it work today . Your tips tremendously helped me. After implementing your suggestions I had to do very little coding to make it working. Thanks a lot .

Anyway, as I am creating the source columns programmatically for the Flat file connection, I had to read the columns from the CSV file using .net IO functionality. Is there any way to accomplish this through SSIS APIs?

Thank you very much again.

Regards

Moim

|||

Hello,

How did you create the metadata for the destination?

Thanks in advance.

Cheers,

kix

Programmatically creating SSIS package

Hi guys,

I was intended to write a program that will create a SSIS package which will import data from a CSV file to the SQL server 2005. But I did not find any good example for this into the internet. I found some example which exports data from SQL server 2005 to CSV files. And following those examples I have tried to write my own. But I am facing some problem with that. What I am doing here is creating two connection manager objects, one for Flat file and another for OLEDB. And create a data flow task that has two data flow component, one for reading source and another for writing to destination. While debugging I can see that after invoking the ReinitializedMetaData() for the flat file source data flow component, there is not output column found. Why it is not fetching the output columns from the CSV file? And after that when it invokes the ReinitializedMetaData() for the destination data flow component it simply throws exception.

Can any body help me to get around this problem? Even can anyone give me any link where I can find some useful article to accomplish this goal?

I am giving my code here too.

I will appreciate any kind of suggestion on this.

Code snippet:

public void CreatePackage()

{

string executeSqlTask = typeof(ExecuteSQLTask).AssemblyQualifiedName;

Package pkg = new Package();

pkg.PackageType = DTSPackageType.DTSDesigner90;

ConnectionManager oledbConnectionManager = CreateOLEDBConnection(pkg);

ConnectionManager flatfileConnectionManager =

CreateFileConnection(pkg);

// creating the SQL Task for table creation

Executable sqlTaskExecutable = pkg.Executables.Add(executeSqlTask);

ExecuteSQLTask execSqlTask = (sqlTaskExecutable as Microsoft.SqlServer.Dts.Runtime.TaskHost).InnerObject as ExecuteSQLTask;

execSqlTask.Connection = oledbConnectionManager.Name;

execSqlTask.SqlStatementSource =

"CREATE TABLE [MYDATABASE].[dbo].[MYTABLE] \n ([NAME] NVARCHAR(50),[AGE] NVARCHAR(50),[GENDER] NVARCHAR(50)) \nGO";

// creating the Data flow task

Executable dataFlowExecutable = pkg.Executables.Add("DTS.Pipeline.1");

TaskHost pipeLineTaskHost = (TaskHost)dataFlowExecutable;

MainPipe dataFlowTask = (MainPipe)pipeLineTaskHost.InnerObject;

// Put a precedence constraint between the tasks.

PrecedenceConstraint pcTasks = pkg.PrecedenceConstraints.Add(sqlTaskExecutable, dataFlowExecutable);

pcTasks.Value = DTSExecResult.Success;

pcTasks.EvalOp = DTSPrecedenceEvalOp.Constraint;

// Now adding the data flow components

IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();

sourceDataFlowComponent.Name = "Source Data from Flat file";

// Here is the component class id for flat file source data

sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}";

CManagedComponentWrapper managedInstance = sourceDataFlowComponent.Instantiate();

managedInstance.ProvideComponentProperties();

sourceDataFlowComponent.

RuntimeConnectionCollection[0].ConnectionManagerID = flatfileConnectionManager.ID;

sourceDataFlowComponent.

RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(flatfileConnectionManager);

managedInstance.AcquireConnections(null);

managedInstance.ReinitializeMetaData();

managedInstance.ReleaseConnections();

// Get the destination's default input and virtual input.

IDTSOutput90 output = sourceDataFlowComponent.OutputCollection[0];

// Here I dont find any columns at all..why?

// Now adding the data flow components

IDTSComponentMetaData90 destinationDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();

destinationDataFlowComponent.Name =

"Destination Oledb compoenent";

// Here is the component class id for Oledvb data

destinationDataFlowComponent.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}";

CManagedComponentWrapper managedOleInstance = destinationDataFlowComponent.Instantiate();

managedOleInstance.ProvideComponentProperties();

destinationDataFlowComponent.

RuntimeConnectionCollection[0].ConnectionManagerID = oledbConnectionManager.ID;

destinationDataFlowComponent.

RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oledbConnectionManager);

// Set the custom properties.

managedOleInstance.SetComponentProperty("AccessMode", 2);

managedOleInstance.SetComponentProperty("OpenRowset", "[MYDATABASE].[dbo].[MYTABLE]");

managedOleInstance.AcquireConnections(null);

managedOleInstance.ReinitializeMetaData(); // Throws exception

managedOleInstance.ReleaseConnections();

// Create the path.

IDTSPath90 path = dataFlowTask.PathCollection.New(); path.AttachPathAndPropagateNotifications(sourceDataFlowComponent.OutputCollection[0],

destinationDataFlowComponent.InputCollection[0]);

// Get the destination's default input and virtual input.

IDTSInput90 input = destinationDataFlowComponent.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the virtual input column collection.

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

managedOleInstance.SetUsageType(

input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

DTSExecResult res = pkg.Execute();

}

public ConnectionManager CreateOLEDBConnection(Package p)

{

ConnectionManager ConMgr;

ConMgr = p.Connections.Add("OLEDB");

ConMgr.ConnectionString =

"Data Source=VSTS;Initial Catalog=MYDATABASE;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;";

ConMgr.Name = "SSIS Connection Manager for Oledb";

ConMgr.Description = "OLE DB connection to the Test database.";

return ConMgr;

}

public ConnectionManager CreateFileConnection(Package p)

{

ConnectionManager connMgr;

connMgr = p.Connections.Add("FLATFILE");

connMgr.ConnectionString = @."D:\MyCSVFile.csv";

connMgr.Name = "SSIS Connection Manager for Files";

connMgr.Description = "Flat File connection";

connMgr.Properties["Format"].SetValue(connMgr, "Delimited");

connMgr.Properties["HeaderRowDelimiter"].SetValue(connMgr, Environment.NewLine);

return connMgr;

}

And my CSV files is as follows

NAME, AGE, GENDER

Jon,52,MALE

Linda, 26, FEMALE

Thats all. Thanks.

As mentioned earlier, this CSV to OLEDB package generator is not far from working.

First, the AccessMode on the OLEDB destination is set to the constant 2, which the "Sql Command" constant. Change that back to 0 ("Table or View").

FYI, the OLEDB destination's Access Mode enumeration is undocumented, but nevertheless can be found in the following dll: Microsoft SQL Server\DTS\PipelineComponents\OleDbDest.dll using a program like "PE Explorer".

Enum AccessMode;

AM_OPENROWSET = 0
AM_OPENROWSET_VARIABLE = 1
AM_SQLCOMMAND = 2
AM_OPENROWSET_FASTLOAD = 3
AM_OPENROWSET_FASTLOAD_VARIABLE = 4

Next, the target table doesn't exist , yet ReinitializeMetadata() for the OLEDB destination component will by default attempt to retrieve the target table's meta data. The table isn't there, so an exception is thrown. Therefore, as part of the driving program, you may wish to create the table temporarily (this table create is separate from the create in the Execute SQL Task), so that there is meta-data to reinitialize.

That should eliminate exceptions, but it doesn't mean the package will validate or execute successfully, just that you could save it to xml.

Then, add source columns for your flat file. SSIS does not materialize them for you (at least as far as I know), so the user needs to do perform that programmatically.

|||

Hello jaegd,

At last I made it work today . Your tips tremendously helped me. After implementing your suggestions I had to do very little coding to make it working. Thanks a lot .

Anyway, as I am creating the source columns programmatically for the Flat file connection, I had to read the columns from the CSV file using .net IO functionality. Is there any way to accomplish this through SSIS APIs?

Thank you very much again.

Regards

Moim

|||

Hello,

How did you create the metadata for the destination?

Thanks in advance.

Cheers,

kix

Programmatically creating .csv file

Hi there,

I got a user who is requesting a weekly report to be exported in csv (comma delimited) format. But this process will run weekly using schedule job and he wants the file to save to a certain directory on the network. Two part questions...

1. Is there a way to create a .csv file programmatically after runing the query?

2. How would I save the .csv file to a specified directory on the network?

TIA

You can use the BulkCopy (bcp) command-line utility to do this. You can schedule a job using SQLAgent that can run it periodically. See Books Online for more details on the utility and options.

Programmatically Created SSIS Package, CSV file to OLDDB (SQLSever 2005)

Hi everyone,

I wanted to thank everyone for posting a ton of valuable information in these forums. I also want to thank all the moderators that have been replying with really insightful help!

I am trying to programmatically create an SSIS package to take .CSV data and put it into a SQL Server 2005. I am assuming that this is pretty common scenario.

I have used many of the examples in this forum as well as heavily borrowing from this example http://www.codeproject.com/csharp/Digging_SSIS_object_model.asp written by Moim Hossain.

I can get my package to create and execute properly but no data is being written to the SQL Server table. This has puzzled me for the last 2 days!

I know the issue isnt with the server itself because I tested it by graphically creating a test SSIS package and it transfers the .CSV data to the table perfectly.

Would anyone know why this would happen? The Execution results are returning success but no data is written to the table!

Could anyone please provide insight as to what my issue may be?

Thanks in advance!

Code Snippet

using System;
using System.IO;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using PipeLineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;

namespace SumCodeApp
{
class SumCodeApp
{
// Variables.
private Package package;
private ConnectionManager flatFileConnectionManager;
private ConnectionManager destinationDatabaseConnectionManager;
private Executable dataFlowTask;
private List<String> srcColumns;

int file_count;
SqlConnection connection;

String folder_path;
String username;
String password;
String DB_server;
String catalog;

// Default Constructor.
public SumCodeApp()
{
}

// Constructor taking in user info.
public SumCodeApp(String folder_path, String username, String password,
String DB_server, String catalog)
{
this.folder_path = folder_path;
this.username = username;
this.password = password;
this.DB_server = DB_server;
this.catalog = catalog;
}

private void CreatePackage()
{
package = new Package();
package.CreationDate = DateTime.Now;
package.ProtectionLevel = DTSProtectionLevel.DontSaveSensitive;
package.Name = "SumCode Package";
package.Description = "Upload the SumCode files to the database";
package.DelayValidation = true;
package.PackageType = DTSPackageType.DTSDesigner90;
}

private void CreateFlatFileConnection()
{
String flatFileName = ".\01105.csv";
String flatFileMoniker = "FLATFILE";
flatFileConnectionManager = package.Connections.Add(flatFileMoniker);
flatFileConnectionManager.Name = "SSIS Connection Manager for Files";
flatFileConnectionManager.Description = String.Concat("SSIS Connection Manager");
flatFileConnectionManager.ConnectionString = flatFileName;

// Set some common properties of the connection manager object.
//flatFileConnectionManager.Properties["ColumnNamesInFirstRow"].SetValue(flatFileConnectionManager, false);
flatFileConnectionManager.Properties["Format"].SetValue(flatFileConnectionManager, "Delimited");
flatFileConnectionManager.Properties["TextQualifier"].SetValue(flatFileConnectionManager, "\"");
flatFileConnectionManager.Properties["RowDelimiter"].SetValue(flatFileConnectionManager, "\r\n");
flatFileConnectionManager.Properties["DataRowsToSkip"].SetValue(flatFileConnectionManager, 0);

// Create the source columns into the connection manager.
CreateSourceColumns();
}

private void CreateSourceColumns()
{
// Get the actual connection manager instance
RuntimeWrapper.IDTSConnectionManagerFlatFile90 flatFileConnection = flatFileConnectionManager.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile90;

RuntimeWrapper.IDTSConnectionManagerFlatFileColumn90 column;
RuntimeWrapper.IDTSName90 name;

// Fill the source column collection.
srcColumns = new List<String>();
srcColumns.Add("CreateDate");
srcColumns.Add("CorpID");
srcColumns.Add("SumCodeID");
srcColumns.Add("Priority");
srcColumns.Add("SumCodeAbv");
srcColumns.Add("SumCodeDesc");
srcColumns.Add("SumCodeGroupID");

foreach (String colName in srcColumns)
{
column = flatFileConnection.Columns.Add();
if (srcColumns.IndexOf(colName) == (srcColumns.Count - 1))
//column.ColumnDelimiter = "\r\n";
column.ColumnDelimiter = "{CR}{LF}";
else
//column.ColumnDelimiter = ",";
column.ColumnDelimiter = "Comma {,}";

name = (RuntimeWrapper.IDTSName90)column;
name.Name = colName;

column.TextQualified = true;
column.ColumnType = "Delimited";
column.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
column.ColumnWidth = 0;
column.MaximumWidth = 255;
column.DataPrecision = 0;
column.DataScale = 0;

}

}

private void CreateDestinationDatabaseConnection()
{
destinationDatabaseConnectionManager = package.Connections.Add("OLEDB");
destinationDatabaseConnectionManager.Name = "Destination Connection - SumCodeCorpGroup";
destinationDatabaseConnectionManager.Description = "Connection to the temporary table SumCodCorpGroup";
destinationDatabaseConnectionManager.ConnectionString = "Data Source=DIVWL-356KCB1;Initial Catalog=SumCode;Provider=SQLOLEDB;Persist Security Info=True;User ID=sum;Password=code";
}

public class Column
{
private String name;
private Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType dataType;
private int length;
private int precision;
private int scale;
private int codePage = 0;

public String Name
{
get { return name; }
set { name = value; }
}

public Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType DataType
{
get { return dataType; }
set { dataType = value; }
}

public int Length
{
get { return length; }
set { length = value; }
}

public int Precision
{
get { return precision; }
set { precision = value; }
}

public int Scale
{
get { return scale; }
set { scale = value; }
}

public int CodePage
{
get { return codePage; }
set { codePage = value; }
}
}

private Column GetTargetColumnInfo(string sourceColumnName)
{
Column cl = new Column();
if (sourceColumnName.Contains("CreateDate"))
{
cl.Name = "CreateDate";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (
sourceColumnName.Contains("CorpID"))
{
cl.Name = "CorpID";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeID"))
{
cl.Name = "SumCodeID";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("Priority"))
{
cl.Name = "Priority";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeAbv"))
{
cl.Name = "SumCodeAbv";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeDesc"))
{
cl.Name = "SumCodeDesc";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeGroupID"))
{
cl.Name = "SumCodeGroupID";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
return cl;
}

private void CreateDataFlowTask()
{
String dataFlowTaskMoniker = "DTS.Pipeline";
dataFlowTask = package.Executables.Add(dataFlowTaskMoniker);

}

public void ImportFile(String directory_path)
{
// Create the package.
CreatePackage();

// Create Flat File Source Connection.
CreateFlatFileConnection();

// Create Database Destination Connection.
CreateDestinationDatabaseConnection();

// Create DataFlowTask.
CreateDataFlowTask();

// Create the DataFlowTask
PipeLineWrapper.IDTSComponentMetaData90 sourceComponent = ((dataFlowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).ComponentMetaDataCollection.New();
sourceComponent.Name = "Source File Component";
sourceComponent.ComponentClassID = "DTSAdapter.FlatFileSource";

PipeLineWrapper.CManagedComponentWrapper managedFlatFileInstance = sourceComponent.Instantiate();
managedFlatFileInstance.ProvideComponentProperties();
sourceComponent.RuntimeConnectionCollection[0].ConnectionManagerID = flatFileConnectionManager.ID;
sourceComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(flatFileConnectionManager);

managedFlatFileInstance.AcquireConnections(null);
managedFlatFileInstance.ReinitializeMetaData();

Dictionary<String, int> outputColumnLineageIDs = new Dictionary<String, int>();
PipeLineWrapper.IDTSExternalMetadataColumn90 exOutColumn = null;

foreach (PipeLineWrapper.IDTSOutputColumn90 outColumn in sourceComponent.OutputCollection[0].OutputColumnCollection)
{
exOutColumn = sourceComponent.OutputCollection[0].ExternalMetadataColumnCollection[outColumn.Name];
managedFlatFileInstance.MapOutputColumn(sourceComponent.OutputCollection[0].ID, outColumn.ID, exOutColumn.ID, true);
outputColumnLineageIDs.Add(outColumn.Name, outColumn.ID);
}
managedFlatFileInstance.ReleaseConnections();

String a = sourceComponent.RuntimeConnectionCollection[0].Name.ToString();
String b = sourceComponent.OutputCollection[0].Name;
String c = sourceComponent.OutputCollection[0].Description;
String d = sourceComponent.OutputCollection[0].OutputColumnCollection.Count.ToString();

// Create DataFlowTask Destination Component.
PipeLineWrapper.IDTSComponentMetaData90 destinationComponent = ((dataFlowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).ComponentMetaDataCollection.New();
destinationComponent.Name = "OLEDB SQL Connection";
destinationComponent.ComponentClassID = "DTSAdapter.OLEDBDestination";

PipeLineWrapper.CManagedComponentWrapper managedOleInstance = destinationComponent.Instantiate();
managedOleInstance.ProvideComponentProperties();

// Create a path and attach the output of the source to the input of the destination.
PipeLineWrapper.IDTSPath90 path = ((dataFlowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).PathCollection.New();
path.AttachPathAndPropagateNotifications(sourceComponent.OutputCollection[0], destinationComponent.InputCollection[0]);

destinationComponent.RuntimeConnectionCollection[0].ConnectionManagerID = destinationDatabaseConnectionManager.ID;
destinationComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(destinationDatabaseConnectionManager);

managedOleInstance.SetComponentProperty("AccessMode", 0);
managedOleInstance.SetComponentProperty("OpenRowset", "[SumCode].[dbo].[SumCodeCorpGroup]");
managedOleInstance.SetComponentProperty("AlwaysUseDefaultCodePage", false);
managedOleInstance.SetComponentProperty("DefaultCodePage", 1252);
managedOleInstance.SetComponentProperty("FastLoadKeepIdentity", false); // Fast load
managedOleInstance.SetComponentProperty("FastLoadKeepNulls", false);
managedOleInstance.SetComponentProperty("FastLoadMaxInsertCommitSize", 0);
managedOleInstance.SetComponentProperty("FastLoadOptions","TABLOCK,CHECK_CONSTRAINTS");

managedOleInstance.AcquireConnections(null);
managedOleInstance.ReinitializeMetaData();

PipeLineWrapper.IDTSInput90 input = destinationComponent.InputCollection[0];
PipeLineWrapper.IDTSVirtualInput90 vInput = input.GetVirtualInput();

foreach (PipeLineWrapper.IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
//if (outputColumnLineageIDs.ContainsKey(vColumn.LineageID.ToString()))
//{
managedOleInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, Microsoft.SqlServer.Dts.Pipeline.Wrapper.DTSUsageType.UT_READONLY);
//}
}

List<String> tmp = new List<String>();
foreach(PipeLineWrapper.IDTSInputColumn90 inc in destinationComponent.InputCollection[0].InputColumnCollection)
{
tmp.Add(inc.Name);
}

PipeLineWrapper.IDTSExternalMetadataColumn90 exColumn;
foreach (PipeLineWrapper.IDTSInputColumn90 inColumn in destinationComponent.InputCollection[0].InputColumnCollection)
{
exColumn = destinationComponent.InputCollection[0].ExternalMetadataColumnCollection[inColumn.Name];
Column mappedColumn = GetTargetColumnInfo(exColumn.Name);
String destName = mappedColumn.Name;

exColumn.Name = destName;

managedOleInstance.MapInputColumn(destinationComponent.InputCollection[0].ID, inColumn.ID, exColumn.ID);
}

managedOleInstance.ReleaseConnections();

DTSExecResult result = package.Execute();
a = "0";
}

}
}

A good first step in troubleshooting these types of problems: Save the file out to a DTSX (using the Application.SaveToXml method, then open and run it in BIDS. That might give you a better idea what's going on.

|||

I include a save to file by default, but have it for Debug confiurations only -

Code Snippet

#if DEBUG

// Save package to disk, DEBUG only

new Application().SaveToXml(@."C:\Temp\" + package.Name, package, null);

#endif

You would also be better off using the overloaded Execute method that enables you to pass in IDTSEvents amongst other things. This way you can capture error events in your application, otherwise you have no idea of what has one wrong, which when deployed will be an issue I guess. The save to file trick is easier when developing the code as you get the power of the designer and debugger, rather than just events you capture.

program to insert the csv file to SQL server

Dear professional
I am writing a program to insert the csv file to SQL server.
However, the data source contains different charater like that:
ABC-001
ABC-002
BDE_001
BDE_002
have there is any SQL to convert the data as below:
ABC-001-->ABC001
ABC-002-->ABC002
BDE_001-->BDE001
BDE_002-->BDE002
ThanksBasically you need to concatenate. like
left(fieldname, 3) + right(fieldname,3)
Or you can use substring also
Amarnath
"Anton" wrote:
> Dear professional
> I am writing a program to insert the csv file to SQL server.
> However, the data source contains different charater like that:
> ABC-001
> ABC-002
> BDE_001
> BDE_002
> have there is any SQL to convert the data as below:
> ABC-001-->ABC001
> ABC-002-->ABC002
> BDE_001-->BDE001
> BDE_002-->BDE002
> Thanks
>
>|||"Amarnath" wrote:
> Basically you need to concatenate. like
> left(fieldname, 3) + right(fieldname,3)
> Or you can use substring also
> Amarnath
> "Anton" wrote:
> > Dear professional
> >
> > I am writing a program to insert the csv file to SQL server.
> > However, the data source contains different charater like that:
> >
> > ABC-001
> > ABC-002
> > BDE_001
> > BDE_002
> >
> > have there is any SQL to convert the data as below:
> >
> > ABC-001-->ABC001
> > ABC-002-->ABC002
> > BDE_001-->BDE001
> > BDE_002-->BDE002
> >
> > Thanks
> >
> >
> >
> >|||YOu can use Replace function. Replace '-' with ''