April 19, 2021 at 3:14 pm
I want to insert the date part of the file name into a date field in my SQL table called "PyRlByPeriod" within connection "10.1.201.29.a2hr" which is my database. The file names are always in this format: "ConvoEVQ2021-04-05"
April 19, 2021 at 3:14 pm
Thanks... within my Loop container I placed an "Execute SQL Task Editor" but I'm having trouble with its needed syntax in the SQL Statement and the Parameter Mapping... something is making the package fail.
Why not use a Data Flow Task? Or is this Exec T-SQL Task doing something with the data after it is inserted by the Data Flow Task?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 19, 2021 at 3:16 pm
I want to insert the date part of the file name into a date field in my SQL table called "PyRlByPeriod" within connection "10.1.201.29.a2hr" which is my database. The file names are always in this format: "ConvoEVQ2021-04-05"
We're back at the start here; we've already explained earlier in the topic that you should be using a Data Flow Task and how to both use it and get the name of the file into the dataset you are inserting. Why have you gone back to using an Execute T-SQL Task and got rid of/not tried the Data Flow Task we said you should be using?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 19, 2021 at 3:58 pm
When I use the data flow task and the Source Assistant / Flat File Connection Manager Editor it will not allow me to choose multiple flat files with my folder
April 19, 2021 at 4:00 pm
When I use the data flow task and the Source Assistant / Flat File Connection Manager Editor it will not allow me to choose multiple flat files with my folder
But you're looping through them; so you wouldn't choose "multiple" to load simultaneously your For Each Loop Container would do the Data Flow for each file.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 19, 2021 at 4:09 pm
So I chose just the first .csv file in the list?
April 19, 2021 at 4:17 pm
Sorry it took so long to get back, weekend, Monday. All of my file name are formatted the same 2Q11_20210423_RRD_CT1_FILE. I do a Split on the file name inside a Scrip Task as the first step in the For each container.
Dim aryName() As String = Split(FileName, "_", 5)
If aryName(0) > "" Then
Dts.Variables("varCycleDate").Value = aryName(0).Substring(0, 4)
Else
blnStatusGood = False
GoTo Main_Exit
End If
Dim strDate As String = aryName(1).Substring(0, 4) + "-" + aryName(1).Substring(4, 2) + "-" + aryName(1).Substring(6, 2)
If IsDate(strDate) Then
Dts.Variables("varMail_Date").Value = CDate(strDate)
Else
blnStatusGood = False
GoTo Main_Exit
End If
April 19, 2021 at 8:04 pm
Thanks... this helped tremendously... right now it is however using the FileNameColumnName under "Component Properties" in my "Advanced Editor for Flat File Source" to deliver the full path when I just need to harvest the date part of the file name... any ideas?
April 20, 2021 at 8:20 am
Considering you said earlier:
The file names are always in this format: "ConvoEVQ2021-04-05"
Assuming that there is no extension, what's wrong left LEFT(User::FileNameVariable,10)? For the above that'll be 2021-04-05. You can wrap that in a convert to a date as well if you want, though yyyy-MM-dd is an unambiguous format for the new date and time data types.
If there is an extension, you should be able to tweak that easily enough with an additional RIGHT function.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 20, 2021 at 9:36 am
Considering you said earlier:
The file names are always in this format: "ConvoEVQ2021-04-05"
Assuming that there is no extension, what's wrong left
LEFT(User::FileNameVariable,10)? For the above that'll be2021-04-05. You can wrap that in a convert to a date as well if you want, thoughyyyy-MM-ddis an unambiguous format for the new date and time data types.If there is an extension, you should be able to tweak that easily enough with an additional
RIGHTfunction.
Did you mean RIGHT(User::FileNameVariable,10) there? Notwithstanding that, your comment stands.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 20, 2021 at 9:39 am
Did you mean RIGHT(User::FileNameVariable,10) there? Notwithstanding that, your comment stands.
I did, however, considering the time of the morning I can use the excuse the coffee hadn't kicked in. 😉
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 20, 2021 at 2:09 pm
I've gone back to the "Component Properties" in my "Advanced Editor for Flat File Source" to make this change and I seem to have forgotten how to put this formula in. Under "Custom Properties" it simply has FileNameColumnName as a property and SourceFileName as the value but I don't know where to put the expression in.
April 20, 2021 at 2:15 pm
I've gone back to the "Component Properties" in my "Advanced Editor for Flat File Source" to make this change and I seem to have forgotten how to put this formula in. Under "Custom Properties" it simply has FileNameColumnName as a property and SourceFileName as the value but I don't know where to put the expression in.
In the Derived Column Transformation.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 20, 2021 at 2:47 pm
Thanks! I was able to put in the formula in a Derived Column Transformation account for the extension with LEFT(RIGHT(@[User::FileNameDate],14),10).
Viewing 15 posts - 31 through 45 (of 55 total)
You must be logged in to reply to this topic. Login to reply