August 29, 2011 at 9:09 am
I need to update a table from an external file using SSIS but without loading the file (Excel, flat file, whatever) into a temp table in the database. Basically I want to update rows in the table based on information in the file. Is this possible? If so how?
Thanks in advance.
August 29, 2011 at 12:05 pm
Mick Opalak (8/29/2011)
I need to update a table from an external file using SSIS but without loading the file (Excel, flat file, whatever) into a temp table in the database. Basically I want to update rows in the table based on information in the file. Is this possible? If so how?
Yes, it's possible. But you don't give a lot of specifics. Is this a one time load or a reoccurring task? How confident are you in the source data? Or does the incoming file format change often?
I'm assuming you don't have permisisons to create a table, hence your prohibition about temp tables, but do have permisisons to add/update records to the table. To add records, this can be done as simply as using a flat file data source and writing directly to your destination table usine a OLE DB destination. If you want to update records, you could use the flat file data source again and use an OLE Command task to pass parameters to a stored procedure that executes an UPDATE statement. The problem downside of the OLE Command is that it executes record by record.
HTH,
Rob
August 29, 2011 at 12:11 pm
Basically my manager has issued me a challenge in SSIS. Given an Excel file, update the rows in a table by joining the file to the table via its key value and update the table, but don't use any SQL code, i.e. an UPDATE statement, to do it. In the past I've always done this sort of thing by loading the Excel file into a temp table, executing an UDPATE statement with a join and then dropping the temp table. He wants to see if this sort of thing can be done by someone who might be familiar with the table and its contents but doesn't know any SQL.
August 29, 2011 at 12:17 pm
If SQL is prohibited you can use the Slowly Changing Dimension transformation in SSIS to update your table. This works best on smaller tables. The wizard should walk you through matching up your data on your business key and then which fields to update. It is very particular about data types, so make sure you've performed any conversions on your source data prior to the SCD transformation.
MWise
August 29, 2011 at 2:21 pm
Mick Opalak (8/29/2011)
Basically my manager has issued me a challenge in SSIS. Given an Excel file, update the rows in a table by joining the file to the table via its key value and update the table, but don't use any SQL code, i.e. an UPDATE statement, to do it. In the past I've always done this sort of thing by loading the Excel file into a temp table, executing an UDPATE statement with a join and then dropping the temp table. He wants to see if this sort of thing can be done by someone who might be familiar with the table and its contents but doesn't know any SQL.
The Slowly changing dimension wizard may mitigate actually writing any SQL. I rarely use it due to performance issues, so I don't recall.
You can successfully add the updated data to an SSIS data flow but you will still have to write the UPDATE statement in SQL for the OLEDB Destination to do the actual update.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply