0

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.

2
  • Is there a reason you can't just pop it off Service Broker directly? – billinkc Mar 27 '15 at 14:20
  • 1
    And no, you can't use a "Flat file source" because that's for items on the file system. Instead, since you need to access a database table-like object, you'll be looking at an OLE DB Source or an ADO.NET Source depending on your connection managers – billinkc Mar 27 '15 at 14:44
1

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

    }

}
2
  • Thanks for the suggestion, I'm going to give it a shot now. – Fishcake Mar 27 '15 at 14:20
  • Works perfectly, thanks for pointing me in the right direction. – Fishcake Mar 30 '15 at 8:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.