EdZeppelin
asked on
How to script a Package in SSIS using an Excel Source and Data Reader Dest
Greetings,
Could someone assist in writing a script a Package in SSIS (ie the "Script Task") to have an Excel Source control point to an Excel file and DataReader Destination or OLE DB Destination control append the Excel file's data into a SQL Server table. I'm a newbie at SSIS ; any help or good links appreciated.
Could someone assist in writing a script a Package in SSIS (ie the "Script Task") to have an Excel Source control point to an Excel file and DataReader Destination or OLE DB Destination control append the Excel file's data into a SQL Server table. I'm a newbie at SSIS ; any help or good links appreciated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was looking for the place to specify SSIS zone and didn't see it. I'll look for it again when posting next question.
I need the script because I'll want to pass in a file path and file name as a variable for the Excel file. The path and file name will be specified by an end user in an ASPX web page and passed to the SSIS package where the Excel Source control will incorporate this dynamic info.
I need the script because I'll want to pass in a file path and file name as a variable for the Excel file. The path and file name will be specified by an end user in an ASPX web page and passed to the SSIS package where the Excel Source control will incorporate this dynamic info.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think I'm real close. I right clicked on Excel Connection Manager and opened up the Property Expression Builder and selected ConnectionString under property on the left side and I tried to specify a user variable XLConnStr for the right side Expression but all variations kicked out errors. Does someone know the right syntax for the Expressions dialog box, I looked in the two books on SSIS and they were that helpful.
Is a dtsDataParameter in vb.net the same as a package variable in SSIS; the code in the apx side is working, just have to get package and variable set right.
Thanks for all your help & links.
Is a dtsDataParameter in vb.net the same as a package variable in SSIS; the code in the apx side is working, just have to get package and variable set right.
Thanks for all your help & links.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm setting the variable to data type to "string". I've been unable to specify a User variable when creating a new variable, even after typing in "User::" so when I try to use it in the Expression window it would kick out an error saying it was unable to find the variable I created. The scope is set to Data Flow Task and was trying to change it to package level scope but haven't yet found the spot to change it. Is that an easy fix?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I clicked the ellipsis at the right of the Expression Builder and the variable I created was not visible/accessible.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was able to change the scope of the variable to package and then set it in the Excel Conn Manager and set the Expression \ ExcelFilePath to the User variable but then the following error showed up. I'll do some 'Net searching on error msg #'s etc. but has someone ran into this one below?
Error at SSISExcelTest [Connection manager "Excel Connection Manager"]: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Invalid argument.".
Error at Data Flow Task [Excel Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECT
The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error at SSISExcelTest [Connection manager "Excel Connection Manager"]: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Invalid argument.".
Error at Data Flow Task [Excel Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECT
The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
There may be error messages posted before this with more information on why the AcquireConnection method call failed.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
If my own comment was considered part of the solution; it was an accident and should be changed. Apologies.
But for the answer now:
why you want to script it?
you can use data flow task in SSIS with excel source and any destination you want.
what you want to do exactly? what is your problem?