Solved

How to script a Package in SSIS using an Excel Source and Data Reader Dest

Posted on 2010-11-13
15
1,040 Views
Last Modified: 2013-11-10
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.
0
Comment
Question by:EdZeppelin
  • 7
  • 6
  • 2
15 Comments
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34130622
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
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 72 total points
ID: 34130659
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
 

Author Comment

by:EdZeppelin
ID: 34130895
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
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 72 total points
ID: 34130988
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
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 178 total points
ID: 34131796
>>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
 

Author Comment

by:EdZeppelin
ID: 34132301
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
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 178 total points
ID: 34133748
what is your variable data type?
you should write it in expression like this:
User::XLConnStr
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:EdZeppelin
ID: 34137829
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
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 178 total points
ID: 34137959
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
 

Author Comment

by:EdZeppelin
ID: 34138121
I clicked the ellipsis at the right of the Expression Builder and the variable I created was not visible/accessible.
0
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 178 total points
ID: 34138177
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
 

Author Comment

by:EdZeppelin
ID: 34138426
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
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 178 total points
ID: 34138995
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
 

Accepted Solution

by:
EdZeppelin earned 0 total points
ID: 34141203
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
 

Author Closing Comment

by:EdZeppelin
ID: 34179111
If my own comment was considered part of the solution; it was an accident and should be changed.  Apologies.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now