Solved

SSIS- Importing Date/Time into SQL along with FilePath

Posted on 2012-03-27
3
397 Views
Last Modified: 2012-04-04
I have a DTSX file that imports excel files into SQL. Rather than have SQL timestamp the entries, I need to somehow import the time that either the excel file was modified, or have the dtsx package insert into SQL when the SSIS package was executed. My second part is I have a user variable CurrentExcelPath in my for/next loop. I would like to see if I can get that path imported into the SQL DB at runtime.

Any help would be much appreciated.
Thanx
0
Comment
Question by:BKennedy2008
  • 2
3 Comments
 
LVL 16

Expert Comment

by:vdr1620
ID: 37774641
I think..
1. you should be able to use derived column to input the date time or use StartTime system variable
2. you can get the sql path using the same variable that is being used in the for each loop

Hope it makes sense .. let me know if that's not you want
0
 

Author Comment

by:BKennedy2008
ID: 37776275
I used the Excel file sheet to create a date time for when it is changed and imported that.

The only thing I have left is to import the path. I have the path variable, but not sure how to import the ExcelCurrentPath into SQL. I cannot get it to show up anywhere in the data flow task to get it to map to a OLE Destiantion field.\

Do I need to write a custom SQL statement inside the OLE DB Destination task? It will only take a select statement and not an insert. Do I use an OLE DB  Command Transformation? Seems like I am missing the boat here.....
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
ID: 37779711
Well, you can either write a SQL statement as an expression and used SQL command from variable as your source

"select column1, column2, "+VariableName+" from tablename"

Or use Derived column transformation in b/w source and destination
 -- add a new column with the variable as the value
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

867 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

24 Experts available now in Live!

Get 1:1 Help Now