Programmatically Change Column Data Types?

0
votes

I am maintaining a BIDS integration service project that was created by a previous staff member. Unfortunately, I don't have any experience with BIDS.

The process takes information from flat file (a series of CSVs) and imports them into our database. The client that provides the data has recently switched to providing Unicode data, so I need to update the integration project to accommodate this. The current BIDS data flow returns a validation error for each column, "Cannot convert between unicode and non-unicode string data types."

Problem: It looks like I can accomplish this by updating the flat file connection manager, but I have several hundred fields that need changed. Is there a way to do this programmatically? Manually changing all of these columns' settings will take a lot of time and be tedious.

Things I have tried:

  • I could use a derived column transformation to cast each column from DT_STR to DT_WSTR. However, there are too many columns to do this manually. And it looks like I would have to specify the length for each column, which is a chore.
  • I could also use a data conversion transformation, but again I would have to manually click each column, search through a list of data types, and pick the one I want.
  • Current attempt: I'm trying to use a script component.
3
visual-studio-2008ssisbids
What all dataflow task ar there – sandeep rawat
There is a flat file source and an ole db destination. Errors are directed to another flat file (flat file destination). – indigochild
Do you mean that you need to change datatype of several hundred fields in the connection manager to support UTF-8? – DBA
Yes. That's what I am trying to do. – indigochild
In that case BIML is best way.. – sandeep rawat

3 Answers

1
votes

You need to make three+ changes: two+ within SSIS and one at your database.

SSIS is going to have a Flat File Connection Manager that you need to modify all instances of the AnsiString type to be String.

Then, any Data Flow Tasks that have a Flat File Source which references the modified Flat File Connection Manager will then need to be updated to correct their metadata.

Then any asynchronous components within the SSIS package will likely need to be adjusted as they may not pick up on the metadata change.

Finally, you'll need to update the target database tables to go from a varchar datatype to nvarchar. Otherwise, SSIS will fail validation on the destination, instead of the source as you are experiencing now.

I've done a bit with SSIS from a programmatic interface, I don't envy you the task you have before you. While you could use a Script Task to inspect an SSIS package and then modify it, you'd be best served making a dedicated console/winform application for doing this manipulation. While it's the same debugger in either SSIS or a dedicated app, the Debug->Change->Restart debugging cycle is much easier without starting the SSIS package and then having the Script Task open for debugging.

0
votes

I can think of two possibilities:

Using .Net:

https://msdn.microsoft.com/en-us/library/ms136025.aspx

Using BIML:

http://bimlscript.com/

0
votes

The easiest way to do this wasn't programmatic at all. BIDS allows you to view and edit the XML underlying a project.

To see or edit this XML, go to View--> Code.

Since I wanted to replace all the STR data types with unicode-friendly WSTR data types, I just did a find-replace on the XML. I replaced dataType="str" with dataType="wstr" and codePage="65001" with codePage="0".