SSIS Package to load contents of CSVs to SQL Table
- DaveBriCam wrote:
It's simply not there
Do you have all the other Data Flow tools? Conditional Split, Merge, Multicast etc?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Sorry it's there when I go into Data Flow... my bad
-
Found the Derived Column Transformation but do I type in my SQL to get the Date from the file name into the "Expression"?
- DaveBriCam wrote:
Found the Derived Column Transformation but do I type in my SQL to get the Date from the file name into the "Expression"?
No, you need to use SSIS's own expression language (which is a bit like C#). What is the format of your file names (hopefully, there is a common format) and what result do you want to return?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
ConvoEVQ2021-03-22 is the convention of the .csv file names with only the month and day changing. Here I only want to return 2021-03-22 as the value that will be going in the Date field in my SQL Server table.
-
This should give you an idea.
In the Variables pane, I created a variable called FileName and assigned its value.
Then I created the variable FileNameDate and assigned its value as an expression.
The expression simply extracts the final 10 characters of the first part of the filename (before the .)
If you replace User::FileName with your dynamic filename variable in the above expression, it will re-evaluate for each loop iteration.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
What do you mean by "dynamic filename variable"?
-
In your Foreach container, you will configure a variable to be assigned the name (optionally the entire path) of the file being processed on the 'current' loop iteration. That is what I meant.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I do this all the time, we input files that have the date in the file name. I use a for each loop on the Control Flow and retrieve the file name into a variable. Then use a script task to reformat the date and save it to a date variable. In the Data Flow you can use a Derived Column task to add it to your output columns.
-
How do you get part or all the file name into the variable?
- DaveBriCam wrote:
How do you get part or all the file name into the variable?
You do that in your ForEach Loop Container. When you create this, you have to define what part of the file's name you want to store in the variable (i.e. Full Path, File Name, etc), and then define which variable that is stored in.
For example, in my ForEach Loop I am putting the fully qualified value in the variable
User::FilePath:Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
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.
- DaveBriCam wrote:
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.
What do you want this ExecSQL task to do?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 16 through 30 (of 55 total)
You must be logged in to reply to this topic. Login to reply