none
Lookup current date spreadsheet file in sharedfolder then load into SQL table.

    Question

  • Lookup current date spreadsheet file in sharedfolder then load into SQL table.

    I have requirment like to extract the data from spreadsheet and load into SQL table using SSIS,

    So want to check the current date file exists then load or else skip in SSIS. I know we do this For each file loop container but not sure how to impliment,  Source Spreadsheet and destination SQL table

     We get spreadshets in shared path dialy basis overnight.

                    GM108_RYX_TTT_20180104_1234567_345.xls

    GM108_RYX_TTT_20180103_1324674_674.xls

    GM108_RYX_TTT_20180102_1453474_127.xls

    I appreciate if you could give full details of for each loop container and data flow task.

    Thursday, January 04, 2018 2:30 PM

All replies

  • Hello 

    Please help me on this !!

    I have excel file names like  ABCD_EFG_YYYYMMDD_HHMMSS.xls

    I want to look up file name like current date is  ABCD_EFG_YYYYMMDD only and ignore timestamp and load the data in SQL table. Please suggest me how to use in foreach loop container.

    Wednesday, January 03, 2018 5:18 PM
  • Hello 

    Please help me on this !!

    I have excel file names like  ABCD_EFG_YYYYMMDD_HHMMSS.xls

    I want to look up file name like current date is  ABCD_EFG_YYYYMMDD only and ignore timestamp and load the data in SQL table. Please suggest me how to use in foreach loop container.

    Wednesday, January 03, 2018 7:19 PM
  • you can do like this

    1. Add an expression for the FileSpec property of the ForEach loop and set the expression like below

    "ABCD_EFG_" + REPLACE(LEFT((DT_WSTR, 30)GETDATE(),10),"-","") + "*.xls"

    It will ensure only filenames with current date in its name are iterated through and processed by the for each loop


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, January 03, 2018 7:59 PM
  • Hi Prateep,

    there are plenty of examples online e.g http://phil-austin.blogspot.ca/2008/01/ssis-expression-for-yyyymmdd-iso-date.html

    You basically must set the expression up on the file name property of the ForEach Loop to pick the file masked as you mentioned (the example ^ covers it up).

    Please let us know what else we can help you with.


    Arthur

    MyBlog


    Twitter

    Wednesday, January 03, 2018 8:04 PM
    Moderator
  • You can create a variable, i.e, CurrentDate with the following expression:

    LEFT(REPLACE((DT_STR, 50, 1252)GETDATE(), "-", ""), 8)

    and then use the following expression in the property of FileSpec if the prefix of YYYYMMDD is not fixed:

    "*_" + @[User::CurrentDate] + "*.xls"


    A Fan of SSIS, SSRS and SSAS

    Wednesday, January 03, 2018 8:53 PM
  • Thank you for posting

    How do i pass this value in Dataflow task to excel connection manager

    Wednesday, January 03, 2018 9:30 PM
  • Wednesday, January 03, 2018 9:40 PM
    Moderator
  • It got this error

    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 2" 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 Data Flow Task [SSIS.Pipeline]: component "Excel Source" (1) failed validation and returned error code 0xC020801C.

    Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

    Wednesday, January 03, 2018 10:00 PM
  • It must be due to an invalid expression for the file or no rights. Need to see the value.

    Arthur

    MyBlog


    Twitter

    Wednesday, January 03, 2018 10:14 PM
    Moderator
  • Okay,

    Can you please suggest if  you any have details link of same threads

    Thanks 

    Wednesday, January 03, 2018 11:24 PM
  • Enable logging please.

    Post all the errors text in full here along with the value of the expression (everything that is logabble).


    Arthur

    MyBlog


    Twitter

    Wednesday, January 03, 2018 11:28 PM
    Moderator
  • Lookup current date spreadsheet file in sharedfolder then load into SQL table.

    I have requirment like to extract the data from spreadsheet and load into SQL table using SSIS,

    So want to check the current date file exists then load or else skip in SSIS. I know we do this For each file loop container but not sure how to impliment,  Source Spreadsheet and destination SQL table

     We get spreadshets in shared path dialy basis overnight.

                    GM108_RYX_TTT_20180104_1234567_345.xls

    GM108_RYX_TTT_20180103_1324674_674.xls

    GM108_RYX_TTT_20180102_1453474_127.xls

    I appreciate if you could give full details of for each loop container and data flow task. 

    Note : I am using visual studio 2008.

    Thursday, January 04, 2018 2:53 PM
  • Hi PrateepO,

    In the older thread we showed you how to capture a file based on date in a ForEach Loop.

    This time it is no different, you only need to adjust the expression.

    The ForEach will simply exit if no matching files found.


    Arthur

    MyBlog


    Twitter

    Thursday, January 04, 2018 3:13 PM
    Moderator
  • It got this error

    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 2" 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 Data Flow Task [SSIS.Pipeline]: component "Excel Source" (1) failed validation and returned error code 0xC020801C.

    Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

    Looks like a connection string issue with Excel

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, January 04, 2018 3:47 PM
  • I tried a lot, Not working 

    Please suggest if you have any related posts that explains more which is similar to same. 

    Thursday, January 04, 2018 4:26 PM
  • I tried a lot, Not working 

    Please suggest if you have any related posts that explains more which is similar to same. 

    I gave you the exact suggestion in the other thread. All you need to do was replace it with your actual pattern. What issue did you face when you tried it?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, January 04, 2018 4:27 PM
  • where do we map this  User:strExcelfile variable to get this file from specfile
    Thursday, January 04, 2018 8:09 PM
  • Warning: The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.

    Thursday, January 04, 2018 8:12 PM
  • Warning: The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.

    This is normal. Just a warning. Likely there was no match to the expression formed, which becomes the primary suspect now.

    Log it and inspect its value.


    Arthur

    MyBlog


    Twitter

    Thursday, January 04, 2018 8:21 PM
    Moderator
  • where do we map this  User:strExcelfile variable to get this file from specfile

    Sorry do you mean getting the filenames processed by for loop? or setting the file pattern in for each loop?

    if its latter I showed you already

    for former you need to go to variable mappings tab and map filename variable for index 0

    i,e like this example


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, January 04, 2018 8:29 PM
  • Thank You,

    I passed filename variable in excel connection manager property to excelfilepath, It seems when i am running package validating package saying blank

    Error at Data Flow Task [SSIS.Pipeline]: component "Excel Source" (1) failed validation and returned error code 0xC020801C.

    Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

    Error at Data Flow Task: There were errors during task validation.

    Thursday, January 04, 2018 9:22 PM
  • TITLE: Package Validation Error
    ------------------------------

    Package Validation Error

    ------------------------------
    ADDITIONAL INFORMATION:

    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 3" 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 Data Flow Task [SSIS.Pipeline]: component "Excel Source" (1) failed validation and returned error code 0xC020801C.

    Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

    Error at Data Flow Task: There were errors during task validation.

    Error at GSL108_Detail_Collateral_by_Manger [Connection manager "Excel Connection Manager 3"]: 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: "Failure creating file.".

     (Microsoft.DataTransformationServices.VsIntegration)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------
    Thursday, January 04, 2018 9:28 PM
  • Likely the path to the file is not right.

    I asked above ^

    "Log it and inspect its value."


    Arthur

    MyBlog


    Twitter

    Thursday, January 04, 2018 9:31 PM
    Moderator
  • Take a look at the article "Dynamic file name for Excel Connection Manager – SSIS". The important thing is that you need to initialize the variable strExcelFile.

    A Fan of SSIS, SSRS and SSAS

    Thursday, January 04, 2018 10:33 PM