July 27, 2009 at 1:01 pm
SSIS Newbie I did a lot of DTS in 2000 ,but rusty in that.
I know its a lot of questions(3) below, but it would help me to get off to a fast start and show some progress to this problem while go through some chapters in my Micro Press SSIS book in the evenings to get up to speed on SSIS. I foresee more SSIS needs much more robust than this coming in the future...hmmmm
1.do i need 3 connections to excel for each 3 workbooks?
trying to import 3 tabs(workbooks) from an excel file to 3 into temp tables in into sql server 2005 for an sp that runs nightly to do checks and validations on the data, the data(records) that passes the checks from the temp tables will need to be appended to sql permanenet tables
this is how i was explained it need to be done.
2.so would it be better to use memeory emp tables like #tblClaims or the TempDB in sql Server
3.so for the sql server temp tables, i would need 3 connections in the connection manager for sql server and 3 data flow tasks?
July 27, 2009 at 1:19 pm
Hi there,
Are the 3 excel files have the similar data? If not then you will need 3 separate connections. If they have similar data and same set of columns, then you may be able to do a ForEach Loop to process these files.
I am not sure, if you can actually load to a temp table. Temp tables are only available for the session.
To load to 3 different tables on the database, you will only need one connection manager.
Hope that answers your question.
Thanks
July 27, 2009 at 2:39 pm
Thanks so much for you answer!
On part 1: I may have used the wrong terminolgy,so I will say it differently.
its 1 xls file with 3 tab (representing the 3 excel tables) that need to go 3 sql tables.
The excel Tables do have different columns and data.
On the Temp Table part: so is it best then as far as temp tables to actually create 3 tables on the DB like
Tmp_Claims,Tmp_ClaimsTransactions,Tmp_Contacts. The sp that checks and validates the data in these tables
runs nightly in a job so u r right, I do not want connection issues with a #temptable
On part 3: great, I will set up only 1 sql server connection.
July 27, 2009 at 2:51 pm
If there is only one excel file with 3 tabs in it. Then yes you will only need one connection to Excel.
You are correct on the 2nd. You will need 3 physical tables as you have suggested.
July 27, 2009 at 3:04 pm
You only need one Excel connection manager, but you probably need three different data flows to get the data from the three worksheets. If the worksheet layouts are the same, you could possibly use one dataflow in some kind of control loop, but it will be simpler to do them individually.
Using temp tables would be difficult. They are unique to a given SQL connection, and an SSIS connection manager can create multiple connections. You would have to set the UseSameConnection property on the SQL Server connection manager (or is it on the data source tool in the data flow?) to make sure all three pages load under the same connection, and all processing would have to be done from the SSIS package on the same connection. You can use global temp tables to make them visible to other connections, but they still have a lifetime controlled by the connection that created them. If any of the connections is closed, the global temp tables will disappear.
You can avoid these issues with temp tables by creating regular tables in the tempdb database for the life of the package. Just use "CREATE TABLE tempdb.dbo.xyz" instead of "CREATE TABLE #xyz", and you don't have to worry about the connections. Drop them at the end of the process to avoid cluttering up the system, since this will not be done automatically.
If you're using SSIS simply to import the data for processing by a stored procedure, and if the server can read the spreadsheet, and if your server is not 64-bit, you could skip SSIS and use OPENROWSET to read the spreadsheet data directly from the stored procedure. Temp tables would then be much more feasible.
July 28, 2009 at 3:37 am
I agree with what Scot has said.
One Excel connection.
Three data flow tasks (each would have an Excel data source to get the data from that particular sheet).
Each data flow task using a table in the tempdb.
If you are fussy like me over varchar/nvarchar from Excel to the database table you may need to add a data conversion task.
HTH
Ells.
:w00t:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply