Data With Gaurav

Start with the Below screen shot ( Before Script Task) and you will come to know about the senario. Note : The Script Component also works for the special characters within the Data as well . for ex "A$hish" Hover over the below Regex. it will show > Show potential fixes > in that Select System.Text.RegularExpressions. The Below will disappear.  Use the Below Script.  public override void Input0_ProcessInputRow(Input0Buffer Row) { Row.CustomerID = RemoveSpecialCharacters(Row.CustomerID); Row.NameStyle = RemoveSpecialCharacters(Row.NameStyle); Row.Title = RemoveSpecialCharacters(Row.Title); Row.FirstName = RemoveSpecialCharacters(Row.FirstName); Row.LastName = RemoveSpecialCharacters(Row.LastName); Row.CompanyName = RemoveSpecialCharacters(Row.CompanyName); Row.SalesPerson = RemoveSpecialCharacters(Row.SalesPerson); Row.EmailAddress = RemoveSpecialCharacters(Row.EmailAddress); Row.Phone = RemoveSpecialCharacters(Row.Phone); } public static string ...

Skipping of rows from the TSV file can very easily done by just mentioning in a Text box which is present in the Flat File configuration wizard.  Select the no of rows you want to skip and you can see the output in the preview option.  You can also do the same setting by clicking on the flat file connection manager and changing the value in the highlighted field.

 There are mainly 3 ways in which we can skip particular rows in excel and then copy the data in the SQL server.  I have explained 2 ways in this Blog and the third method is by using a Script task, which is presently out of scope for this article.  Method 1 : In the first method , you can configure the openrowset property by right clicking on the Execute Sql task and then opening the properties > OpenRowSet .      Preview :  Method 2 : You can configure the Excel task , but in that choose Sql Command in the Excel connection manager and then type the following. This will solve the issue and also in this method you can rename the column name in the destination table as well.  

 While using the Execute SQL Task, it gives you a choice to connect to the Source or the destination , this is a very important setting as  this enbles the Execute SQL task to either run at the source or at the destination. The highlighted text is the box in which you keep the values.

 Firstly you are supposed to make the below properly to true after right clicking on the Package. you are supposed to do this for all the tasks. In the first option create a logfile for the Checkpoint. Checkpoint doesnt Start the package at the row level , it starts the package at the Task level.

 1.By using the ispac file.   The ispac file will be present in the Bin in the project folder.(the ispac file is created after building the package)  2.By Deploying the package( by DBA ). In SQL SERVER - -  After that Double click on ISpac File. The Ispac  file will be present in the Bin folder in the project location. Give the destination server details and Click on Connect After Connecting to the SQL Server , you get the Folder you just created. Now go to the SQL server and all the packages will be available. IN SQL server the DBA has to simply Right click on package and execute , then the SSIS package will execute and run. Creating a JOB   Start the SQL Server Agent. Create a Schedule

-When we know the no of files present in the Source Folder then we use Forloop Container. -When we dont know the now of files in the Source Folder we use ForEachLoop Container . For Loop Container Use the Below code in the Execute SQL Task. Map the variable in the Execute SQL TASK. The SQL Code for looping is  Senario :  Get the data from all the flat files(.TXT) and load all the data in the SQL server. The Columns in the Flat files are same, only the Data Differs. For Each Loop :  I f you want to dynamically loop through something you are supposed to add a variale for the file path. For looping purpose , you are going to use DataFlow Task. For the first file we should keep static connection. Converting the Source into Dynmic  We can Change the Datatype in a Derived column task by doing this.