Data coversion or Derived column?

I have a numeric column with the following sample values in a source flat file:

240

6

48

310

55

I would like to dump them in a table (destination) as string with the length only 3 and in the following format "xxx" .

Data in the destination column will look like this after the transformation:

240

006

048

310

055

Thanks for your help!

milton06 wrote:

I have a numeric column with the following sample values in a source flat file:

240

6

48

310

55

I would like to dump them in a table (destination) as string with the length only 3 and in the following format "xxx" .

Data in the destination column will look like this after the transformation:

240

006

048

310

055

Thanks for your help!

You'll need a derived column component for that. Here's the expression

RIGHT("000" + (DT_STR, 3, 1252)[columnname], 3)

-Jamie

|||Excellent Jamie.|||

milton06 wrote:

Excellent Jamie.

Please mark his post as the answer if that solved your problem.

Thanks,
Phil

Data Conversion Error on Excel Destination

I am inserting rows using OLEDBDestination and want to redirect all error rows to EXCEL Destination.

I have used Data Conversion Transformation to Convert all strings to Unicode string fields before sending it to Excel Destination.

But its gives the following error.

[Data Conversion [16]] Error: Data conversion failed while converting column 'A' (53) to column "Copy of A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.

Can someone please tell me what should I do to make it work?

Thanks,

Did you set the error output of the OLE Destination to "Redirect Rows"?

You might also have metadata problems and could maybe stand to recreate the Excel destination.|||

I have set the error ouput to redirect rows

Actually I have a Data flow task with Oledbsource -->Oledbdestination (Redirect Error Rows ) -- >Data Conversion -- >Excel Destination.

When I do this its gives me the following error.

Data conversion failed while converting column "A" (53) to column "A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABL

[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.

But for this data flow task Oledbsource -- > Data Conversion - > Excel Destination. Its works fine.

I am doing the same data conversion in both data flow tasks.

Please let me know what I am doing wrong.

|||Well, I suppose check and double check your column mappings going into and out of the OLE DB destination.|||

prg wrote:

I am inserting rows using OLEDBDestination and want to redirect all error rows to EXCEL Destination.

I have used Data Conversion Transformation to Convert all strings to Unicode string fields before sending it to Excel Destination.

But its gives the following error.

[Data Conversion [16]] Error: Data conversion failed while converting column 'A' (53) to column "Copy of A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.

Can someone please tell me what should I do to make it work?

Thanks,

Actually I would check the Data conversion transform. Specifically the column A; since is there where the error is being generated. Try placing a data view to inspected the values that go into the data conversion. What happens if you delete column A from the data conversion? Does it fail in a different column?

|||

I have checked the Data conversion transform again. I have removed column A and used another column. Still it fails.

This is kind of weird. I cant figure out where the problem is .

Is it that EXCEL Destination cannot be used to redirect rows that have errors? Because its seems to work perfectly when I do OLEDBSource -> Data Conversion ->Excel Destination.

|||Well, if the errors are created (redirected) because of conversion errors, then it is natural that the redirected error rows don't match your destination data types. They can't because that's why they are in error.

Make sure that the data types of the Excel destination match the data types of your SOURCE data, not the data types of the OLE DB Destination table. Not sure if this will work because the metadata probably won't match...|||

Hi all,

I think I have exactly the same problem using a sql destination for error output.

Error: 0xC02020C5 at GS_ORDREC, Data Conversion [1827]: Data conversion failed while converting column "OL_DONORD" (102) to column "Test" (1880). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

Error: 0xC0209029 at GS_ORDREC, Data Conversion [1827]: The "output column "Test" (1880)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Test" (1880)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at GS_ORDREC, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (1827) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

SSIS package "GS_ODSREC.dtsx" finished: Failure.

-

I check and recheck the package, everything is ok. Strange thing, when I try to convert the "ErrorColumn" field, the package works fine.

Regards

Ayzan

|||

Ayzan wrote:

Hi all,

I think I have exactly the same problem using a sql destination for error output.

Error: 0xC02020C5 at GS_ORDREC, Data Conversion [1827]: Data conversion failed while converting column "OL_DONORD" (102) to column "Test" (1880). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

Error: 0xC0209029 at GS_ORDREC, Data Conversion [1827]: The "output column "Test" (1880)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Test" (1880)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at GS_ORDREC, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (1827) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

SSIS package "GS_ODSREC.dtsx" finished: Failure.

-

I check and recheck the package, everything is ok. Strange thing, when I try to convert the "ErrorColumn" field, the package works fine.

Regards

Ayzan

Okay, you *don't* have the same problem as the OP. If you are using a SQL Server destiantion, SSIS will not automatically cast/convert datatypes for you. The data flow datatypes (metadata) will need to match EXACTLY with the SQL Server destination table.|||

Thank you Phil Brammer,

Sorry this IS the same problem, cause I try with a Sql server destination, a data reader destination, a flat file destination, for redirecting the failed rows, and I have still the same message ... in spite of casting/converting datatypes

Regards

Ayzan

|||

Ayzan wrote:

Thank you Phil Brammer,

Sorry this IS the same problem, cause I try with a Sql server destination, a data reader destination, a flat file destination, for redirecting the failed rows, and I have still the same message ... in spite of casting/converting datatypes

Regards

Ayzan

Ah, see, you left out valuable information! So I'll ask this as I have done before. Before we continue, please provide the metadata information as it stands before going into the destination. Then, please provide the table column information for the destination as well.

One other thing to note is that there could be one bad row that is causing this error... Have you redirected errors and inspected them?|||

Ok,

There is a simple Data Flow, so as to reproduce the error:

#Data Source, OLEDB, AdventureWorksDW

#OLE DB Source, SQL Command :

SELECT TOP (10) TimeKey, OrganizationKey, DepartmentGroupKey, ScenarioKey, AccountKey+100 as AccountKey, Amount
FROM FactFinance

#OLE DB Destination

AdventureWorks.FactFinance

#Error Output to Data Conersion

TimeKey -> DT_STR

#DataReader Destination

Enjoy !

|||

Ayzan wrote:

Ok,

There is a simple Data Flow, so as to reproduce the error:

#Data Source, OLEDB, AdventureWorksDW

#OLE DB Source, SQL Command :

SELECT TOP (10) TimeKey, OrganizationKey, DepartmentGroupKey, ScenarioKey, AccountKey+100 as AccountKey, Amount
FROM FactFinance

#OLE DB Destination

AdventureWorks.FactFinance

#Error Output to Data Conersion

TimeKey -> DT_STR

#DataReader Destination

Enjoy !

I don't have a AdventureWorks.FactFinance table. I have the data warehouse table... Are you creating your own FactFinance table in the AdventureWorks table? The mere prefix of "Fact" indicates that it should be in AdventureWorksDW.|||

Sorry,

#OLE DB Destination

AdventureWorksDW.FactFinance

Well in fact, whatever the source/destination, you just have to generate an error while trying to insert new records.

Regards

Ayzan

|||

Ayzan wrote:

Sorry,

#OLE DB Destination

AdventureWorksDW.FactFinance

Well in fact, whatever the source/destination, you just have to generate an error while trying to insert new records.

Regards

Ayzan

I'm utterly confused now. So your source and destination tables are the same? What are you doing exactly, and what are you expecting to see? Why would I select the top 10 records from FactFinance and then turn around and insert them again? Regardless, nowhere should a DT_STR datatype be picked up on the Timekey field because it is an integer field.

Nevermind. I understand now. Hang on.

Data Conversion Error on Excel Destination

I am inserting rows using OLEDBDestination and want to redirect all error rows to EXCEL Destination.

I have used Data Conversion Transformation to Convert all strings to Unicode string fields before sending it to Excel Destination.

But its gives the following error.

[Data Conversion [16]] Error: Data conversion failed while converting column 'A' (53) to column "Copy of A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.

Can someone please tell me what should I do to make it work?

Thanks,

Did you set the error output of the OLE Destination to "Redirect Rows"?

You might also have metadata problems and could maybe stand to recreate the Excel destination.|||

I have set the error ouput to redirect rows

Actually I have a Data flow task with Oledbsource -->Oledbdestination (Redirect Error Rows ) -- >Data Conversion -- >Excel Destination.

When I do this its gives me the following error.

Data conversion failed while converting column "A" (53) to column "A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABL

[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.

But for this data flow task Oledbsource -- > Data Conversion - > Excel Destination. Its works fine.

I am doing the same data conversion in both data flow tasks.

Please let me know what I am doing wrong.

|||Well, I suppose check and double check your column mappings going into and out of the OLE DB destination.|||

prg wrote:

I am inserting rows using OLEDBDestination and want to redirect all error rows to EXCEL Destination.

I have used Data Conversion Transformation to Convert all strings to Unicode string fields before sending it to Excel Destination.

But its gives the following error.

[Data Conversion [16]] Error: Data conversion failed while converting column 'A' (53) to column "Copy of A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.

Can someone please tell me what should I do to make it work?

Thanks,

Actually I would check the Data conversion transform. Specifically the column A; since is there where the error is being generated. Try placing a data view to inspected the values that go into the data conversion. What happens if you delete column A from the data conversion? Does it fail in a different column?

|||

I have checked the Data conversion transform again. I have removed column A and used another column. Still it fails.

This is kind of weird. I cant figure out where the problem is .

Is it that EXCEL Destination cannot be used to redirect rows that have errors? Because its seems to work perfectly when I do OLEDBSource -> Data Conversion ->Excel Destination.

|||Well, if the errors are created (redirected) because of conversion errors, then it is natural that the redirected error rows don't match your destination data types. They can't because that's why they are in error.

Make sure that the data types of the Excel destination match the data types of your SOURCE data, not the data types of the OLE DB Destination table. Not sure if this will work because the metadata probably won't match...|||

Hi all,

I think I have exactly the same problem using a sql destination for error output.

Error: 0xC02020C5 at GS_ORDREC, Data Conversion [1827]: Data conversion failed while converting column "OL_DONORD" (102) to column "Test" (1880). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

Error: 0xC0209029 at GS_ORDREC, Data Conversion [1827]: The "output column "Test" (1880)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Test" (1880)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at GS_ORDREC, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (1827) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

SSIS package "GS_ODSREC.dtsx" finished: Failure.

-

I check and recheck the package, everything is ok. Strange thing, when I try to convert the "ErrorColumn" field, the package works fine.

Regards

Ayzan

|||

Ayzan wrote:

Hi all,

I think I have exactly the same problem using a sql destination for error output.

Error: 0xC02020C5 at GS_ORDREC, Data Conversion [1827]: Data conversion failed while converting column "OL_DONORD" (102) to column "Test" (1880). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

Error: 0xC0209029 at GS_ORDREC, Data Conversion [1827]: The "output column "Test" (1880)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Test" (1880)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at GS_ORDREC, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (1827) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

SSIS package "GS_ODSREC.dtsx" finished: Failure.

-

I check and recheck the package, everything is ok. Strange thing, when I try to convert the "ErrorColumn" field, the package works fine.

Regards

Ayzan

Okay, you *don't* have the same problem as the OP. If you are using a SQL Server destiantion, SSIS will not automatically cast/convert datatypes for you. The data flow datatypes (metadata) will need to match EXACTLY with the SQL Server destination table.|||

Thank you Phil Brammer,

Sorry this IS the same problem, cause I try with a Sql server destination, a data reader destination, a flat file destination, for redirecting the failed rows, and I have still the same message ... in spite of casting/converting datatypes

Regards

Ayzan

|||

Ayzan wrote:

Thank you Phil Brammer,

Sorry this IS the same problem, cause I try with a Sql server destination, a data reader destination, a flat file destination, for redirecting the failed rows, and I have still the same message ... in spite of casting/converting datatypes

Regards

Ayzan

Ah, see, you left out valuable information! So I'll ask this as I have done before. Before we continue, please provide the metadata information as it stands before going into the destination. Then, please provide the table column information for the destination as well.

One other thing to note is that there could be one bad row that is causing this error... Have you redirected errors and inspected them?|||

Ok,

There is a simple Data Flow, so as to reproduce the error:

#Data Source, OLEDB, AdventureWorksDW

#OLE DB Source, SQL Command :

SELECT TOP (10) TimeKey, OrganizationKey, DepartmentGroupKey, ScenarioKey, AccountKey+100 as AccountKey, Amount
FROM FactFinance

#OLE DB Destination

AdventureWorks.FactFinance

#Error Output to Data Conersion

TimeKey -> DT_STR

#DataReader Destination

Enjoy !

|||

Ayzan wrote:

Ok,

There is a simple Data Flow, so as to reproduce the error:

#Data Source, OLEDB, AdventureWorksDW

#OLE DB Source, SQL Command :

SELECT TOP (10) TimeKey, OrganizationKey, DepartmentGroupKey, ScenarioKey, AccountKey+100 as AccountKey, Amount
FROM FactFinance

#OLE DB Destination

AdventureWorks.FactFinance

#Error Output to Data Conersion

TimeKey -> DT_STR

#DataReader Destination

Enjoy !

I don't have a AdventureWorks.FactFinance table. I have the data warehouse table... Are you creating your own FactFinance table in the AdventureWorks table? The mere prefix of "Fact" indicates that it should be in AdventureWorksDW.|||

Sorry,

#OLE DB Destination

AdventureWorksDW.FactFinance

Well in fact, whatever the source/destination, you just have to generate an error while trying to insert new records.

Regards

Ayzan

|||

Ayzan wrote:

Sorry,

#OLE DB Destination

AdventureWorksDW.FactFinance

Well in fact, whatever the source/destination, you just have to generate an error while trying to insert new records.

Regards

Ayzan

I'm utterly confused now. So your source and destination tables are the same? What are you doing exactly, and what are you expecting to see? Why would I select the top 10 records from FactFinance and then turn around and insert them again? Regardless, nowhere should a DT_STR datatype be picked up on the Timekey field because it is an integer field.

Nevermind. I understand now. Hang on.

Data Conversion - NULL datetime causes failure

Hi,

I have a flat file source and am bringing it into an OLE DB Destination connected to a SQL Server 2005 database. Since I am bringing in the data via a flat file, I have a conversion step in the data flow, which uses a "Data Conversion" transformation to convert each column to the appropriate destination data type.

When I run my SSIS package, if the datetime column in the flatfile is NULL, then the Data Conversion step fails. I have my Data COnversion step set to redirect error rows to an error output. So, I get all the rows with populated datetime values in the intended destination, and none of the rows with null datetime values (these go to the error destination.)

This is a problem! I need to make sure all valid rows fall through. It seems very odd to me that a NULL datetime could not be converted to a datetime data type. This makes no sense, since a datetime column can naturally hold NULL, just like any other type. So, my question is: is this a bug? It seems to me it is. I can't believe this would be the desired behavior?!

Microsoft: please let us know whether this is expected behavior. If so, then what would the desired workaround be?

Thanks

Do you have the retain nulls option checked in the Flat File Source?

If you are treating the column as a string in your flat file source, you may need to use a Derived Column transform to handle the conversion. Use an expression like:

Code Snippet

([YourDateColumn]=="")|| (ISNULL([YourDateColumn])) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)[YourDateColumn]

|||Thanks much - the checkbox in the flatfile source did the trick. Never noticed that before, but it worked great. Thanks again.

Data Conversion

I need help!!!! I am about to go nuts! I am getting the following error in SSIS:

Error at Violations Load [SQL Server Destination [3800]]: The column ""Site No "" can't be inserted because the conversion between types DT_STR and DT_NUMERIC is not supported.

I have tried using the data conversion task, modifying all properties to DT_NUMERIC and so on. I just can't figure it out! I am attempting to load a numeric field from a flat file into a SQL Server database. I cannot find any information on this and have tried about everything. I need any help or suggestions anyone can offer! Thank you in advance for your help!!

SD

If the column in sql is a string (char, varchar, etc) you should use a (DT_WSTR,<<length>>)intcolum from file.

For example.. (DT_WSTR,2)12 would cast the number 12 into a string of 2 character length as "12"

Hope that helps.

|||

The destination component tells you the type of the target table. Double click on any data path (the green lines between the components) to see teh tye of the field in the pipeline. At some point the 2 will be different. Thats where you need to do a data conversion.

-Jamie

Data Conversion

Hi,

I have a flat file with over 300 fields and need to do a data conversion before SQL final destination, most fields are DT_STRING, is there an easier way of converting the data without having to manualy do the 300 fields.

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

|||It's going to be manual somehow... Either you configure the flat file source with the correct data types, or you add a derived column/data conversion component, or you stage the data into SQL Server and write a SQL statement to convert the view.|||

Hi,

I think i've managed to do it, i manualy went through the import using the SQL wizzard and at the final stage saved to an SSIS package copied the data flow ammened it to fit into my project and its running now.

Thanks,

Slash.

Data Conversion

Hi,

I have a flat file with over 300 fields and need to do a data conversion before SQL final destination, most fields are DT_STRING, is there an easier way of converting the data without having to manualy do the 300 fields.

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

|||It's going to be manual somehow... Either you configure the flat file source with the correct data types, or you add a derived column/data conversion component, or you stage the data into SQL Server and write a SQL statement to convert the view.|||

Hi,

I think i've managed to do it, i manualy went through the import using the SQL wizzard and at the final stage saved to an SSIS package copied the data flow ammened it to fit into my project and its running now.

Thanks,

Slash.

Data Conversion

Hi,

I have a flat file with over 300 fields and need to do a data conversion before SQL final destination, most fields are DT_STRING, is there an easier way of converting the data without having to manualy do the 300 fields.

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

|||It's going to be manual somehow... Either you configure the flat file source with the correct data types, or you add a derived column/data conversion component, or you stage the data into SQL Server and write a SQL statement to convert the view.|||

Hi,

I think i've managed to do it, i manualy went through the import using the SQL wizzard and at the final stage saved to an SSIS package copied the data flow ammened it to fit into my project and its running now.

Thanks,

Slash.

Data Conversion

Hi,

I have a flat file with over 300 fields and need to do a data conversion before SQL final destination, most fields are DT_STRING, is there an easier way of converting the data without having to manualy do the 300 fields.

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

|||It's going to be manual somehow... Either you configure the flat file source with the correct data types, or you add a derived column/data conversion component, or you stage the data into SQL Server and write a SQL statement to convert the view.|||

Hi,

I think i've managed to do it, i manualy went through the import using the SQL wizzard and at the final stage saved to an SSIS package copied the data flow ammened it to fit into my project and its running now.

Thanks,

Slash.

Data Conversion

Hi,

I have a flat file with over 300 fields and need to do a data conversion before SQL final destination, most fields are DT_STRING, is there an easier way of converting the data without having to manualy do the 300 fields.

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

|||It's going to be manual somehow... Either you configure the flat file source with the correct data types, or you add a derived column/data conversion component, or you stage the data into SQL Server and write a SQL statement to convert the view.|||

Hi,

I think i've managed to do it, i manualy went through the import using the SQL wizzard and at the final stage saved to an SSIS package copied the data flow ammened it to fit into my project and its running now.

Thanks,

Slash.

Data Conversion

Hi,

I have a flat file with over 300 fields and need to do a data conversion before SQL final destination, most fields are DT_STRING, is there an easier way of converting the data without having to manualy do the 300 fields.

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

|||It's going to be manual somehow... Either you configure the flat file source with the correct data types, or you add a derived column/data conversion component, or you stage the data into SQL Server and write a SQL statement to convert the view.|||

Hi,

I think i've managed to do it, i manualy went through the import using the SQL wizzard and at the final stage saved to an SSIS package copied the data flow ammened it to fit into my project and its running now.

Thanks,

Slash.

Data Conversion

Hi,

I have a flat file with over 300 fields and need to do a data conversion before SQL final destination, most fields are DT_STRING, is there an easier way of converting the data without having to manualy do the 300 fields.

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

|||It's going to be manual somehow... Either you configure the flat file source with the correct data types, or you add a derived column/data conversion component, or you stage the data into SQL Server and write a SQL statement to convert the view.|||

Hi,

I think i've managed to do it, i manualy went through the import using the SQL wizzard and at the final stage saved to an SSIS package copied the data flow ammened it to fit into my project and its running now.

Thanks,

Slash.