March 15, 2018 at 5:27 pm
Hi Guys
I'm back banging my head on what I thought would be a simple task. I need to copy the contents of a text file to the windows clipboard.
I'm using a script task to do the read and paste and have no problem reading the file. I've been researching VB methods of writing to the clipboard and tried lots of them but when they build ok, they all crash with an exception error. So I'm obviously doing something wrong or missing something.
I have added Imports System.IO to the code and tested the File.readAllText by writing to a new file correctly. But when I replace the streamreader code with the Clipboard code it crashes.
I've come across some VB info that states "The Clipboard class can only be used in threads set to single thread apartment (STA) mode" and I've tried unsuccessfully to implement that, but the VB wrapper in SSIS says 'don't play with the attributes' etc.
I've pasted my VB code below in the hope that smarter people than I will spot my error, help me implement the STA mode if needed, or (as usual) point out a better solution!
Once I get this sorted I can stream the data quality output from a looped SQL load, process and extract process directly into an Excel workbook and do myself out of a job!
Public Sub Main()
'
' Add your code here
Dim putLine As String
putLine = File.ReadAllText("C:\Temp\2 Column Output.txt")
Clipboard.Clear()
Clipboard.SetText(putLine, TextDataFormat.Text)
'Dts.TaskResult = ScriptResults.Success
End Sub
March 15, 2018 at 5:57 pm
Excuse my left-field question (I don't have an answer for you), but why would you want to copy something to the clipboard?
March 15, 2018 at 9:28 pm
Hi Martin
That's not left field at all.
I use SQL server to modify and consolidate data from around 100 organisations.
With the help of the wonderful guys here I now have a nice SSIS script that:
unzips the original files the organisations provide,
loads them into MS SQL,
Moves the original zip to a 'done' folder,
processes the original contents,
drops out a copy of the modified originals,
zips them and stores them in an 'modified processed' folder,
creates a totals-so-far set, zips them, stores the zip in a 'files so far' folder.
(Lots of variablenaming of zip files etc.along the way)
We need to check that missing demographic data is below 10%, so along the way I run a few SQL queries to count the number of missing items, identify the individual and this is written to a text file in SSIS.
Currently I open the text file in Notepad and copy the content. I then have a macro in an Excel spreadsheet with a macro button that pastes the data, calculates the percentages of missing data and pastes the record into the next line of a single worksheet.
By getting SSIS to copy to the clipboard I can loop through the 100 or so organisations as I'll be able to auto-run the paste macro in the spreadsheet.
The task for each organisation used to take around 50 minutes to complete manually. It's now down to around a minute each. With the clipboard paste and a for-each loop I hope it will be down to about 15 minutes for 100 organisations. And a single button push.
March 16, 2018 at 3:28 am
Have you tried using Powershell instead of VB?
John
March 16, 2018 at 5:14 am
steve.alston - Thursday, March 15, 2018 9:28 PMWe need to check that missing demographic data is below 10%, so along the way I run a few SQL queries to count the number of missing items, identify the individual and this is written to a text file in SSIS.Currently I open the text file in Notepad and copy the content. I then have a macro in an Excel spreadsheet with a macro button that pastes the data, calculates the percentages of missing data and pastes the record into the next line of a single worksheet.
By getting SSIS to copy to the clipboard I can loop through the 100 or so organisations as I'll be able to auto-run the paste macro in the spreadsheet.
The task for each organisation used to take around 50 minutes to complete manually. It's now down to around a minute each. With the clipboard paste and a for-each loop I hope it will be down to about 15 minutes for 100 organisations. And a single button push.
Do you have Excel installed on the same server as SSIS? If not, there's no easy way to get SSIS to run Excel macros.
It may be possible to 'inject' the text from the Notepad file into the appropriate cell in an Excel workbook, but that is likely as far as you will get.
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
March 18, 2018 at 4:30 pm
Getting the text into Excel is the easy part, I've done similar things before as part of an SSIS script. I just run an execute process task calling Excel with the /e argument calling the spreadsheet. I got the spreadsheet to autorun the macro and the last part of the macro closes Excel. Admittedly it makes modifiying the spreadsheet or macro a bit tricky if you forget to bypass the macro autorun when you open the spreadsheet to do that, but it works and the SSIS script flows nicely.
As I already have the Excel bit working and starting with a paste from clipboard I was hoping SSIS would be able to paste to clipboard.
It sounds so simple, but I appreciate that it's probably a weird question!
March 18, 2018 at 4:31 pm
John Mitchell-245523 - Friday, March 16, 2018 3:28 AMHave you tried using Powershell instead of VB?John
Hi John
Er no. I don't know much about Powershell.
Maybe it's time to learn!
March 18, 2018 at 5:59 pm
John Mitchell-245523 - Friday, March 16, 2018 3:28 AMHave you tried using Powershell instead of VB?John
Thank you John!
I can't believe it's so simple in Powershell. I can see I'll have to learn more.
At last I can build the looped job I've dreamed about!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply