Creating packages in code - OLE-DB Source to Flat File File Destination

by Darren Green 29 Jan 2009 18:02

This code sample programmatically creates a package with an OLE-DB Source and a Flat File Destination, and the resulting package exports data from SQL Server to a CSV file.

The finished package just has the one Data Flow Task shown below.

 SqlToFlatFile Package

The code creates the package, configures the task, and components, then saves the package to disk, useful for checking the package and testing, before finally executing.

namespace Konesans.Dts.Samples
{
    using System;
    using Microsoft.SqlServer.Dts.Runtime;
    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
    using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;

    internal class SqlToFlatFile
    {
        public void CreatePackage()
        {
            Package package = new Package();
            package.Name = "SqlToFlatFile";

            // Add the SQL OLE-DB connection
            ConnectionManager connectionManagerOleDb = package.Connections.Add("OLEDB");
            connectionManagerOleDb.Name = "OLEDB";
            connectionManagerOleDb.ConnectionString =
                "Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=master;Integrated Security=SSPI;";

            // Add the Flat File DB connection, basic info only, will define add columns later
            ConnectionManager connectionManagerFlatFile = package.Connections.Add("FLATFILE");
            connectionManagerFlatFile.ConnectionString = @"C:\Temp\FlatFile.txt";
            connectionManagerFlatFile.Name = "FlatFile";
            connectionManagerFlatFile.Properties["Format"].SetValue(connectionManagerFlatFile, "Delimited");  
            connectionManagerFlatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(connectionManagerFlatFile, true);
            
            // Add the Data Flow Task 
            package.Executables.Add("STOCK:PipelineTask");

            // Get the task host wrapper, and the Data Flow task
            TaskHost taskHost = package.Executables[0] as TaskHost;
            MainPipe dataFlowTask = (MainPipe)taskHost.InnerObject;

            // Add OLE-DB source component
            IDTSComponentMetaData90 componentSource = dataFlowTask.ComponentMetaDataCollection.New();
            componentSource.Name = "OLEDBSource";
            componentSource.ComponentClassID = "DTSAdapter.OleDbSource.1";

            // Get OLE-DB source design-time instance, and initialise component
            CManagedComponentWrapper instanceSource = componentSource.Instantiate();
            instanceSource.ProvideComponentProperties();

            // Set source connection
            componentSource.RuntimeConnectionCollection[0].ConnectionManagerID = connectionManagerOleDb.ID;
            componentSource.RuntimeConnectionCollection[0].ConnectionManager = 
                DtsConvert.ToConnectionManager90(connectionManagerOleDb);

            // Set the source properties
            instanceSource.SetComponentProperty("AccessMode", 2);
            instanceSource.SetComponentProperty("SqlCommand", "SELECT * FROM sysobjects");

            // Reinitialize the metadata, refresh columns
            instanceSource.AcquireConnections(null);
            instanceSource.ReinitializeMetaData();
            instanceSource.ReleaseConnections();


            // Add Flat File destination
            IDTSComponentMetaData90 componentDestination = dataFlowTask.ComponentMetaDataCollection.New();
            componentDestination.Name = "FlatFileDestination";
            componentDestination.ComponentClassID = "DTSAdapter.FlatFileDestination.1";

            // Get Flat File destination design-time instance, and initialise component
            CManagedComponentWrapper instanceDestination = componentDestination.Instantiate();
            instanceDestination.ProvideComponentProperties();

            // Set destination connection
            componentDestination.RuntimeConnectionCollection[0].ConnectionManagerID = connectionManagerFlatFile.ID;
            componentDestination.RuntimeConnectionCollection[0].ConnectionManager = 
                DtsConvert.ToConnectionManager90(connectionManagerFlatFile);

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


            // Get input and virtual input for destination to select and map columns
            IDTSInput90 destinationInput = componentDestination.InputCollection[0];
            IDTSVirtualInput90 destinationVirtualInput = destinationInput.GetVirtualInput();
            IDTSVirtualInputColumnCollection90 destinationVirtualInputColumns = 
                destinationVirtualInput.VirtualInputColumnCollection;

            // Get native flat file connection 
            RuntimeWrapper.IDTSConnectionManagerFlatFile90 connectionFlatFile = 
                connectionManagerFlatFile.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile90;
            
            // Create flat file connection columns to match pipeline
            int indexMax = destinationVirtualInputColumns.Count - 1;
            for (int index = 0; index <= indexMax; index++)
            {
                // Get input column to replicate in flat file
                IDTSVirtualInputColumn90 virtualInputColumn = destinationVirtualInputColumns[index];

                // Add column to Flat File connection manager
                RuntimeWrapper.IDTSConnectionManagerFlatFileColumn90 flatFileColumn = 
                    connectionFlatFile.Columns.Add() as RuntimeWrapper.IDTSConnectionManagerFlatFileColumn90;
                flatFileColumn.ColumnType = "Delimited";
                flatFileColumn.ColumnWidth = virtualInputColumn.Length;
                flatFileColumn.DataPrecision = virtualInputColumn.Precision;
                flatFileColumn.DataScale = virtualInputColumn.Scale;
                flatFileColumn.DataType = virtualInputColumn.DataType;
                RuntimeWrapper.IDTSName90 columnName = flatFileColumn as RuntimeWrapper.IDTSName90;
                columnName.Name = virtualInputColumn.Name;

                if (index < indexMax)
                    flatFileColumn.ColumnDelimiter = ",";
                else
                    flatFileColumn.ColumnDelimiter = Environment.NewLine;
            }

            // Reinitialize the metadata, generating external columns from flat file columns
            instanceDestination.AcquireConnections(null);
            instanceDestination.ReinitializeMetaData();
            instanceDestination.ReleaseConnections();

            // Select and map destination columns
            foreach (IDTSVirtualInputColumn90 virtualInputColumn in destinationVirtualInputColumns)
            {
                // Select column, and retain new input column
                IDTSInputColumn90 inputColumn = instanceDestination.SetUsageType(destinationInput.ID, 
                    destinationVirtualInput, virtualInputColumn.LineageID, DTSUsageType.UT_READONLY);
                // Find external column by name
                IDTSExternalMetadataColumn90 externalColumn =
                    destinationInput.ExternalMetadataColumnCollection[inputColumn.Name];
                // Map input column to external column
                instanceDestination.MapInputColumn(destinationInput.ID, inputColumn.ID, externalColumn.ID);
            }

            
            #if DEBUG
            // Save package to disk, DEBUG only
            new Application().SaveToXml(String.Format(@"C:\Temp\{0}.dtsx", package.Name), package, null);
            Console.WriteLine(@"C:\Temp\{0}.dtsx", package.Name);
            #endif

            package.Execute();

            foreach (DtsError error in package.Errors)
            {
                Console.WriteLine("ErrorCode       : {0}", error.ErrorCode);
                Console.WriteLine("  SubComponent  : {0}", error.SubComponent);
                Console.WriteLine("  Description   : {0}", error.Description);
            }

            package.Dispose();
        }
    }
}

Sample code file and example package produced by the code.

SqlToFlatFile.cs
SqlToFlatFile.dtsx

Comments (12) -

2/8/2009 11:10:12 AM #

kim alexander

Infitesimally small & large.
duly noted.

kim alexander

2/11/2009 9:42:52 PM #

Tony Elkins

I tried a modified version of the Ole DB Source to Flat File example.  I put it into a small C# app on my local workstation.  The application successfully creates the package but fails to run the package. I get the following error: ErrorCode: -1073450754, Subcomponent:DTS.Pipeline, Description: SSIS Error Code DTS_E_PRODUCTIONLEVELTOLOW. The product level is insufficient for component "FlatFileDestination"(61).  I'm not sure what the problem is.  The package works when run from the Integrated Services Project in Visual Studio. Is it required that this code reside on the server to work properly. Any help would be appreciated. Thanks

Tony Elkins United States

2/12/2009 8:04:03 AM #

Allan Mitchell

Have a look at this article Tony and it should help
blogs.msdn.com/.../...t-level-is-insufficient.aspx

Allan Mitchell Germany

9/15/2009 3:33:33 PM #

Tony Elkins

For the Ole-Db Source to Flat File Destination example, How do you get column names/header row to appear in the flat file?

Tony Elkins United States

9/16/2009 10:24:43 PM #

