SSIS FILE Copy task with variable.and DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

SSIS FILE Copy task with variable.and DB

Author

Topic

abcefg
Starting Member

16 Posts

Posted - 2009-03-26 : 09:19:19

Hello
I have Tran and backup files which made by automatic schedule task. Every 3rd hour it run and creates back/Tran files.
Now I want to copy these entire file to network define path.
But challenge is.
1. All Filename should be saving in my Configuration db, also want to save the copied-date and failed/success result.
2. The DTS look into the db and work only those files which Status is set as Need-to-be-send
3. DTS may call to some table and get the file names which need to be send and DTS send these files only.
4. Once File copied succeed, update the record against the file name and set that is FileSent flag
5. What I have done yet.
a. I made a foreachloop task and load All File Name into Filename Table.
b. Waiting for help: {How to pass data from database to variable and using this variable copy the file to network directory.}
6. By this way we can audit what file and when I sent to remote-path.
As you know I am new in ssis,. So help me. Please

abcefg
Starting Member

16 Posts

Posted - 2009-03-30 : 01:32:45

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient
Imports System.Collections
Imports System.IO

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
'
' Add your code here
'==============================================================
Dim objCon As OleDb.OleDbConnection
Dim objCommand As OleDb.OleDbCommand
Dim objReader As OleDb.OleDbDataReader
Dim objConnectionManager As ConnectionManager
Dim sConString As String
Try

objConnectionManager = Dts.Connections("localhost.ETLConfigurationDB")

objCon = New OleDb.OleDbConnection(objConnectionManager.ConnectionString)
objCon.Open()

objCommand = New OleDb.OleDbCommand("Select top 1* From [FTPdetail]", objCon)
objReader = objCommand.ExecuteReader()

objReader.Read()
Dim sEDWHServerFTP_IP As String = objReader.Item("EDWHServerFTP_IP").ToString()
'Dim sServerUserName As String = objReader.Item("EDWHServerFTP_UserName").ToString()
Dim sEDWHServerFTP_Pwd As String = objReader.Item("EDWHServerFTP_Pwd").ToString()
Dim sEDWHServerFTP_Port As String = objReader.Item("EDWHServerFTP_Port").ToString()
Dim sEDWHServerFTP_Timeout As String = objReader.Item("EDWHServerFTP_Timeout").ToString()
Dim sEDWHServerFTP_ChunkSize As String = objReader.Item("EDWHServerFTP_ChunkSize").ToString()
Dim sEDWHServerFTP_Retries As String = objReader.Item("EDWHServerFTP_Retries").ToString()
Dim sEDWHServerFTP_UserName As String = objReader.Item("EDWHServerFTP_UserName").ToString()

objReader.Close()

'objReader.Item("empno").ToString()

'Create the connection to the ftp server

Dim cm As ConnectionManager = Dts.Connections.Add("FTP")

'Set the properties like username & password

cm.Properties("ServerName").SetValue(cm, sEDWHServerFTP_IP) 'You define Server IP also
cm.Properties("ServerUserName").SetValue(cm, sEDWHServerFTP_UserName)
cm.Properties("ServerPassword").SetValue(cm, sEDWHServerFTP_Pwd)
cm.Properties("ServerPort").SetValue(cm, sEDWHServerFTP_Port)
cm.Properties("Timeout").SetValue(cm, sEDWHServerFTP_Timeout) 'The 0 setting will make it not timeout
cm.Properties("ChunkSize").SetValue(cm, sEDWHServerFTP_ChunkSize) '1000 kb
cm.Properties("Retries").SetValue(cm, sEDWHServerFTP_Retries)

' cm.Properties("ServerName").SetValue(cm, "10.0.0.102")
'cm.Properties("ServerUserName").SetValue(cm, "")
'cm.Properties("ServerPassword").SetValue(cm, "")
'cm.Properties("ServerPort").SetValue(cm, "21")
'cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
'cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
'cm.Properties("Retries").SetValue(cm, "1")

'create the FTP object that sends the files and pass it the connection created above.

Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server

objCommand.CommandText = "SELECT (select top 1 dbo.FTPdetail.PServerBakLocation FROM dbo.FTPdetail)+ dbo.FilesToBeSent.[FileName] Filename from dbo.FilesToBeSent where FilesToBeSent.IsDataTransfered=0"

objReader = objCommand.ExecuteReader()

Dim objFilesList As New System.Collections.Specialized.StringCollection

While objReader.Read()
objFilesList.Add(objReader.Item("filename").ToString)
End While

objReader.Close()

'Build a array of all the file names that is going to be FTP'ed (in this case only one file)
'Dim files(0) As String
'files(0) = objReader.Item("PServerBakLocation").ToString() 'this is the file name and location for source
'ftp the file
'Note: I had a hard time finding the remote path directory. I found it by mistake by creating both the FTP connection and task in the SSIS package and it defaulted the remote path setting in the FTP task.
Dim sFilesArray(0) As String
Dim sFile As String
Dim sFileTransferValue As String
Dim sErrorReason As String

ftp.Connect() '= False Then

For Each sFile In objFilesList
'sFilesArray(objFilesList.IndexOf(sFile)) = sFile
sFilesArray(0) = sFile

Try
ftp.SendFiles(sFilesArray, "/user/backup", True, False) ' the True makes it overwrite existing file and False is saying that it is not transferring ASCII
sFileTransferValue = "1"
sErrorReason = ""
Catch Ex As Exception
sErrorReason = "Error: " + Ex.Message.ToString()
sFileTransferValue = "0"
End Try

objCommand.CommandText = "UPDATE FilesToBeSent SET ErrorReason = '" + sErrorReason + "', IsDataTransfered = " + sFileTransferValue + " WHERE FILEName = '" + Path.GetFileName(sFile) + "'"
objCommand.ExecuteNonQuery()
Next

ftp.Close()

Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.Log(ex.Message, 0, Nothing)
Dts.TaskResult = Dts.Results.Failure
Finally
If objCon.State = ConnectionState.Open Then
objCon.Close()
End If
If objCon IsNot Nothing Then
objCon.Dispose()
End If
End Try

'System.Console.ReadLine()
'==============================================================

End Sub

End Class

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources