Solved

SSIS Visual Studio DTSX Question

Posted on 2008-10-23
27
1,761 Views
Last Modified: 2013-11-10
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
Comment
Question by:tomasdlv
  • 12
  • 9
  • 6
27 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22792124
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
 

Author Comment

by:tomasdlv
ID: 22792158
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22794702
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
 

Author Comment

by:tomasdlv
ID: 22797863
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22797927
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
 

Author Comment

by:tomasdlv
ID: 22797982
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22798092
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
 

Author Comment

by:tomasdlv
ID: 22798142
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22798321
OK, made some changes and comments. See attached.
Copy-of-sample.dtsx.txt
0
 

Author Comment

by:tomasdlv
ID: 22798633
hey hoggzilla, it tells me package is corrupt.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22798642
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
 

Author Comment

by:tomasdlv
ID: 22798659
pedro i did as you said i get the error of "cannot convert between unicode and non-unicode string data types"
0
 

Author Comment

by:tomasdlv
ID: 22798830
here i'll send you my file. take a look please
Package3.dtsx.txt
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 17

Expert Comment

by:HoggZilla
ID: 22799192
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22799204
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
 

Author Comment

by:tomasdlv
ID: 22799221
ok now i get no errors but i go to sql and there is no data in the table
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22800262
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22811145
tomas,
you must map the input rows to output rows inside OLEDB Destination.
Check it and give feedback!
regards!
Pedro
0
 

Author Comment

by:tomasdlv
ID: 22812719
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22812947
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22812967
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
 

Author Comment

by:tomasdlv
ID: 22813076
how did you do this? i have to set this to another folder.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22813131
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
 

Author Comment

by:tomasdlv
ID: 22813294
forgive me but i still dont see how exactly to create the variable...is there like a step by step somewhere?
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22813352
No problem! :-)
To create the variable is simple... in the controlflow, right-click with mouse and select Variables
helped?
0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 500 total points
ID: 22813388
This is pretty good SSIS tutorial.
http://www.accelebrate.com/sql_training/ssis_tutorial.htm
 
0
 

Author Closing Comment

by:tomasdlv
ID: 31509321
thx
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dynamics crm 2011 8 45
MS SQL export CSV & schedule It 9 44
Help with SQL Query 23 39
SQL Server creating a temp table 7 38
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now