December 5, 2008 at 10:22 am
Hey i need to use part of my imported file names and add them into a table i've about 20 file each day with the format of
xxx_yyyymmdd_z.txt and xxx_yyyymmdd_zz.txt all the file will go into one directory i tried using substring but i need all the files have have the same name for it to work
The part i want from the files are the z and zz
Anyone any ideas am trying to do this in a dirived column is ssis.
December 5, 2008 at 12:48 pm
Hi there,could you explain more on what you are trying to accomplish ?
All files come into a single dir and have diff names, you would like to store file names into separate tables based on part of the file name !..Not sure If I got it correct ?
December 8, 2008 at 2:41 am
I've a foreach loop in ssis, that loops through a dir that has about 30 files in it. The format of the file name is xxx_yyyymmdd_z.txt and xxx_yyyymmdd_zz.txt
I need to idintify the files as there is no way to identify them by there content, So am using the file name to do this. The part of the file name "xxx_yyyymmdd_zz.txt" am using is the z or zz in some cases.
Example of files
xxx_yyyymmdd_z.txt"
xxx_yyyymmdd_zz.txt"
xxx_yyyymmdd_v.txt"
xxx_yyyymmdd_vv.txt"
xxx_yyyymmdd_j.txt"
xxx_yyyymmdd_jj.txt"
xxx_yyyymmdd_u.txt"
xxx_yyyymmdd_uu.txt"
Table format will have a column for each file name as well as the file content.
File_name_col
z
zz
v
vv
j
jj
u
uu
December 8, 2008 at 9:16 am
You loop over all .txt files below and only want to pick file's that have matching end characters as in your table column below,regardless of the first "xxx_yyyymmdd" characters. Is this correct ?
xxx_yyyymmdd_z.txt"
xxx_yyyymmdd_zz.txt"
xxx_yyyymmdd_v.txt"
xxx_yyyymmdd_vv.txt"
xxx_yyyymmdd_j.txt"
xxx_yyyymmdd_jj.txt"
xxx_yyyymmdd_u.txt"
xxx_yyyymmdd_uu.txt"
Table format will have a column for each file name as well as the file content.
File_name_col
z
zz
v
vv
j
jj
u
uu
December 8, 2008 at 9:27 am
If all your filenames are consistent then you should be able to use a combination of FINDSTRING to find the position of the last '_' and then use SUBSTRING to strip out the relevant information
December 8, 2008 at 12:49 pm
Steve,
The method that I could think of is directly using a Script Task that performs the below.
- Read the single column from the sql table and create an array of fileName as below
fileName(j) = "xxx" + "_" + dt.ToString("yyyyMMdd") + "_" + sqlRead("FileName") + ".txt"
- Loop over the file names in the folder and compare each file name to the above constructed fileName array.AND if there is match perform necessary operation to the file[Example movement of the matching file's to another folder].
** The above is with the assumption of picking up only files with current date . 20081208
Im interested to know how I can accomplish the above with SUBSTRING .What transformation would I need to used once you loop over the file's using for each loop container.Appreciate if you could explain .Thanks
December 9, 2008 at 3:50 am
Edit the foreach loop container.
On the TAB for Variable Mappings there shoul be a variable to hold the fully qualified file name is set. By fully qualified i.e. “C:\FEL\file.csv”
Now add a script task in the loop.
the input parameter will be set to the same vairiable that holds the file name.
I have posted the script task contents below from my use of this:
(I wanted to get the filename with no extension or path into a variable)
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
Public Sub Main()
'-------------------------------
' Author: Ells
' Date: 3rd December
' Variables:
' filename, tablename
'-------------------------------
Dim oFileName As String = ""
Dim oTableName As String = ""
Dim oString As String = ""
Dim i As Integer = 0
Dim oFileNameLength As Integer = 0
Dim SubStringLength As Integer = 0
' Capture name of file and length of fully qualified name
oFileName = Dts.Variables("filename").Value.ToString
oFileNameLength = oFileName.Length
'MsgBox("1.FileName: " + oFileName)
' Reverse the file name and take only the 5th and subesequent charcters ie drop the '.csv'
oString = StrReverse(oFileName)
'MsgBox("Reveresed: " + oString.ToString)
oFileNameLength = oFileNameLength - 4
oString = Mid(oString, 5, oFileNameLength)
'MsgBox("2.the full qualified without extension: " + oString)
'MsgBox("3.oFileNameLength: " + oFileNameLength.ToString())
' we have removed the extension,
' now loop from the strat to find the folder \ in the filename
For i = 1 To oFileNameLength
If Mid(oString, i, 1) <> "\" Then
oTableName = oTableName + Mid(oString, i, 1)
Else
i = oFileNameLength
End If
Next
oTableName = StrReverse(oTableName)
Dts.Variables("tablename").Value = oTableName.ToString
MsgBox(oTableName.ToString)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
March 10, 2011 at 3:31 am
How could i get the filename in package variable defined. I need folder name also.
Basically I try to insert data from excel to sql table. Along with the data, the filename and foldername has to be inserted in the table.
How can we insert the value in variable to sql table. I tried derived column. Getting this error "The destination component does not have any available inputs for use in creating a path."
Thanks for your time
regards
KRS
March 10, 2011 at 3:46 am
krishnaroopa (3/10/2011)
How could i get the filename in package variable defined. I need folder name also.Basically I try to insert data from excel to sql table. Along with the data, the filename and foldername has to be inserted in the table.
How can we insert the value in variable to sql table. I tried derived column. Getting this error "The destination component does not have any available inputs for use in creating a path."
Thanks for your time
regards
KRS
If you are using a for each loop, you can store the full path to the Excel file into a variable.
You can parse this variable into two variables that contain the folder and the filename.
You can add the values of these two variables to the data using a derived column. If this doesn't work, you probably didn't configure it correctly.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 10, 2011 at 3:53 am
I'd use a derived column, using something like this: -
REVERSE(SUBSTRING(REVERSE(@[User::varFileName]),1,FINDSTRING(REVERSE(@[User::varFileName]),"_",1) - 1))
That'll include the extension, so add another substring in there to remove it.
After that, I'd have a conditional split that checked what was in the new derived column, if it was the allowed parts I'd load into a SQL destination - otherwise I'd output to a reject csv file.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply