ssis reading in flat files with variable names

• alex.planting

  Mr or Mrs. 500

  Points: 533

  More actions

  January 29, 2010 at 3:31 am

  #229517

  could someone please point me in the right direction of importing an flat file into sql server with use of ssis

  i have created an sql task to decide which file should be imported (user variable) the variable holds the complete path and filename. Now i want an data flow task but i cannot assign the variable in the connection properties.

  Every week we receive a file that needs to be imported in sql server, i now do the import manually and then run an SPR to validate and process the data. Clearly i want to automate this process but I am missing the expertise on SSIS.

  any hints,tips or examples are welcome

• wschampheleer

  SSCertifiable

  Points: 5504

  More actions

  January 29, 2010 at 4:28 am

  #1110617

  Select the connection manager for the flat file (at the bottom).

  Press F4 - this will open the properties pane.

  Look for expressions. Click the field on the right and you will see a grey box with ellipsis. Click on it.

  This will open the property Expressions Editor.

  For property, select ConnectionString from the drop down box.

  For Expression, click on the ellipsis again.

  Drag the variable containing the file name from the upper left box to the Expression box. Click on Evaluate Expression to test.

  Run your package.

  Regards,

  Willem
  http://wschampheleer.wordpress.com[/url]

• alex.planting

  Mr or Mrs. 500

  Points: 533

  More actions

  January 29, 2010 at 5:38 am

  #1110651

  excellent, will try.

  thanks willem