SSIS Package to load contents of CSVs to SQL Table

Question

SSIS Package to load contents of CSVs to SQL Table

Viewing 15 posts - 31 through 45 (of 55 total)

You must be logged in to reply to this topic. Login to reply

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 1

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"

Thom A SSC Guru Points: 99130 More actions · Answer 2

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.

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

Thom A SSC Guru Points: 99130 More actions · Answer 3

DaveBriCam wrote:

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

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 4

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

Thom A SSC Guru Points: 99130 More actions · Answer 5

DaveBriCam wrote:

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

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 6

DaveBriCam

SSCarpal Tunnel

Points: 4298

April 19, 2021 at 4:09 pm

#3873145

So I chose just the first .csv file in the list?

wburke 85918 SSChasing Mays Points: 619 More actions · Answer 7

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 8

Please take a read through here for a detailed walkthrough.

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

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 9

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?

Thom A SSC Guru Points: 99130 More actions · Answer 10

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 11

Thom A wrote:

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.

Did you mean RIGHT(User::FileNameVariable,10) there? Notwithstanding that, your comment stands.

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

Thom A SSC Guru Points: 99130 More actions · Answer 12

Phil Parkin wrote:

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

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 13

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.

Thom A SSC Guru Points: 99130 More actions · Answer 14

DaveBriCam wrote:

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

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 15

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