SSIS Package to load contents of CSVs to SQL Table
-
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?
- 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 see31/03/2021.Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - 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?
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 -
Currently in the package it is mapping to NVARCHAR(50) but what if I wanted to map to a field that had DATE datatype?
-
(DT_DATE) should do the job.
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 -
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?
- 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.
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 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.
-
I solved the above by simply changing the datatype in SQL Server table to NVARCHAR... again thanks for all the help!
- 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):
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 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
nvarchartovarchar) 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
Viewing 11 posts - 46 through 55 (of 55 total)
You must be logged in to reply to this topic. Login to reply