June 20, 2011 at 10:02 am
Each quarter we receive a spreadsheet containing data that is used to update a column in a table. We usually just concatenate the fields within the spreadsheet to build update statements, but would like to automate this a bit.
The spreadsheet contains the primary key of the destination table and new values for a particular field, based on the primary key. Is SSIS the right tool for this? We've played around with iSSIS a little, but keep getting the "Unicode - NonUnicode" error. This error occurs even when using Data Conversion in the package.
We could import the spreadsheet data into a staging table and do the updates from there, but we would like a one shot solution if possible.
Any ideas on the best approach to this project?
June 20, 2011 at 10:22 am
bpowers (6/20/2011)
Each quarter we receive a spreadsheet containing data that is used to update a column in a table. We usually just concatenate the fields within the spreadsheet to build update statements, but would like to automate this a bit.The spreadsheet contains the primary key of the destination table and new values for a particular field, based on the primary key. Is SSIS the right tool for this? We've played around with iSSIS a little, but keep getting the "Unicode - NonUnicode" error. This error occurs even when using Data Conversion in the package.
We could import the spreadsheet data into a staging table and do the updates from there, but we would like a one shot solution if possible.
Any ideas on the best approach to this project?
SSIS is as good a place as any.
Sounds like you just need to get the data conversion errors sorted to make this work.
After creating the data conversions, did you remember to remap your fields (data conversion creates new fields, leaving the original fields as-is)?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 20, 2011 at 12:07 pm
That was it. When using the Data Conversion it creates a copy of the input columns with the new data type. We were still using the original links.
Is there an "Update" control item available? If not, any idea on how we can accomplish an update to our table based on the primary key in our speadsheet? We have been looking, but can't seem to find the right documentation to help us.
Thanks
June 20, 2011 at 12:35 pm
bpowers (6/20/2011)
Is there an "Update" control item available? If not, any idea on how we can accomplish an update to our table based on the primary key in our speadsheet? We have been looking, but can't seem to find the right documentation to help us.
Unfortunately, not in built-in tools. Inserts work no problem from the stream, but deletes and updates usually are applied via the SCD (Slowly Changing Dimension) component. That's a row-by-row component. Part of the reason for that is the way the stream wants to operate, which is row-by-row (or at least chunk by chunk) at the highest speed possible, so that it doesn't have to wait for the stream to end before the next component can begin working.
In this case, you would be best off dumping to a staging table and triggering the update from a proc so you can do a single pass.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 20, 2011 at 12:38 pm
That is what I needed to know.
Thanks to everyone here for their help. It is appreciated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply