Category Archives: BI

SSIS 2014 Execution Error while validating Script Component

It’s been so long after my last article. Looks like I been lazy for few years now 🙂

I assume lot of people using  SSIS package deployment utility after they support project deployment model. I always use the ispac for easier deployment though it doesn’t support environments variables in deployment. Today we deployed a project to our environment which contains new package and no modifications to the existing packages were made. New package worked fine but some of the old packages started failing, I checked internal versions of the package and couldn’t find any changes. Errors were happening in Script Task. Continue reading SSIS 2014 Execution Error while validating Script Component

Loading Flat File – Row Delimiter Not Recognized

I was working in a migration project where we migrated SQL Server 2000 to SQL Server 2008 R2. Everything went fine except one SSIS package. It’s a simple package where we are importing data from a csv file. This csv file is created from Unix environment with TAB as column delimiter and CR\LF as row delimiter.The source file might have values for all the columns or it won’t have value for one or multiple columns. It worked perfectly fine in SQL Server 2000 however when we migrated to SQL Server 2008 R2 the behavior is totally changed. When you execute the package it will take data from the next rows for the missing column values.

Continue reading Loading Flat File – Row Delimiter Not Recognized

Rollback in Data Import Task–SSIS

I was working in a package and I was requested to set the import task as transaction consistent. In SSIS you can do all sorts of ETL stuff so when you import data you need to make sure the import is in consistent state which means either all the data should gets loaded or it should be rolled back to the previous state. You can enable this option either in package level or container level or at task level. Based on your requirement you can set the transaction property so the final output is in consistent state.

Continue reading Rollback in Data Import Task–SSIS

File Exists Check in SSIS

Couple of weeks back I was working with a SSIS package development. My requirement is to check whether the file exists before I start importing the data. If it doesn’t exist then the package should update the db table. When I heard this requirement I thought I can make use of the “File System Task” in SSIS toolbox. When I start the work I came to know that you can’t get this piece of information from that task. It’s really weird for me as you can move\copy a file or folder using this task however checking the file existence is not possible with that task as shown below.

Continue reading File Exists Check in SSIS