Wednesday, 4 January 2017

SSIS Appetizer: import numerics with other regional settings

Case
I have a CSV file with numeric values that use a dot "." as decimal separator instead of the comma "," we use locally. When I try to import it in SSIS with a Flat File Source it gives me an error. I don't want to/can't change the regional settings on the server. How do I import this flat file without errors?
The value could not be converted because of a potential loss of data
10.5 should be 10,5 (or vice versa)






















Error: 0xC02020A1 at DFT - Process Data, FF_SRC - myCsvFile [2]: Data conversion failed. The data conversion for column "myColumn" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at DFT - Process Data, FF_SRC - myCsvFile [2]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "FF_SRC - myCsvFile.Outputs[Flat File Source Output].Columns[myColumn]" failed because error code 0xC0209084 occurred, and the error row disposition on "FF_SRC - myCsvFile.Outputs[Flat File Source Output].Columns[myColumn]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
Error: 0xC0202092 at DFT - Process Data, FF_SRC - myCsvFile [2]: An error occurred while processing file "D:\myFolder\2016-12-27.csv" on data row 2.
Error: 0xC0047038 at DFT - Process Data, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on FF_SRC - myCsvFile returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.


Solution
You can change the LocaleID of the connection manager to import this file. Right click the connection managers and choose Properties...
Go to properties of flat file connection manager




















Then locate the LocaleID property and change it to English (United States), English (United Kingdom) or an other country that uses a dot "." as decimal separator. Or change it to for example Dutch (Netherlands) if you have the opposite problem.
Change LocaleID
















Now run the package again to see the result.
Success











Thursday, 29 December 2016

Execute packages in Scale Out

Case
How do I execute a package with the new Scale Out function? I don't see any options when executing a package.
Find the Scale Out options





















Solution
The new Scale Out execution is not (yet) integrated in the standard package execution window. And the Package Execution Task has not changed. Therefore it will always execute the package on the same worker as the parent package. Both will probably change within a couple CTP releases.

Catalog
If you right click on SSISDB within the catalog then you will see the new context menu item "Execute in Scale Out..."
Execute in Scale Out...


























Next you can choose which packages to execute and on which worker servers.
Execute in Scale Out















After hitting the OK button no reports are shown like in the regular execution, but you can find the reports in the context menu of the Catalog.
No open report option









And in the report you can see which 'random' worker executed the particular package.
The Machine property show which worker was used


















Conclusions
Nice first version of the Scale Out. Hopefully the next CTP contains a new version of the Execute Package Task and an integration of the regular execution and scale out execution. Please try it out and let me (or Microsoft) know what you think about it.

Some considerations, because the worker services uses a local system account you might want to consider changing that to a domain account or use other options like a proxy or a database user. Other concerns are the firewall if you're using a local database on the master and local paths (d:\myfiles\) on the master won't work either.
NT Service\SSISScaleOutWorker140

Related Posts Plugin for WordPress, Blogger...