Tuesday, November 1, 2016

SSIS Errors

Today, while working on a new ETL in SSIS 2008 (using an old ETL as a model) I stumbled upon an error that was pretty new to me: Error at Data Flow Task [DTS.Pipeline]: input column "Column1" (1234) has lineage ID 1234 that was not previously used in the Data Flow task.

First of all, what is this lineage id? 
According to TechNet Lineage id is "a property of the component or transformation used in the data flow task. It contains an integer value that will work as buffer pointer. Each column in the data flow task will be assigned a lineage id." In fact, a column can have more than one lineage ID, if more transformations are applied on it. Basically every column has an Id pointing to the buffer.
The fix to the problem was to refresh the mapping of the columns. The mapping was not refreshed, so basically a component is waiting for something at a buffer allocation identified by something that was not used before. Reading from the position 1234 in the buffer would yield nothing since no one is writing at that position before.

Thursday, June 2, 2016

xp_cmdshell in SQLServer 2014

Today I needed to create a job that moves files on the file system.

despite the recommendetions against xp_cmdshell, I use it because similar jobs do the same, so the SP has to exist on the server anyway. The problem was that running the move command in CMD line worked fine, while running the SQL code returned a "file access denied" exceptions.

Of course, the solution is to give access to the folders to the user that SQL Server runs under. Look at the services in task manager and add the user to the folder.