I need to grab data stored on a remote AS/400 file system to which I connect using a linked server connection, so I can send OPENQUERY
select statements and receive the data on SQL Server.
I don't know how many files/tables I'm going to end up with yet, but at this point I know enough to see that I'm going to need a flexible and scalable solution.... that ideally performs well enough.
So I have created a table in my Metadata
database that looks something like this:
Id Filename FilterColumn FilterValue LastTransferred
1 STDLIB.FOOBAR TRXDATE NULL NULL
2 STDLIB.SOMETH NULL NULL NULL
...
My control flow looks like this:
Collect Metadata
↓
[ Foreach Loop Start ]
Execute DeleteStmt
↓
Execute OPENQUERY
↓
Write to destination
[ Foreach Loop End ]
In the Collect Metadata step I have a data flow task that reads from the above metadata table and a script component that uses it to generate T-SQL statements - one to truncate/delete the existing local tables, and another to select the remote data; then I send everything into a Recordset Destination.
So far, so good.
The Foreach Loop Container iterates the recordset and can execute the generated truncate/delete commands, then executes the OPENQUERY
select command, and dumps the result set into a dedicated User::RemoteData
object variable.
And this is where I'm stumped: in the "Write to destination" step, I have another script component (configured as a "source") that takes this variable and uses an OleDbDataAdapter
to fill up a DataTable
, from which I'm able to pick up the column names and actual row data (every iteration of the Foreach loop container has a different table in User::RemoteData
).
So I started implementing the CreateNewOutputRows
override like this:
public override void CreateNewOutputRows()
{
using (var records = new DataTable())
using (var adapter = new OleDbDataAdapter())
{
adapter.Fill(records, Variables.RemoteData);
var builder = new StringBuilder();
builder.Append("INSERT INTO dbo.");
builder.Append(Variables.StagingTableName);
var columnNames = new string[records.Columns.Count];
for (var index = 0; index < records.Columns.Count; index++)
{
columnNames[index] = records.Columns[index].ColumnName;
}
builder.Append("(");
builder.Append(string.Join(",", columnNames);
builder.Append("););
builder.Append(" VALUES (");
foreach (DataColumn column in records.Columns)
{
// ok, stop, think. this can't be right.
}
}
}
So I stopped and started thinking... and I can't think of a nice, neat way to do this.
Am I going to have to script these INSERT
commands and check column types to properly single-quote-delimit string values and whatnot?
Isn't this going to perform like a snail? I'm not liking that the Foreach container is going to process each table sequentially, and I can already see the above solution completing in hours... when it's only the "transfer data over from remote system" part (I have many more ETL steps coming up, that will need to consume this data).
What would be a better way? Do I have to drop the idea of sourcing my data flow from my Metadata
database, and have n
"statically-defined" workflows, one for each table?
select *
from each table in the remote database, as that makes things very easy, or are you doing incremental ETL?