I have a table, let's call it AlarmTriggers and it has a Boolean field pulled into it. When my software writes to it the table doesn't set the field to true. It's kept false. I want to use an SSIS package that filters all false records in the table and pull it over to another server. After the records have been pulled I would like the SSIS package to mark those pulled records as true. So, the source table field pulled must be set to true. I am using SQL Server 2005 Workgroup Edition. Can you tell me how to do this?
You can certainly use SSIS to transfer the data to another server and to mark the rows as transferred using the Boolean column. Use a Data Flow task in SSIS to transfer the data. In the Data Fow task you will specify an OLE DB source connection that points to your server and database. In the OLE DB source you can specify a table, view or some SQL code to extract the data. You may consider using a SQL code and creating SELECT statement that filters data based on your Boolean column. When you transfer the rows using your Data Flow task you can use an EXECUTE SQL task to update the rows setting the Boolean column to True.
Dig Deeper on SQL Server Interoperability
Migrate DTS packages to SQL Server Integration Services with Migration Wizard and deploy SSIS packages in SQL 2005. Learn how to access DTS Designer ...
Can I call triggers in stored procedures or vice versa?
We're having a lot of trouble scheduling and editing SSIS jobs in SQL Server 2005. The SA has created a proxy account for us to create SSIS packages....