SSIS bits 'n' bobs: Recent Comments

Comment on When to Not use SSIS - directory / file iterations!!!!

Paul Ibison — Sat, 02 Feb 2008 19:47:46 GMT

If only it were that simple in an investment bank

Comment on When to Not use SSIS - directory / file iterations!!!!

Nigel — Wed, 30 Jan 2008 19:40:22 GMT

Why don't you use robcopy.exe to sync the directories - it's a one liner. I use it all the time, there are loads of copying options and its an official MS utility - found in the server 2003 resource kit tools, so there is no reason why even corporate administrators would not install it.

Comment on Escaping Double Quotes in Flat Files: SSIS is different to DTS

JeroenS — Thu, 24 Jan 2008 09:53:21 GMT

aaah finaly someone is sharing my SSIS problem :) I'm trying to import a csv using SSIS; Works fine in DTS but not in SSIS; Looking at the problem seems that SSIS finds a " in the data and DTS 'just imports' the data...
"blatekst"","blatekst2","blatekst3" fails in SSIS..
even using the microsoft sample 'undouble' (http://www.microsoft.com/downloads/thankyou.aspx?familyId=b51463e9-2907-4b82-a353-e15016486e1d&displayLang=en) does not work; it hangs first on reading the csv before the " is changed...

Thanks,
Jeroen Schoenmakers

Comment on Simple Method for Importing Ragged files / Files with Different Row Types in SSIS / Files with a problematic final row

Paul Ibison — Mon, 14 Jan 2008 08:08:06 GMT

No need for a Group By - if you wanted, you could define the logic in TSQL by using Substring for each column and it ends up like the conditional split method. But when you have 100s of columns, this IMO isn't really viable and is difficult/impossible to support solutions with non-explicit mappings like this.
Cheers.

Comment on Simple Method for Importing Ragged files / Files with Different Row Types in SSIS / Files with a problematic final row

bear in a box — Mon, 14 Jan 2008 01:24:26 GMT

once it was in the database, wouldn't an sql-insert (with a group by and filter to remove empty rows) do, instead of exporting again as a file?
just a thought, and even if so, tests for time to complete vs. file-based ops like above.

Comment on Coping with No Column Names in the OLEDB Data Source Editor

Luke — Sun, 09 Dec 2007 18:22:53 GMT

A caution for method #2: In SQL 2000, at least, table variable performance takes a _huge_ nosedive when the rowcount exceeds ~200. (It was explained to me as roughly analogous to moving the table from the stack to the heap.) In my case, a query that would return 30k rows in five seconds on its own would take over two minutes to return the same number of rows when written as a table-valued UDF.

I haven't been brave enough to test this in SQL 2005, but I see nothing to suggest that it's changed.

Comment on Thorough Package Logging - Some Lessons Learned and Recommendations

Luke — Sun, 09 Dec 2007 17:33:19 GMT

One problem with using event handlers for logging: my current project makes extensive use of nested packages, and I found it impossible to create a log4net-like email logger using only event handlers. I had to create a custom, stateful logger to filter out the garbage.

Comment on Coping with No Column Names in the OLEDB Data Source Editor

Paul Ibison — Sat, 17 Nov 2007 21:09:13 GMT

Hi Shawn,
strange that this didn't work.
At first I thought it might be related to recompilation, so I forced the proc containing the table variable to recompile but it still worked fine for me - no SET FMTONLY OFF and one execution only. Can you email me your proc (Paul.Ibison@ReplicationAnswers.Com) and I'll try to repro. For now you might want to take a look at the comments of Phil and myself which will greatly reduce the # executions of the proc.
Cheers,
Paul

Comment on Coping with No Column Names in the OLEDB Data Source Editor

Shawn — Fri, 16 Nov 2007 21:53:58 GMT

I've tried using method #2 but unfortunately it still won't work without the "SET FMTONLY OFF" command which results in it running 5 times. My query however is more complex than just putting data into a variable table, there's an insert command into an audit table and a few other things. I DID however SELECT * (and tried individual records) at the bottom of the SProc. I'm not sure what's going on :(

Comment on Coping with No Column Names in the OLEDB Data Source Editor

Phil Brammer — Fri, 16 Nov 2007 00:02:31 GMT

On option 1 above, what happens if you set the DelayValidation property to true on that component? Do you still get 5 executions of the sproc?

Also, what was the auditing mechanism in the sproc?

Basically, I'm looking for a repro so we can get a solid "best practices" going forward from the guys at Microsoft.