December 15, 2008 at 8:01 am
Please suggest me the approach for this to write a script in SSIS as i am new bee to SSIS.
Scenario:
I have an excel table data which needs to be exported to SQL Server. This excel table will have primary key columns and non-primary keys. Primary keys column header will be marked as RED while non-primary keys are marked as normal text.
The challenge for me now is:
1) Write a script in SSIS to identify primary columns from excel so that I will have primary columns data seperate as one destination and non-primary column values as another destination.
I am new to SSIS. Please suggest me how this can be acchieved.
December 15, 2008 at 8:28 am
So are you saying that the only way to identify the primary key is by the colour of the text in excel?
this will not be possible with the standard excel source, so you may need to write some VBA to help you do this..
December 15, 2008 at 8:35 am
Thanks Steve,
Could you please guide me in getting the VBA script to get this job done. As a newbie, I am unaware of this VBA scripting and request your assistance
I would also try from my end to achieve the result
December 15, 2008 at 8:46 am
you can use the color property of Font to work out what the font colour of the cell is. You can loop through the worksheet and then change the values of these cells to something SSIS can pickup in the column name, eg. a suffix.
Though this really depends on how many worksheets you have to import. It may be easier to do this by hand, esp. if you have no VBA experience.
Where are these worksheets coming from? are you able to use another method to tag primary keys such as a prefix/suffix ?
December 15, 2008 at 11:52 am
Hi Steve,
The excel sheet will contain the data of the client and the client will be updating on a daily basis.
Now, I need to identify and retrieve the primary column values to one excel sheet and non-primary key values to another sheet.
As we know primary keys would never change, so client felt a need to segregate them seperately.
Please provide me solution.
December 16, 2008 at 3:28 am
You should then ask the client if they can use another method to identify the primary keys, such as a suffix / prefix (PK_) as this will make the SSIS import much easier.
Also what is the reason for splitting out the PK ?
this should really be stored in the same table as the data that it represents.
December 16, 2008 at 9:29 am
Query Scenario:
1) First of all, for data import utility, the client can use dotnet application or legacy application
2) The data source type can be MS Access from which to import data.
3) Choose destination database, most probably SQL Server
4) Data Validation needs to be done for the data import
5) As an SSIS developer, I have to generate a Data validation report showing TableName, Primary Key ID, and Error Message
6) After this, the data will be imported.
7) At last, there will be a Data Import report showing Table Name, Number of Records to IMport, Number of imported Records, and Number of Skipped Records
Now, I need to concentrate for Step 5 for which data validation report needs to be generated
Please guide me ASAP
December 16, 2008 at 9:48 am
Okay thanks, that is pretty much the exact opposite from what you described in your first post, where you wrote that you need to export Excel --> Sql server.
For an error report simply setup the data-flow and on your transformation componenets set the error handling to re-driect rows on error, Send these rows to a Excel data destination and this will be your error report..
December 16, 2008 at 9:58 am
Thanks Steve, COuld you be much more descriptive?
You mean to say, i need to use transformations in order to validate data. If yes, which transformation.
As I have Date of Birth, SSN, Gender, DateTimeStamp and Application Type as primary keys for which data validation needs to be done.
Which transformation needs to be used for data validation? Please let me know ASAP.
December 16, 2008 at 10:03 am
It depends on what sort of validations are needed.
For data type and sizes, use the data conversion transformation and you can re-deirect the error rows that do not conform to these data types.
For Business logic, if it fairly simple then you can use a derived column to write an expression against a column and redirect the rows based upon the expression results.
Or for more advanced business logic, use a script component and you can write the validaitons using vb..
December 18, 2008 at 10:19 am
Thanks Steve..
But I have much tough task ahead to complete first as soon as possible. Please helpme on this. Its on the same assignment
We have an excel sheet template as table and we need to store structure in the database(SQL Server)
Reqt 1 : I need to write a script so that "All tables primary keys we will log in a table.. (One time script).. Based on this table we need to write a script to identify whether particular column is primary key or not.."
Reqt 2 Whenever this excel template is updated with more columns, I also need to write a script so that excel template is compared with SQL Server database, and if columns are added, I need to write a script so that an "Alter table" script is invoked to modify the structure in the database(SQL Server)
December 23, 2008 at 10:38 am
Steve,
Ths reqt needs to be accomplished. URGENT
Requirement:
Whenever an excel template is updated with more columns, I also need to write a script so that excel template newly uodated template with updated columns , and if columns are added, I need to write a script so that an "Alter table" script is invoked to modify the structure in the database(SQL Server)
Please throw some light as how to accheive this.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply