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.
EdZeppelinAsked:
Who is Participating?
 
EdZeppelinConnect With a Mentor Author Commented:
Yes, setting the default for the variable was the last missing piece to the puzzle; thanks for all for the help.  I had to make some changes to the code on the aspx side and it was working.
0
 
Reza RadCommented:
I hope you ask your further question in SSIS Zone!

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?
0
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
Hello,

I've written an article regarding the import of an Excel file into SQL Server, using SSIS.  I believe that may be useful in your situation.
Check it out here: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_3248-Importing-Excel-Data-Using-Integration-Services.html

Best regards,
Valentino.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
EdZeppelinAuthor Commented:
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.
0
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
Hello EdZeppelin,

The SSIS zone has been "disabled", meaning that it's no longer possible to put questions in that specific zone (part of an EE cleanup action).  So your question is in the good categories right now :-)
Ow, and editors can still add the zone to your question (as seems to have happened here).

As for your question: you can perfectly configure the path/filename of the Excel source dynamically, no need to use any script for that!  The easiest way is to create a package variable, set the value of your Excel Connection Manager's to that variable.  Then when you call the package, you can pass the value for that package variable to it.

References
Excel Connection Manager - http://msdn.microsoft.com/en-us/library/ms139836%28v=SQL.90%29.aspx
Using Variables in Packages - http://msdn.microsoft.com/en-us/library/ms140216%28v=SQL.90%29.aspx
dtexec utility - http://msdn.microsoft.com/en-us/library/ms162810.aspx

Through that last link you can find an example of how to pass a value for a package variable into your package when executing it with dtexec.  Here it is:

/SET \package\DataFlowTask.Variables[User::MyVariable].Value;newValue

Tip: try to use the DTEXECUI utility first to set the variable's value.  That gives you a usable GUI so that you're sure that there are no errors in the path to the variable.  After setting the value, you can copy/paste the command-line string from one of the other tabs.

(I hope I didn't overwhelm you with all this info?!  SSIS can be a tough nut at first but once you've used it a bit it will all become clear, well, sort of.)

Good luck!
Valentino.
0
 
Reza RadConnect With a Mentor Commented:
>>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.<<
you can do all of these without scripting
you can use variables for excel file path just with EXPRESSIONS.
just you need to right click on the excel connection manager, select properties, in the properties window select expression, click on the ellipsis button in front of expression, then set expression property as filename and set your variable as expression value.
0
 
EdZeppelinAuthor Commented:
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.
0
 
Reza RadConnect With a Mentor Commented:
what is your variable data type?
you should write it in expression like this:
User::XLConnStr
0
 
EdZeppelinAuthor Commented:
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?
0
 
Reza RadConnect With a Mentor Commented:
in front of expression, there is a ellipsis button, when you click on it, an EXPRESSION BUILDER window will appear that has left pane which contains variables, check that if you can find your variable there or not? if yes, drag and drop that variable to the expression pane.
also try to select ExcelFilePath property .
1.jpg
2.jpg
0
 
EdZeppelinAuthor Commented:
I clicked the ellipsis at the right of the Expression Builder and the variable I created was not visible/accessible.
0
 
Reza RadConnect With a Mentor Commented:
maybe scope of variable is not package ?
could you go back to control flow and click on an empty area , then right click there, then select variables, now create new variable with package scope, and try again
0
 
EdZeppelinAuthor Commented:
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_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  
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.
0
 
Reza RadConnect With a Mentor Commented:
did you set a default value for variable?
this is important because SSIS check this default value at validation time, set a DEFAULT VALID file path for variable.
0
 
EdZeppelinAuthor Commented:
If my own comment was considered part of the solution; it was an accident and should be changed.  Apologies.
0
All Courses

From novice to tech pro — start learning today.