Solved

SSIS Visual Studio DTSX Question

Posted on 2008-10-23
27
1,762 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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

920 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

17 Experts available now in Live!

Get 1:1 Help Now