CODEWITHSUNDEEP
c#t-sqlvisual-studio-2015ssis
interesting-name-here
interesting-name-here

Reputation: 1890

SSIS: Column Size Not Changing Based on Query

I have a package. It has a query that feeds into a Script Component.

In the query I am selecting a varchar(8) column from a table and then I CAST(myDateCol AS varchar(10)).

SELECT 
    myPK, 
    CAST(myDateCol AS varchar(10)), --myDateCol defined as varchar(8)
    myOtherCol
FROM 
    MyServer.MySchema.MyTable

In my script, I am trying to add two characters to the Row.myDateCol in Input0 but I get a Buffer Error and it is in the property setter for myDateCol. You can see that it sets the property to 8 characters but errors out after that.

What I've done is add an output column with Length = 10, set it, and mapped that to the next component in the package but that seems a little silly.

Is there a way to force the size of your input columns based off of the query OR is there a way that I can manually force a refresh in case the package is just stuck thinking that I'm dealing with a varchar(8) as the CAST operation was added later?

Additional Info:

Row.myDateCol = "20170404"
Row.myDateCol = "2017-04-04" // Errors out

Upvotes: 1

Views: 843

Answers (3)

Dave Sexton
Dave Sexton

Reputation: 11188

This is normal behavior for SSIS. When you create a data source which uses a SQL query, SSIS will look at your query and build the the metadata for the dataflow. The data source will only recalculate that metadata if you change the structure of your query, for example number columns or their names.

The easiest way to force a refresh of the data types without resorting to renaming columns is to go to the columns page of the data source editor, Untick and then tick the top tick box of the Available External Columns. This will deselect all columns and re-select them and at the same time refresh the metadata. You can easily confirm this by hovering your mouse over the External\Output column names listed in the lower section.

enter image description here

Upvotes: 3

interesting-name-here
interesting-name-here

Reputation: 1890

So, after some playing around, I found that renaming the column changed the size to varchar(10) per below:

SELECT 
    myPK, 
    CAST(myDateCol AS varchar(10)) AS DATECOL,
    myOtherCol
FROM 
    MyServer.MySchema.MyTable

I then changed it back

SELECT 
    myPK, 
    CAST(myDateCol AS varchar(10)),
    myOtherCol
FROM 
    MyServer.MySchema.MyTable

And the change stuck. I don't know why or how but VS/SSIS somehow never refreshed itself to change to a different type. I assume it has no handling for query changes after the initial query is input unless names/aliases change.

This wasn't just my machine either. Weird.

Upvotes: 1

Mad Myche
Mad Myche

Reputation: 1075

Your problem is the result of dealing with Date(Time) as text instead of the number(s) it is. And I really cannot tell from your question if you want to want to add the extra characters added in at the Data Layer (Sql) or at the Application (C#) Layer.

Casting VarChar(8) => VarChar(10) will still just return VarChar(8) if you don't fill in (pad) that value. You could try a Cast VarChar(8) to Char(10).

Another option would be a double conversion of your column value to Date and then back to your desired varchar(10).

SELECT  myPK,
        Convert(VarChar(10), Convert(Date, myDateCol, 112), 120),
        myOtherCol
FROM
        MyServer.MySchema.MyTable

Upvotes: 2

Related Questions