March 7, 2008 at 7:00 am
Hi,
After making my other post here, I did find some more info about running a VB.net routine in the script task. I know this sounds crazy but is there any way I can just run a script from SSIS and let it handle all of the data loading and saving? Its like just running my VB.net app in SSIS and thats it.
I don't want to run the script on an event - I just want to run it once at the start and thats it.
Can anyone give me an example of how I would do this?
Thanks!
March 7, 2008 at 7:47 am
Just to let everyone know, I assume I got it. I added a Script Task to the control flow page. Then I clicked the Designed Sript button and added in my code from VB.net. I had to select PrecompiledScriptIntoBinaryCode to False. Then I just ran the project and it worked.
I'm still figuring all of this out...
Warren
March 7, 2008 at 12:52 pm
Hi There,
I used 'Execute Process Task ' in SSIS to call a VB Script that I had written.I also have used Execute Process task to call a third party vendor programme in SSIS.
Im not sure if this would help you out.Try giving Execute Process task a try.
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
March 7, 2008 at 1:03 pm
Thanks for the input. I'll definately take a look at that.
Warren
November 24, 2008 at 9:33 am
Hi There,
I used 'Execute Process Task ' in SSIS to call a VB Script that I had written.I also have used Execute Process task to call a third party vendor programme in SSIS.
Im not sure if this would help you out.Try giving Execute Process task a try.
I have also made this work (sort of). It will run from VS2005 but I need to deploy or set it up to run as a SQL Server Agent job. I get all kinds of errors. I am not ever sure where to start looking.
My VB.net project reads a SQL database. Based on the information within a table it will retieve files from the ftp server in that table. It works well when run as .exe it works well when I run it from the SSIS package. But when I deploy it does not work. Where and how do I deploy.
Mark F
November 24, 2008 at 1:19 pm
The script task, as you have discovered, is just a place where you write VB.NET code and was, I believe, included to cover those situations where the other SSIS components did not offer the features and flexibility required.
If you could make your import run in SSIS using just the standard components, it would almost certainly run faster. The standard components may also be easier to maintain, should you leave the company - so your management might prefer it.
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 24, 2008 at 1:27 pm
Phil
I tried to use the script task for this process. I had problems connecting to the database. for the table read.
There are never more then 20 - 30 records to read from the table.
I am willing to keep everything in SSIS however I cannot find enough ssis script tasks information in the 5 books or the internet to cover all the topic required to accomplish this task.
Mark
November 24, 2008 at 1:36 pm
As you have things up and working, you've probably lost the urge to change how it operates now (I know I would!).
And given that you have so few source records, there's no performance issue.
But out of interest, where is your source data coming from? Why is it so difficult to access? What are you doing in VB.NET that you could not do using standard SSIS components?
Maybe you could post the bit of VB.NET code that creates the connection to the source data ...
Cheers
Phil
--edit--
OK, I just answered your post as if you were the person who posted the original thread, sorry
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 24, 2008 at 1:47 pm
Thanks Phil for all the help. Most Of my issues might be to lack on knowledge. This is the code I use in Vb.net I use the connect data on top of screen. But this option is not available when coding a script task.
Imports System.Net
Imports System.IO
Module ReadMYdata
Sub Main()
Dim FileCount As Integer
Dim Ftp As New FTPSession
' Load the Database table TodaysFiles for reading
Dim ADA As New MyDataSetTableAdapters.TodaysFilesTableAdapter()
Dim TBL As New MyDataSet.TodaysFilesDataTable()
ADA.Fill(TBL)
' Set the Default ftp site "FTP://Ftp.ftphost.com"
Dim FTPSite As String = "ftp://ftp.ftphost.com"
' Set the path to load file to
Dim FTPDestination As String = "G:\Production\Input\NTSFiles"
' Loop thru the table for good records to use.
For Each row As MyDataSet.TodaysFilesRow In TBL.Rows
If row.Status_of_File = "On FTP" Then
http://Ftp.RetriveFTPfile(row.User_Name, row.Pass, FTPSite, row.Sub_Dir, row.FileToFind, FTPDestination)
FileCount = FileCount + 1
'Console.WriteLine("info {0} {1} {2} {3} {4} {5}", row.User_Name, row.Pass, FTPSite, row.Sub_Dir, row.FileToFind, FTPDestination)
Else
End If
Next
' Console.WriteLine("Total Files Loaded {0}", FileCount)
' Console.ReadLine()
End Sub
Public Class FTPSession
Sub RetriveFTPfile(ByVal Uname As String, ByVal UPass As String, ByVal FTPSite As String, ByVal Sub_Dir As String, ByVal FiletoFind As String, ByVal FTPDest As String)
Dim ftpFileUri As String
ftpFileUri = FTPSite & Sub_Dir & FiletoFind
FTPDest = FTPDest & "\" & FiletoFind
'Console.WriteLine("URI = {0}", ftpFileUri)
'Console.WriteLine("info {0} {1} {2} {3} {4} {5}", Uname, UPass, FTPSite, Sub_Dir, FiletoFind, FTPDest)
Try
Dim fwr As FtpWebRequest
fwr = FtpWebRequest.Create(ftpFileUri)
fwr.UseBinary = True
fwr.Credentials = New NetworkCredential(Uname, UPass)
fwr.Method = WebRequestMethods.Ftp.DownloadFile
Dim LocalFile As New FileStream(FTPDest, FileMode.CreateNew)
Dim sr As Stream = fwr.GetResponse().GetResponseStream()
Dim Buff(1024) As Byte
Dim BytesRead As Integer = sr.Read(Buff, 0, 1024)
While BytesRead <> 0
LocalFile.Write(Buff, 0, BytesRead)
BytesRead = sr.Read(Buff, 0, 1024)
End While
LocalFile.Flush()
LocalFile.Close()
sr.Close()
Catch ex As Exception
End Try
End Sub
End Class
End Module
November 24, 2008 at 2:13 pm
I had a quick look at the code: so for selected rows in a source SQL Server table you are executing an FTP process?
You need to get yourself on an SSIS course!
Once you have done the course ...
- Define a connection to your SQL Server database
- Use a query as your datasource to select only those rows you are interested in
- Use a FOREACH container to process each input row separately
- Use an FTP task within the FOREACH container
... you get the gist.
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 25, 2008 at 5:37 am
Phil
I have had Appdev SQl Server Integration Service, I also have read from cover to cover Wrox Professional SQL Server 2005 Integration Service. What I need is experience. Not every topic is covered in the classes or the books.
I thank you for the advice, But I still need to be able to either execute my already creaded vb.net code that is working inside of my SSIS package. From a SQL Server job agent. Or be able to run this vb.net code from a SQL server job agent..
Thanks Mark
November 25, 2008 at 12:32 pm
The version of VB.NET used in SSIS is, I believe, called VB.NET for Applications and is not the full-blown development environment in which you have developed your earlier piece of code. So the fact that it will not run as-is in SSIS is not so much of a surprise.
I was under the impression that you wanted to use SSIS to achieve your goal - using the built-in functionality - and that is what I was directing my advice at.
So - on to the final point - I do not see any reason why you cannot use SQL Agent to schedule the running of your already created external .exe file. In this case, SSIS is taken out of the equation completely.
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 25, 2008 at 12:48 pm
Phil
I thank you again. This brings me back to my main question.
I have a Vb.net windows service project. I can run it from VS2005. However I cannot figure out how to deploy it so I can run it. From SQL Job agent.
I am trying to place it on our G:\DS_Library and want to run it on the 2005 SQL Server. I have tried following all the publish instructions in the books and on the web. I can get it to be in the program directory of another pc. But it will not run. There are many differant errors.
Mark
November 25, 2008 at 12:57 pm
So you want to use SQL Agent to start a Windows service? What happens if the service is already running?
What login credentials is the SQL Agent service running with? Can you try logging in as the SQL Agent user and then executing the .exe manually, just to check?
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 25, 2008 at 1:00 pm
Phil
Now you have hit the nail on the head. This is all new to me. As you can see I am a newbie
Mark
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply