January 26, 2015 at 10:37 pm
Hi
Recently the data type for 2 columns in my external report that is schedule every week to come into my FTP drive on my server 2012 and is loaded by the SSIS package into the staging data base and then moved to the repository database. Recently the external operators that I receive my data load from made a change to 2 column which were both set as a float data type in staging data base. I was told these columns were change to a varchar (10). I made changes in the staging table from float to varchar (10) it would not work I also tried nvarchar (50) but still would not work, I also changed one data type in the repository table from float to varchar & also tried nvarchar the other source was already nvarchar (15). The problem is I cannot load the new report, I have to use the old report for our SSRS but it is out dated. Can you advise me with these data type changes what I may be missing?
I think this is related to the above there is an error appearing in the SSIS package under the data flow in repository task stating "The external column for repository report are out of synchronisation with the data source column. I would really appreciate some advise is anyone knows? I am waiting on training in SSIS & SSRS in a couple of weeks,
Kind Regards Pamela
January 27, 2015 at 9:19 am
i wouldnt rely on the training course to be able to help you resolve this, you may be disappointed.
Can you provide more detail from the package error log
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 27, 2015 at 11:40 am
It sounds like you are changing the data type in the database, but not updating it in the SSIS data flow task. Since you seem to know what the data type is at the source, you can make the same data type at the destination. From there, you need to go into the SSIS data flow, and make updates. This is where it can get tricky with SSIS. The message telling you that the columns are out of synchronization is telling you that what is configured on the data flow task is not the same as what the column is on the table referenced. SSIS tries to update it if you say yes.
Beyond that, you need to go into the advanced properties of the source and destination inside of the data flow task (since both are changing in your case) by right clicking on each and "show advanced editor". From there on the source, go to input and output properties, and under the "source output", choose the column under both "output columns" and "external columns" and validate the data type and length (string or unicode string is likely what you'll want). If you get a message about the data types not being able to switch between unicode and non-unicode, simply try the other string type. Unicode stringshould be equal to nvarchar and string should be equal to varchar. In the destination component, go under input and output properties, and confirm the data type under "external column" for the column modified matches the source.
SSIS is sort of unfriendly, but very flexible, when modifying tables that are already involved in data flow tasks. I've been so frustrated in the past that I've scrapped packages and started from scratch since I couldn't get a simple change working.
January 27, 2015 at 2:40 pm
Much appreciated thank-you I knew I missing something in SSIS, I will give this a go.
January 29, 2015 at 11:02 am
How did you make out?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply