January 26, 2011 at 2:14 pm
I am dynamically creating SSIS packages using VS 2008 (C#) and would like to create the destination table on the fly from the source structure. The destination does not exist, so If I can create it while building the package dynamically this would be ideal. We will create and run thousands of packages, one per table. We are going from a Progress database to MS SQL 2008. I believe I could use the old "SELECT TOP 0 * INTO DestTable FROM SourceTable" or something like that, but I would like to use mechanisms in SSIS to do it if possible using the Microsoft.SqlServer.??? namespaces.
I have the Source Component created and can see the columns and DTS datatypes, but would like to do the same as what the designer does when you create a new destination table and it give you the CREATE TABLE ... code. So there must be properties and methods to get the information from the source to build the destination table.
Same goes for the Error Destination table. Once the table are build then the dynamic mapping that I have will work perfectly for the destination and error capture.
Right now I have to create the table before hand, but with thousand of tables I need to do it dynamically.
Thanks.
January 26, 2011 at 2:38 pm
I ended up creating a custom PipelineComponent class that I added to SSIS.
The aim was to bulk insert into a SQL Server table, named by a property, but the table's schema was created to match the definition of the input piepline.
Component configuration:
* Name of the table to be created
* Batch size for bulk copy (default 10000 rows)
* SQL ADO/.NET connection manager
The preExecute method of the component:
* creates a .NET DataTable
* creates the SQL Server table based on the data types of the input pipeline. Note that SSIS UI1, UI2 and UI4 datatypes are rounded up to T-SQL smallint, int and bigint in order to prevent overflow. The SQL Server table has to be defined with all columns nullable.
* creates a SqlBulkCopy object to transfer the DataTable to SQL Server
During execution, the component loads the DataTable and after every ## rows (default 10000) writes out to SQL Server.
On receipt of EndOfRowset any remaining rows are written out and everything is closed and disposed.
It works okay but the use of SlqBulkCopy means that it must run on the same platform as the SQL Server instance.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply