February 13, 2012 at 11:44 am
My VB skills are rusty, but I am getting them back during our current project. The overall goal is to look into a source folder, take the files that are found, edit each file removing the final row in the file (check sum line to be ignored), dynamically add 2 additional columns (SourceFileName & BatchDate...both found in the original file name), determine which of 4 different main file types (8 total different format variations) the file is, and call the correct SSIS package to load the file into the database.
We had a VBscript file running part of this, but as we had issues, and the file kept needing to be modified. I brought it into Visual Studio.Net 2010, and this does not seem to like the WScript option we used to call a batch file which called the SSIS package in the original VBscript version. As such my thought was to find a means of calling the SSIS package directly via VB.Net. I found some code snippets, but so far Visual Studio does not like the syntax.
The code is by no means done, so I am sure there are several areas set up incorrectly. Can someone point out the best way to call the packages? Attached is the entire script I've got so far. There are additional functions which will be created, but they won't be done until I can get at least the first sub function to be accepted.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
February 13, 2012 at 12:40 pm
Ok, I believe I've figured it out, and I feel a touch foolish for missing such a simple solution:
TestString = Chr(34) & "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" & Chr(34) & " /f " & Chr(34) & "I:\Users\Jarid\ClientNameWithheld\ClientNameWithheld_ETL\ClientNameWithheld_ETL\ClientNameWithheld_ETL\DTSX Packages\import_ad.dtsx" & Chr(34) & " /X86" & Chr(34)
Shell(TestString)
I have not running this in the live version (there are file location issues you will need to focus on with the dtsx package), but I was able to call the package with the correct error for the current setup. In short, it worked.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
February 14, 2012 at 12:10 am
That seems to be OK.
Thanks for posting the solution.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply