John Bolter
asked on
help changing Excel input cell for SSIS package
Hi I have an SSIS package EmpImport19.dtsx developed in Visual Studio 2008 and running on SQL Server 2008.
It works and has been working for 4-5 years.
It reads in an Excel file, validates it, and populates some database tables with some extracted content.
When it runs in Microsoft SQL Server Management Studio, by right clicking on EmpImport19.dtsx and then selecting "Run Package", it does it's stuff and then at the end of it shows in dialog box
Information: Cell D70 (Employee19T) has expected type Double and SSIS type DT48.
Information: Cell D71 (Employee21T) has expected type Double and SSIS type DT48.
Information: Cell D72 (Employee29T) has expected type Double and SSIS type DT48.
Information: Cell D73 (Employee34T) has expected type Double and SSIS type DT48.
Information: Cell D75 (DT12EmpDOBT) has expected type Double and SSIS type DT_DBTIMESTAMP.
This is correct.
A new version of the spreadsheet has Employee29T information in a different Excel cell, cell K59
When I open EmpImport19.dtsx in Notepad I see loads of stuff that looks like this
excelCellList.Add(new CellDatum("D70", "Employee19T", typeof(Double), "DT_R8"));
//Double Block1WT85Correlate (double-precision float [DT_R8])
excelCellList.Add(new CellDatum("D71", "Employee21T", typeof(Double), "DT_R8"));
//Double Block2WT85Correlate (double-precision float [DT_R8])
excelCellList.Add(new CellDatum("D72", Employee29T", typeof(Double), "DT_R8"));
//Double TotalChargeableWeight (double-precision float [DT_R8])
excelCellList.Add(new CellDatum("D73", "TotalChargeableWeight", typeof(Double), "DT_R8"));
If I change the line that reads
excelCellList.Add(new CellDatum("D72", Employee29T", typeof(Double), "DT_R8"));
to
excelCellList.Add(new CellDatum("K59", Employee29T", typeof(Double), "DT_R8"));
save the file, close Microsoft SQL Server Management Studio, open it again, and then rerun the package EmpImport19.dtsx
it doesn't pick up the new location of K59 for my data.
When the modified package completes it still shows
Information: Cell D70 (Employee19T) has expected type Double and SSIS type DT48.
Information: Cell D71 (Employee21T) has expected type Double and SSIS type DT48.
Information: Cell D72 (Employee29T) has expected type Double and SSIS type DT48.
Information: Cell D73 (Employee34T) has expected type Double and SSIS type DT48.
Information: Cell D75 (DT12EmpDOBT) has expected type Double and SSIS type DT_DBTIMESTAMP.
See it says D72 still and not K59
My question is where else is Microsoft SQL Server Management Studio getting the D72 from? Where else do I need to change to get it to import the new Excel cell.
Thank you
John
It works and has been working for 4-5 years.
It reads in an Excel file, validates it, and populates some database tables with some extracted content.
When it runs in Microsoft SQL Server Management Studio, by right clicking on EmpImport19.dtsx and then selecting "Run Package", it does it's stuff and then at the end of it shows in dialog box
Information: Cell D70 (Employee19T) has expected type Double and SSIS type DT48.
Information: Cell D71 (Employee21T) has expected type Double and SSIS type DT48.
Information: Cell D72 (Employee29T) has expected type Double and SSIS type DT48.
Information: Cell D73 (Employee34T) has expected type Double and SSIS type DT48.
Information: Cell D75 (DT12EmpDOBT) has expected type Double and SSIS type DT_DBTIMESTAMP.
This is correct.
A new version of the spreadsheet has Employee29T information in a different Excel cell, cell K59
When I open EmpImport19.dtsx in Notepad I see loads of stuff that looks like this
excelCellList.Add(new CellDatum("D70", "Employee19T", typeof(Double), "DT_R8"));
//Double Block1WT85Correlate (double-precision float [DT_R8])
excelCellList.Add(new CellDatum("D71", "Employee21T", typeof(Double), "DT_R8"));
//Double Block2WT85Correlate (double-precision float [DT_R8])
excelCellList.Add(new CellDatum("D72", Employee29T", typeof(Double), "DT_R8"));
//Double TotalChargeableWeight (double-precision float [DT_R8])
excelCellList.Add(new CellDatum("D73", "TotalChargeableWeight", typeof(Double), "DT_R8"));
If I change the line that reads
excelCellList.Add(new CellDatum("D72", Employee29T", typeof(Double), "DT_R8"));
to
excelCellList.Add(new CellDatum("K59", Employee29T", typeof(Double), "DT_R8"));
save the file, close Microsoft SQL Server Management Studio, open it again, and then rerun the package EmpImport19.dtsx
it doesn't pick up the new location of K59 for my data.
When the modified package completes it still shows
Information: Cell D70 (Employee19T) has expected type Double and SSIS type DT48.
Information: Cell D71 (Employee21T) has expected type Double and SSIS type DT48.
Information: Cell D72 (Employee29T) has expected type Double and SSIS type DT48.
Information: Cell D73 (Employee34T) has expected type Double and SSIS type DT48.
Information: Cell D75 (DT12EmpDOBT) has expected type Double and SSIS type DT_DBTIMESTAMP.
See it says D72 still and not K59
My question is where else is Microsoft SQL Server Management Studio getting the D72 from? Where else do I need to change to get it to import the new Excel cell.
Thank you
John
>My question is where else is Microsoft SQL Server Management Studio getting the D72 from?
It is also possible that whatever process calls the SSIS package has a variable that it is feeding 'D72', and then the package via a script task consumes that and changes the connection.
It is also possible that whatever process calls the SSIS package has a variable that it is feeding 'D72', and then the package via a script task consumes that and changes the connection.
ASKER
Thanks for your reply but no, I am just running the file EmpImport19.dtsx from Microsoft SQL Server Management Studio and the cell location doesn't appear to be coming from anywhere else.
I'm stumped!
I'm stumped!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm answering this myself ... obviously I didn't understand how to use SSIS
<Wild guess> Open the SSIS package in Visual Studio/SSDT, and in the Connection Manager there is an Excel connection that references this specific cell. Open it and change the cell reference, and close.
Then Click on every data flow task that uses this connection, and re-map.
Manually opening the SSIS package and editing the xml will not auto-magically re-map.