5,722 reputation
11342
bio website billfellows.blogspot.com
location Kansas City, MO
age 39
visits member for 3 years, 7 months
seen yesterday

Developer, devourer of pie, disciple of databases, dictator of datum, drinker of tea, delirious with alliterative devices

@billinkc

SQL Server MVP


Dec
4
comment Loading XML data from SQL Server directly into MarkLogic?
Knowing nothing of MarkLogic, I find it's helpful to restate questions without an emphasis on a particular technology. Thus, "how can I load data directly into MarkLogic" If you have the answer to that, then it becomes easy to swap in data sourced from SQL Server and possibly using SSIS as the conduit for moving the data. That said, SSIS has no native connectors for MarkLogic.
Dec
4
answered Getting new and updated data with an archive column
Dec
2
comment Getting new and updated data with an archive column
How about making use of the OUTPUT virtual table? Set your transaction isolation level correctly (snapshot/serializable) so that you only see the rows as of the moment your process begins. Use the following for your OLE DB Source UPDATE T SET Archive = 1 OUTPUT DELETED.* FROM Table T WHERE T.Archive = 0; That updates everything in a nice atomic operation with a side effect of generating the target output into your data flow buffers. Route that to your destination and it's done. Nice and neat
Dec
1
revised Replication Madness
added 346 characters in body
Dec
1
comment Replication Madness
@MaxVernon I guess that means the death of untold lives is less than EE licensing and/or increased disk capacity. Pity too, lives these days are just falling in price. In my day, a life would buy you a steak and kidney pie, a cup of coffee, a slice of cheesecake and a newsreel. With enough change left over to ride the trolley from Battery Park to the polo grounds.
Dec
1
comment SSIS cannot access the file
What I would propose is identifying what specifically is failing. It sounds like there a few different candidates for failure in play here: Network share - are you using a UNC path \\server\share or a mapped drive `S:` Since this is Excel, you are running the job in 32 bit mode, right? How does it access the Excel file - read or write? If you make a similar package that just attempts to access the directory (foreach file enumerator), does it "see" the files there (this can help identify permission issues)
Dec
1
comment SSIS cannot access the file
Perhaps it's my ignorance, but I don't see how changing your package protection level is going to benefit this. It doesn't appear to be an issue related to a connection manager which uses stored credentials
Nov
26
comment resource wait SQLCLR - wait_type CLR_SEMAPHORE by SPID 0
Guessing it's an inversion of the installation so EXEC master..sp_procoption N'sp_ssis_startup', 'startup', 'off'
Nov
26
comment resource wait SQLCLR - wait_type CLR_SEMAPHORE by SPID 0
Since it's 2012 and you mention ETL, are you using SSIS? Specifically, the new SSISDB? CLR is required for that to work and when you create the catalog, you have the opportunity to specify that a proc should run on server startup. Perhaps you've selected that for one instance and not in others
Nov
18
comment Oracle 11g SSIS SQL Server 2014 Truncation
Current on your patches? How are you able to tell when the truncation occurs?
Nov
17
revised Any way to import-export-migrate-save-restore a maintenance plan without having SSIS installed (and running)?
added 245 characters in body
Nov
10
revised SQL Server edition support
added 24 characters in body; edited tags; edited title
Nov
10
reviewed Leave Open Division Email Template DB Design
Nov
10
reviewed Close A transaction log grows unexpectedly
Nov
10
reviewed Close The memory usage of SQL Server keeps growing
Nov
10
reviewed Close How to insert values into Bridge Table in star schema?
Nov
10
reviewed Close Update datatype of primary key without deleting the table-SQL Server
Nov
4
comment SSIS package error, can't find pre-compiled script
Look at your Output/Error window. Why it can't find the compiled script is unknown but if the source got modified and it no longer compiles, you'll get the above situation.
Nov
4
comment How to specify localhost for SSIS package source server?
and the error message is...
Oct
30
comment Why is Excel File Opened Exclusively By Another?
I can reproduce your error, if I have Excel open