Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Batch ETL of Multiple Data Files Using an SSIS Foreach Loop Container Expand / Collapse
Author
Message
Posted Monday, June 17, 2013 12:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 27, 2014 11:51 AM
Points: 30, Visits: 61
The "Move File" operation will not allow you to change the name so the destination can only contain a path to the file folder without the file name. If you want to move it and give it a different name at the same time then you need to use the "Rename File" operation. It was confusing to me at first and I still don't understand why they chose to give this functionality to the "Rename File" operation instead of giving it to the "Move File" one or even create another called something like "Move and Rename File".
Post #1464308
Posted Monday, June 17, 2013 2:44 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:59 PM
Points: 902, Visits: 2,426
Doing it as a rename worked. I'm all for using code, but if a non-code solution will do, I'll use that instead. Thanks,


Post #1464383
Posted Thursday, June 20, 2013 12:39 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 20, 2015 2:33 PM
Points: 251, Visits: 352
I've used both Script Tasks and File System Components to perform file manipulation but try to use native SSIS components to perform an operation when available meaning I tend towards the File System Task unless there's specific reason not to.

Because the Script tasks are basically just wrappers for C#/VB .net code you could effectively write a whole application to perform any operation all the way down to a full GUI if desired, but that's not really what it was intended for. The script task is very powerful but it obfuscates the tasks that it performs by removing the logic from SSIS and placing it in a different environment. It's more a personal preference than anything but I just like being able to follow the logic in a package without having to open up a script task, open the script in a separate Visual Studio code window, look at which DLLs were loaded to perform specialized operations, try to figure out which variables are passed back and forth etc.

Regarding the loading of multiple files of the same format one thing that has been very handy in certain circumstances is the Multiple Flat File connection manager. You can use the same kind of wildcard matching that you'd use in a for each file loop and treat any number of like-formatted txt files as a single file. It's especially handy if your data flows include lookups or other operations that have to occur every time the data flow is loaded since they will only occur once as opposed to every iteration of the outside loop. Not a solution in all cases since you wouldn't be able to do file-by-file archiving as easily but useful nonetheless.

Kris
Post #1465896
Posted Monday, January 26, 2015 2:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 11:34 AM
Points: 4, Visits: 112
I tried the code that was suggested in the visual basic 2008 script and get an error for the following statement:

Dim input_file As New FileInfo(Dts.Variables("FilePath").Value.ToString)

The error is "Type 'FileInfo' is not defined."

Do you have an answer for that?
Post #1654869
Posted Monday, January 26, 2015 3:23 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 20, 2015 9:50 AM
Points: 134, Visits: 967
Add "Import IO.System" to the import section of the script task.
Post #1654877
Posted Monday, January 26, 2015 3:42 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, March 16, 2015 3:30 PM
Points: 630, Visits: 163
To be able to use FileInfo, you have to import library reference Microsoft.SqlServer.SmoExtended if you are using SQL2008 or SQL2008R2 and then use it in your Script Main window by Using Microsoft.SqlServer.Management.Smo;. You might want to bring Microsoft.SqlServer.Management.Collector and Microsoft.SqlServer.Management.Common as well.
Post #1654885
Posted Monday, January 26, 2015 4:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 11:34 AM
Points: 4, Visits: 112
When I try adding Imports IO.System to the visual basic 2008 script contained in this article, I get the following error:

Namespace or type specified in the Imports 'IO.System' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases.

Has anyone actually tried to run the script by Stan Kulp "Batch ETL of Multiple Data Files Using an SSIS Foreach Loop Container" ?
Post #1654892
Posted Monday, January 26, 2015 4:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 11:34 AM
Points: 4, Visits: 112
When I try adding Imports Microsoft.SqlServer.SmoExtended to the visual basic 2008 script contained in this article, I get the following error:

Namespace or type specified in the Imports 'IO.System' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases.

Any other ideas?
Post #1654894
Posted Tuesday, January 27, 2015 8:09 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 20, 2015 9:50 AM
Points: 134, Visits: 967
I just ran it and it worked perfectly. I wrote it in 2008, but just made it work in 2012.

What version of SSIS are you using? Did you use the BatchETL.dtsx file in the BatchETL.zip resources file?
Post #1655058
Posted Tuesday, January 27, 2015 9:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 11:34 AM
Points: 4, Visits: 112
I'm using version 2008 R2. In my SSIS package I have a for each loop container that reads multiple xml files and inserts data into sql tables. That works fine. The last step in the for each loop is a Script Task for archiving the XML files to another folder. So, I copied exactly the code in your example. The only change I made was the connection for the archive folder which is the Dim DestinationDirectory. One other change was instead of:

Dim input_file As New FileInfo(Dts.Variable("FilePath").Value.ToString)

I used:

Dim input_file As New IO.FileInfo(Dts.Variable("FilePath").Value.ToString)

Without making that change I was getting the error: "Type InfoFile is not defined"

Now I'm getting this error during execution:

Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: The path is not of a legal form.
at System.IO.Path.NormalizePathFast(String path, Boolean fullCheck)
at System.IO.FileInfo..ctor(String fileName)
at ST_b1430fd9112f48ef96919934e7029f42.vbproj.ScriptMain.Main()
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()




Here is my script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime


<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum


Public Sub Main()

Dim DestinationDirectory As String = "T:\Developers\Ron.Dore\CyberCom\CyberComXMLfilesArchive\"
Dim file_name As String() = Split(Dts.Variables("FilePath").Value.ToString, "\")

Dim input_file As New IO.FileInfo(Dts.Variables("FilePath").Value.ToString)

Try
input_file.MoveTo(DestinationDirectory & file_name(2))
Catch ex As Exception
End Try

End Sub

End Class



Thanks for your help.
Post #1655097
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse