Sql-server – Treat database field as a flat file source

sql serverssis

We have a partner sending us csv data to a web service of ours that gets sent to a messaging system that is SQL Server based.

I am writing data to disk and then importing to another database by using this file as a flat file source in an SSIS Data Flow task.

Although this works I'd like to avoid using the file system and treat the database field as a flat file source.

Is this possible?

I'm very new to SSIS packages and searching for this scenario is very difficult because of the number of hits just relating to importing a csv file.

Best Answer

My first inclination would be to use a script component (e.g. C#) as source in a Data Flow Task. The script would just read the column contents, parse the individual records and fields, and send records down the pipeline. The individual fields (perhaps strongly-typed) can be defined in the output record definition of the designer.

https://msdn.microsoft.com/en-us/library/ms136060.aspx contains an example of parsing a CSV file and processing a SQL query. You can combine the two examples for your needs. Below is an example of this. I don't have time to test right now but it should get you started. 

public class ScriptMain:
    UserComponent

{
    IDTSConnectionManager100 connMgr;
    SqlConnection sqlConn;
    SqlDataReader sqlReader;

    public override void AcquireConnections(object Transaction)
    {
        connMgr = this.Connections.MyADONETConnection;
        sqlConn = (SqlConnection)connMgr.AcquireConnection(null);

    }

    public override void PreExecute()
    {

        SqlCommand cmd = new SqlCommand("SELECT CsvContents FROM dbo.CsvImportTable;", sqlConn);
        sqlReader = cmd.ExecuteReader();

    }

    public override void CreateNewOutputRows()
    {

        while (sqlReader.Read())
        {
            {
                string csvContents = sqlReader.GetString(0);
                CreateNewOutputRowsFromCsv(csvContents);
            }
        }

    }

    public void CreateNewOutputRowsFromCsv(string csvContents)
    {

        string nextLine;
        string[] columns;

        char[] delimiters;
        delimiters = ",".ToCharArray();

        nextLine = textReader.ReadLine();
        while (nextLine != null)
        {
            columns = nextLine.Split(delimiters);
            {
                MyAddressOutputBuffer.AddRow();
                MyAddressOutputBuffer.AddressID = columns[0];
                MyAddressOutputBuffer.City = columns[3];
            }
            nextLine = textReader.ReadLine();
        }

    }

    public override void PostExecute()
    {

        sqlReader.Close();

    }

    public override void ReleaseConnections()
    {

        connMgr.ReleaseConnection(sqlConn);

    }

}
Related Question