Name an ODBC destination file with a date

  • Hi

    I currently have text files sent monthly that need to be imported into SQL server. I'm new to SSIS and have fumbled my way through importing each one using a data flow task for each file (flat file source and OLE DB Destination). Within my OLE DB Destination data flow, I've set the name of the table to match my import file, however, I'm wondering if there is a way to add the date of the file to the end of it. For example, one text file is called Branch. Is it possible to name the SQL table 'Branch_20130630' or something of the like?

    Also, when I set the table name within the OLE DB Destination, is there an option that overwrites the table should it be there already? (in the case of incomplete data that would need to be sent/reloaded)

    Thanks so much!

    Tiffany

  • tiffanyb (6/24/2013)


    Hi

    I currently have text files sent monthly that need to be imported into SQL server. I'm new to SSIS and have fumbled my way through importing each one using a data flow task for each file (flat file source and OLE DB Destination). Within my OLE DB Destination data flow, I've set the name of the table to match my import file, however, I'm wondering if there is a way to add the date of the file to the end of it. For example, one text file is called Branch. Is it possible to name the SQL table 'Branch_20130630' or something of the like?

    Also, when I set the table name within the OLE DB Destination, is there an option that overwrites the table should it be there already? (in the case of incomplete data that would need to be sent/reloaded)

    Thanks so much!

    Tiffany

    I'm not 100% sure I understand your questions, but here goes:

    1) Is there is a way to add the date of the file to the end of it?

    I presume that the file name changes every month and you don't want to keep editing the SSIS package to accommodate that?

    2) Is there an option that overwrites the table should it be there already?

    Are you creating a new table for every file that you import? This would usually be considered bad practice - better to put all of the data in the same table and include extra column(s) to help you identify the source of the data (eg, file name)

    There is no such option, however, it's easy enough to achieve the same outcome. Before your dataflow task, add an ExecuteSQL task which does a truncate on the table.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • You can enter a dynamic destination using an expression https://www.simple-talk.com/sql/ssis/working-with-property-expressions-in-sql-server-integration-services/

    Leave your existing settings as they are, the destination will be changed by the expression on execution.

    You'll need a variable which is populated by reading the file date somehow, probably use powershell, then use the variable in the OLE DB expression

  • foxxo (6/25/2013)


    You can enter a dynamic destination using an expression https://www.simple-talk.com/sql/ssis/working-with-property-expressions-in-sql-server-integration-services/

    Leave your existing settings as they are, the destination will be changed by the expression on execution.

    You'll need a variable which is populated by reading the file date somehow, probably use powershell, then use the variable in the OLE DB expression

    The destination here is a SQL Server table, not a flat file.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • You can change data access mode for the oleDB destination object to "table name or view name variable" then assign a value to the variable via an expression or other ssis task

  • Hi Phil and foxxo,

    thank you both for your replies. After I knew what to search for, I found a way to make 3 package variables;

    FileDate = getdate() --For now, it is possible that not all clients send their data monthly... We might have to backdate some

    LastMonth = (DT_Date)DATEADD("D",-(Day( @[User::FileDate])), @[User::FileDate])

    LoanFileName = "Loans" + (DT_WSTR, 4) YEAR(@[User::LastMonth]) +

    RIGHT("0"+(DT_WSTR, 2) MONTH(@[User::LastMonth]),2) +

    RIGHT("0"+(DT_WSTR, 2) DAY(@[User::LastMonth]),2)

    Now my issue is trying to reference these variables within the tasks. I tried using the OLE DB Destination and setting the data access mode to Table name or view name variable. I can select my variable from the list, however I get an error message;

    Opening a rowset for "Loans20130531" failed. Check that the object exists in the database.

    So, I thought I would try and use an execute SQL task to create the table before the data flow task runs. I ran into some trouble when trying to name the create table command;

    CREATE TABLE @[User::LoanFileName](

    [Customer ID] varchar(50),

    [Account Number] varchar(50),

    [Major] varchar(50),

    [Minor] varchar(50),

    [Benefit] varchar(50),

    [Status] varchar(50),

    [OpenDate] varchar(50),

    [ClosedDate] varchar(50),

    [Original Loan Amount] varchar(50),

    [Balance] varchar(50),

    [Maturity Date] varchar(50)

    )

    The above doesn't work, but I read that you can replace the @[User::LoanFileName] with a ? and then map it in the parameter mapping section of the execute SQL task editor. I tried that, but its still not working. I think I'm stuck now.... Any ideas?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply