Iterate through all Excel files and all their sheets

hello,

i need to transfer (migrate ) the data from xl sheet to sqlserver but actually the thing is if the source excel file has different sheets, in each sheet i have the data

and i need to move the entire data( all the data that is present in all sheets of the excel file) to a single table into sql server

like wise i have many xl files ( which have many sheets ) .

for eg:

excel file 1:

-> sheet 1

-> sheet 2

-> sheet 3

excel file 2:

-> sheet 1

-> sheet 2

-> sheet 3

excel file 3:

-> sheet 1

-> sheet 2

-> sheet 3

now i need to get the data from all of the files and i need to insert into a single table ( sql server) in ssis package

so plz help me by giving the solution asap.

thanks

B L Rao

hello ,

while i am trying to transfer the data from xl file to table in sql server by using ssis package it is giving error saying that primary key violation and cant insert duplicate value.

i understood that there is some duplicate data but can i find where that duplicate data exists i mean in which row ? because it contains thousands of records.

thanks and regards

B L Rao.

|||You may accomplish this by using 2 nested Loops: One fairly simple, a foreach loop to iterate through all excel files; a second one to iterate through each excel sheet. I am not sure how to implement the second one; perhaps if the number and name of the sheets is always the same you could built a list of values in a variable and then have the excel component to get the table name from a variable. Just an Idea, you would need to figure out the details.

Iterate a Variable

Hi Guys,

I need to design this SSIS migration package to migrate data.

In a Execute SQL Task, I need to get a full result set and assign it to an variable, such as v_collection;

The SQL statement can be as simple as : select primary_key from a_table;

After that I have a ForEach Loop container, that consumes the variable, and assign each iteration to another variable, such as v_iter, the type of v_iter is DT_I4, because the primary key is a long integer

The problem is: in Oracle, the primary key is NUMERIC(10,0) and in SQL it is int.

I can not assign a NUMERIC(10,0) to an variable of DT_I4, but if I change the variable definition to DT_NUMERIC, then it would not work for SQL.

Anyone knows how to fix this?

I was thinking to add a Script Task between the Execute SQL Task and the Foreach Loop, and somehow access the collection variable, v_collection, and manually convert the value to a DT_IT, then repopulate another collection variable, v_collection_I4 with Integers, and force the Foreach Loop to use v_collection_I4 collection variable.

Will this work? If yes, how? :)

Thanks a lot!

Wenbiao

Hi Wenbiao,

My general principle with variables in SSIS is to use String datatype where at all possible. So following your example above, I would change your SQL statement along the lines of:

select convert ( varchar, primary_key) as primary_key from a_table;

The variable datatype would then be a String.

Then during your Dataflow within your loop, you can use a Derived Column transformation to perform a Type Cast to whatever datatype you need before you deliver the data.

Good luck.

Mike

|||As an alternative, you could create two variables, the first one DT_NUMERIC, the second DT_I4. Set the first one with the numeric value from Oracle in your For Each Loop. Set the second one to evaluate as an expression, and cast the first variable to DT_I4 in the expression. It will be updated each time the first variable changes, and you can use it in the tasks in the loop.

Iterate a Variable

Hi Guys,

I need to design this SSIS migration package to migrate data.

In a Execute SQL Task, I need to get a full result set and assign it to an variable, such as v_collection;

The SQL statement can be as simple as : select primary_key from a_table;

After that I have a ForEach Loop container, that consumes the variable, and assign each iteration to another variable, such as v_iter, the type of v_iter is DT_I4, because the primary key is a long integer

The problem is: in Oracle, the primary key is NUMERIC(10,0) and in SQL it is int.

I can not assign a NUMERIC(10,0) to an variable of DT_I4, but if I change the variable definition to DT_NUMERIC, then it would not work for SQL.

Anyone knows how to fix this?

I was thinking to add a Script Task between the Execute SQL Task and the Foreach Loop, and somehow access the collection variable, v_collection, and manually convert the value to a DT_IT, then repopulate another collection variable, v_collection_I4 with Integers, and force the Foreach Loop to use v_collection_I4 collection variable.

Will this work? If yes, how? :)

Thanks a lot!

Wenbiao

Hi Wenbiao,

My general principle with variables in SSIS is to use String datatype where at all possible. So following your example above, I would change your SQL statement along the lines of:

select convert ( varchar, primary_key) as primary_key from a_table;

The variable datatype would then be a String.

Then during your Dataflow within your loop, you can use a Derived Column transformation to perform a Type Cast to whatever datatype you need before you deliver the data.

Good luck.

Mike

|||As an alternative, you could create two variables, the first one DT_NUMERIC, the second DT_I4. Set the first one with the numeric value from Oracle in your For Each Loop. Set the second one to evaluate as an expression, and cast the first variable to DT_I4 in the expression. It will be updated each time the first variable changes, and you can use it in the tasks in the loop.