Darren Green

Tony, the header row option is set on the Flat File Connection Manager. The line of interest from the sample above is  connectionManagerFlatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(connectionManagerFlatFile, true);

Darren Green United Kingdom

10/1/2009 9:13:49 PM #

umair

Hi,
   This is code working perfectly for me. I just want to know that its completely transform the data from flat file to sql table. Can we have some option that we can extract data from flat file and do some modification on the data and then dump the transformed data to SQL table. I also want to know that if i want to skip some columns from flat file, then how can i do that.


Thanks,
Umair Hasan Zubairy.

umair United States

6/17/2010 4:31:19 AM #

VickyWinner

Hi
     Can you post an example for OLEDB Source - to - OLEDB Destination?  Also I would appreciate if you could post an example on deploying this code on LIVE server(step by step illustration would be helpful).

Thanks
VickyWinner

VickyWinner India

8/24/2010 6:13:44 PM #

S Desai

Hello,

I want to create packages in code - OLE-DB Source to Flat File File Destination (.xls) using SSIS + SQL Server 2008 + C#.NET. I have added refernce to required assembly for dynamic package creation (related to SQL Servre 2008 SSIS) and use component ID as specified for SQL Server 2008 in your web site and replace 90 to 100 in above code but code does not work, it is giving an exception "instanceSource.ProvideComponentProperties();"

Please give me sample code to export SQL Server 2008 Database table to Excel file using dynamic package creation.


S Desai India

3/6/2012 5:42:18 PM #

Andy Menon

This is a wonderful article indeed! I used this code as a primer and coded a similar scenario in the 2008 environment. As mentioned in documentation elsewhere on the internet, there are some differences in programming against the 2005 and 2008 versions of the components. As far as this example is concerned, make sure to use the names that contain the right version numbers. For example, in the 2008 versions, the names mentioned in this article must be changed to DtsAdapter.OleDbSource.2 and DTSAdapter.FlatFileDestination.2. Similary, if the code fails with a COM Exception when refreshing metadata in the OLEDB source, you'll need to configure the DCOM setup on the DEV machine using the dcomcnfg utility on Windows7. In brief, fire up dcomcnfg from the command prompt, and seek out the DTS Task Host under the DCOM Config Node. In the Security tab, customize the Access Permissions and grant your login permission to allow Local and Remote Access. Close and restart Visual studio, and you should be all set.

Hope this helps for someone who'se started in the VS2010 environment just like I have.

Cheers,
Andy Menon

Andy Menon United States

3/6/2012 5:46:14 PM #

Andy Menon

Another note on the Connection String.
Use the correct provider version as well. Here's an example connection string:

Data Source=myMachine\\SQLEXPRESS;Initial Catalog=MyDatabase;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;

Note the version number in the Provider part of the connection string. This should work with both SQLExpress 2008 on you local machine as well as a remote 2008 production server.

:)

Andy Menon United States

6/12/2012 11:32:49 AM #

Srivathsani

Hi,

I wantto transfer data between excel and sql server.I had to insert data conversion element in between as there was data type.But i dont know how to change the data types of the output columns from Data conversion element.
i.e The conversion should change the unicode data present in the excel file and should get saved as non unicode in DB.Please help me with this.

Regards,
S.Srivathsani

Srivathsani India

6/12/2012 11:39:50 PM #

Darren Green

Srivathsani your recent email referenced a forum post which I have replied to, it deals with a data conversion sample.

Darren Green United Kingdom

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading