• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1782
  • Last Modified:

SSIS Visual Studio DTSX Question

i have a folder that recieves files via ftp. these files are txt files all named differently every time and different data but in the same csv format every time. i need ssis to be able to look into this folder and look for i guess *.txt files and perform the tasks i assign afterward. is this possible?
0
tomasdlv
Asked:
tomasdlv
  • 12
  • 9
  • 6
1 Solution
 
HoggZillaCommented:
Funny! I just explained the basic concept for this right here. It involves a Foreach Loop Container, some variables, expressions and Script Task to do what you want with the files. In this example I get the File Creation Date and loop through to get the most recent file. But you could use the process for anything with the file.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_23837718.html?cid=238#a22787803
If you have a specific question about this, let me know. Good luck!
0
 
tomasdlvAuthor Commented:
ok excellent...listen im not at work right now but first thing tomorrow i'll try this and let you know so keep an eye out and i'll let you know how it went...thanks
0
 
PedroCGDCommented:
Dear Tomasdlv,
Look at this project I made for other user:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_23828561.html
Regards
Pedro
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
tomasdlvAuthor Commented:
Pedro,

i'm stuck on the derived column step...what are the steps? and secondly i have created a table in sql so how do i get the data into sql? what is the insert query?
0
 
HoggZillaCommented:
Hi tomasdlv,
Definately get that from Pedro, but I wanted to let you know I posted the dtsx solution here for reading files. Rename the package removing the .txt.

Package2.dtsx.txt
0
 
tomasdlvAuthor Commented:
hey hoggzilla,

thanks for getting back to me...i actually looked at your dtsx already and i dont see how this would apply to me. it simply renames the file in sequence. i need a package to blindly look at a folder and pick up any txt files and dump them into a table i've created in sql.
0
 
HoggZillaCommented:
Actually the package does the following, it does not rename a file. It looks at a directory for every file matching a pattern, just like *.txt. It captures the filename and path so you can do whatever with it.
So, in your case, change the Expression definition for FileSpec to something dynamic or hardcode it to *.txt if that is static.
The name of every file in that folder that matches the FileSpec will be populated into a variable you then use in the Foreach Loop Container. Here is the screen shot of the Variable Mapping.
i need a package to blindly look at a folder and pick up any txt files and dump them into a table i've created in sql.
 OK, second part. Do you want to capture the contents of the file and write that to a table or just the filename? For writing the contents, I have attached the Script. It writes the contents to a variable and you inturn use a Execute SQL Task to save that variable to the table.
If it is just simple writing the filename to the table, just put a SQL Task in the Foreach Loop Container that reads the "LoopFileName" variable - or whatever you choose to call it, and inserts the variable contents to a table.
Are you familiar with using Parameters and Execute SQL Task?

'This will write File Contents to a Variable
'Script Task in SSIS
 
' 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
 
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()
        Dim errorInfo As String = ""
        Dim Contents As String = ""
 
        Contents = GetFileContents(CStr(Dts.Variables("FILENAME").Value), errorInfo)
        If errorInfo.Length > 0 Then
            MsgBox(errorInfo, MsgBoxStyle.Critical, "Error")
            Dts.TaskResult = Dts.Results.Failure
        Else
            Dts.Variables("FILE_CONTENTS").Value = Contents
            MsgBox(Contents, MsgBoxStyle.OkOnly, "File contents")
            MsgBox(Dts.Variables("FILE_CONTENTS").Value, MsgBoxStyle.OkOnly, "Variable Contents")
            Dts.TaskResult = Dts.Results.Success
        End If
    End Sub
 
 
    Public Function GetFileContents(ByVal filePath As String, Optional ByVal ErrorInfo As String = "") As String
        Dim strContents As String
        Dim objReader As IO.StreamReader
        Try
            objReader = New IO.StreamReader(filePath)
            strContents = objReader.ReadToEnd()
            objReader.Close()
            Return strContents
        Catch Ex As Exception
            ErrorInfo = Ex.Message
        End Try
    End Function
 
 
 
 
 
End Class

Open in new window

foreach-loop-variable.bmp
0
 
tomasdlvAuthor Commented:
hey,

take a look if you can at the sample package i attached. let me know if this is right.

thanks hogg
sample.dtsx.txt
0
 
HoggZillaCommented:
OK, made some changes and comments. See attached.
Copy-of-sample.dtsx.txt
0
 
tomasdlvAuthor Commented:
hey hoggzilla, it tells me package is corrupt.
0
 
PedroCGDCommented:
Tomas,
derived column is only for debug purposes to check the path of each file.
To insert data into SQL, you only need a OLEDB Destination and link source to destination and map columns!
If you need some more help, let me know!
Regards!
pedro
0
 
tomasdlvAuthor Commented:
pedro i did as you said i get the error of "cannot convert between unicode and non-unicode string data types"
0
 
tomasdlvAuthor Commented:
here i'll send you my file. take a look please
Package3.dtsx.txt
0
 
HoggZillaCommented:
I tried to add it again. It seem fine when i donwloaded it back. Be sure to save it, removing the .xml or .txt extenstion. It should be Sample.dtsx.
sample.dtsx.txt
0
 
HoggZillaCommented:
For the unicode - non-unicode conversion error. Use a Data Conversion task instead of the Derived Column task. Make sure the database is getting the correct column types.
0
 
tomasdlvAuthor Commented:
ok now i get no errors but i go to sql and there is no data in the table
0
 
HoggZillaCommented:
Before you can debug the Data Flow Task, you need to pull it out of this process. The original question here was just getting the files from a folder. We can do that. Now, moving the data from each file to a database table is another step.
In the package file I attached I created an expression to hold the file connection string for the source Flat File (fx). When you setup the data flow task for the first time, including the mapping between destination Flat File delimited columns and source OLE DB table columns, this has to be operational first. Did you get this to work before you tried creating a dynamic task to read any number of files in one run?
If not, get this to work before you try to add a Foreach Loop Container. There are plenty of good examples on loading a Flat File in SSIS to a database. Here is one:
http://aspalliance.com/889_Extracting_Data_from_a_Flat_File_with_SQL_Server_2005_Integration_Services.2
If you want a solution here for reading a flat file into a SQL Table, I suggest you open another question and post a sample file. I would be glad to help.
0
 
PedroCGDCommented:
tomas,
you must map the input rows to output rows inside OLEDB Destination.
Check it and give feedback!
regards!
Pedro
0
 
tomasdlvAuthor Commented:
pedro, thanks this has been very helpful...just one more thing...it the foreach loop i have it set to look for any files in a specified folder but on the flat file source i have to specify one file it wont let me say *.* so what is happening it that the process works but it only imports the one file. i have 5 files in this folder and the process imports the same file 5 times.
0
 
HoggZillaCommented:
I don't know if you have looked at your dtsx package I modified and posted, but I explained it through notes in the actual package and set it up for you. The file name is saved from the loop into a variable. The variable is then used in an expression to define the Connection String property of the file.
0
 
PedroCGDCommented:
As hogg wrote, the connectionstring for each file is saved in a SSIS global variable to read each file in your folder.
Helped?
Regards
0
 
tomasdlvAuthor Commented:
how did you do this? i have to set this to another folder.
0
 
PedroCGDCommented:
You cann see here:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_23828561.html

And in the HoogZilla comments. (check the foreach loop image that Hogg set the absolute path of each file to a variable). Next in flatFile source you define the connectionstring of flatfile as expression, and here you map the connection string to the variable refered before.

So... you need 2 maps!
1. Map ForeachLoop path to variable XPTO
2. Map variable XPTO to flatFile connectionstring using expression property!

Helped? :-)
regards!
0
 
tomasdlvAuthor Commented:
forgive me but i still dont see how exactly to create the variable...is there like a step by step somewhere?
0
 
PedroCGDCommented:
No problem! :-)
To create the variable is simple... in the controlflow, right-click with mouse and select Variables
helped?
0
 
HoggZillaCommented:
This is pretty good SSIS tutorial.
http://www.accelebrate.com/sql_training/ssis_tutorial.htm
 
0
 
tomasdlvAuthor Commented:
thx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 12
  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now