I have a SSIS 2005 package that is up and running in our production environment.
The package uses an SMTP Connection Manager to send an e-mail message out to a designated user.
We have a scheduled job that executes this package, and also overrides the SMTP connection string so that the package can target the test or production mail server, which makes it possible to keep a single service on both our test and production servers, just configured differently.
We recently changed the server name of our production mail server and went into the scheduled job and changed the command prompt values that run to point to the new server.
At this stage we can determine which rows of the Excel sheet are new, and which exist already. Before sorting the columns, I would advise converting the Excel columns to the same type as database columns, in this case I have selected the four columns: a. The data belonging to the new row will be inserted into the table, and the data belonging to the Existing Row will update said row. Drag the line from the conditional split to the OLE DB Destination, you will be prompted to choose the output, you want to select New Row (as defined in the Conditional Split) 4.
To do this we use a conditional split where we ascertain if the UID is null (from the database table), if it is we know this is a new row and so should be inserted into the table, if it isn’t we know this row exists in the table and so the row should be updated (perhaps one of the column values has changed). Choosing the mapping tab and ensure they map to the columns in the table That is the insert sorted, how about the update; 1. Drag the green line from the conditional split to the OLD DB Command, you will be prompted to choose the output, select Existing Row (as defined in the conditional split) 3.
A couple of nice-to-know tips about this: The actual data source properties in the SSIS project look as following: So here you will create a new connection using Excel Connection Manager, set the Data access mode (which is “Table or view” if you need all the data from the excel sheet) and then just select the data sheet from which you need the data.