Solved

SSIS Visual Studio DTSX Question

Posted on 2008-10-23
27
1,768 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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
 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

617 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