SSIS Package to load contents of CSVs to SQL Table

Question

SSIS Package to load contents of CSVs to SQL Table

Phil Parkin

SSC Guru

Points: 246693
More actions
April 20, 2021 at 4:29 pm
Answer
#3873565

(DT_DATE) should do the job.
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.

Viewing 11 posts - 46 through 55 (of 55 total)

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

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 1

One more thing I changed my expression to (DT_Date)(LEFT(RIGHT(@[User::FileNameDate],14),10)) to convert to a date but it returns format like: 3/31/2021 (?) Are there other ways to convert the date?

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

DaveBriCam wrote:

One more thing I changed my expression to (DT_Date)(LEFT(RIGHT(@[User::FileNameDate],14),10)) to convert to a date but it returns format like: 3/31/2021 (?) Are there other ways to convert the date?

Dates don't have a format; the format you see is purely based on the application. Presumably you are American, and thus VS is defaulting to display the value in the format M/d/yyyy. For myself, who is English, I would likely see 31/03/2021.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246693 More actions · Answer 3

DaveBriCam wrote:

One more thing I changed my expression to (DT_Date)(LEFT(RIGHT(@[User::FileNameDate],14),10)) to convert to a date but it returns format like: 3/31/2021 (?) Are there other ways to convert the date?

What is the datatype of the column you are inserting to?

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.

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 4

Currently in the package it is mapping to NVARCHAR(50) but what if I wanted to map to a field that had DATE datatype?

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 5

Also if I wanted to pass a literal string to a SQL field via SSIS do I just add another variable and assign the literal value to pass in with an additional Derived Column?

Phil Parkin SSC Guru Points: 246693 More actions · Answer 6

DaveBriCam wrote:

Also if I wanted to pass a literal string to a SQL field via SSIS do I just add another variable and assign the literal value to pass in with an additional Derived Column?

Yes. Or if the string is completely static, I'd suggest using a parameter rather than a variable.

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.

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 7

I created a parameter and gave it a value. Can I use my existing Derived Column to pass the parameter's value into one of my SQL fields? I gave it a try but ran into the "cannot convert between unicode and non-unicode" error but I'm not sure if it happened when i mapped to a SQL destination field or in the Derived Column properties, either way I'm finding no way to edit the type . Of course the parameter is type string.

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 8

I solved the above by simply changing the datatype in SQL Server table to NVARCHAR... again thanks for all the help!

Phil Parkin SSC Guru Points: 246693 More actions · Answer 9

DaveBriCam wrote:

I solved the above by simply changing the datatype in SQL Server table to NVARCHAR... again thanks for all the help!

Well done, but for future reference note that you can do the CAST from NVARCHAR(n) to VARCHAR(n) from within the Derived Column definition, like this (change 10 to your desired string length):

2021-04-20_18-40-14

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.

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

DaveBriCam wrote:

I created a parameter and gave it a value. Can I use my existing Derived Column to pass the parameter's value into one of my SQL fields? I gave it a try but ran into the "cannot convert between unicode and non-unicode" error but I'm not sure if it happened when i mapped to a SQL destination field or in the Derived Column properties, either way I'm finding no way to edit the type . Of course the parameter is type string.

The reason you got this error, by the way, is SSIS is far less leniant that SQL on things like implicit conversion and truncation; in that doesn't allow either. As such, if you are converting from one data type to another (in this case from nvarchar to varchar) you must be explicitly converting or the error will occur. For truncation, there are normally options to disable truncation errors on your transformations, destinations, etc.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk