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