Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Create dynamic filename in SSIS

Posted on 2010-11-12
9
Medium Priority
?
946 Views
Last Modified: 2012-06-22
Hi,

I'm migrating a DTS package to SSIS. In the DTS, I have an ActiveX Script that set a variable (filename with date and time as the file extension) and a Dynamic Properties Task that set the filename to a Text-File destination feeded by a Transform Data Task that uses a SQL query that extract and format the data to the required field-length. That works fine.

Now, I need to do the same in SSIS. Flat-File Connection Manager ask for an existing file. But this file is created by the DTS, it doesn't exist yet. And anyway, filename should be dynamic.

I tried Raw File, where I can select a variable as filename, but the output is binary.

The result file is imported by a SAP ERP system, so it has to match the required fix field-length.

how can I do this in SSIS?

thanks for your time and help
0
Comment
Question by:Dominic34
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34121085
You will be able to do the same thing with a csv destination. You will need to set the destination filename as either an expression or a variable. If variable you will be able to evaluate the variable as an expression.
0
 

Author Comment

by:Dominic34
ID: 34121438
how can I set the destination as a variable? When setting up a flat-file connection, it ask for a valid file name, I have to input something there. How can I use Expression to dynamically change filename of a flat-file connection ?
0
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 2000 total points
ID: 34121496
In the properties of the flat file connection manager you will see a section called expressions. In the expressions section set the Property to be updated to ConnectionString and enter your expression to build your filename.

Yuo will still have to enter a filename so it will validate but upon run-time this will be over-ridden.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34121503
When i say "In the expressions section" you will have to click the ... button.
0
 

Author Comment

by:Dominic34
ID: 34121752
ok good. I've set the property to my variable name. But don't know why, the variable never changes when I execute the ActiveX Script:

Function Main()
dim NomFic
      NomFic = year(now())  & Pad2Left(month(now()))  & Pad2Left(day(now())) & Pad2Left(hour(now())) & Pad2Left(minute(now()))
      DTSGlobalVariables("APicking").value = DTSGlobalVariables("Path").value + DTSGlobalVariables("APickingName").value + NomFic

      Main = DTSTaskExecResult_Success
End Function

Function Pad2Left(ByVal Value)
   Pad2Left = Right("0" & CStr(Value), 2)
End Function

I have set the ConnectionString Expression to the variable "APicking". The ActiveX is executed before the Data Flow Task. Variable is not ReadOnly. But it don't changes and I generate same filename everytime.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34121776
If you look at the properties of the variable and set EvaluateAsExpression you can actually set the value of the variable there. You shouldnt need to use a script task to do this.
0
 

Author Comment

by:Dominic34
ID: 34121867
ok sorry, I had a bug in my variable. File create with correct name and data is fed into it. Still have a problem with the formatting. All rows are inserted into a single line in the file. There is no CR/LF, although Row Delimiter is CR/LF and preview of data is looking good...
0
 

Author Comment

by:Dominic34
ID: 34122056
This is my extraction query:
SELECT  DISTINCT
RTRIM( dbo.PAD(a.[vpmOrder], 22, ' ', 'L')) AS PO,
      RTRIM(dbo.PAD(a.itmCode, 6, ' ', 'L')) AS ITEM,
      'V' as TYPE,
      RTRIM( dbo.PAD(a.[vpmNoColis], 20, ' ', 'L')) as COLIS,
      dbo.PAD(a.[itmQtyReelle], 6, '0', 'L') AS QTY,
      RTRIM( dbo.PAD(b.[orderDate], 8, ' ', 'L')) as DATE
FROM   TB_vpmPreColisage a
INNER JOIN  [TB_vpmOrder] b ON a.[vpmOrder] = b.[orderNO]
INNER JOIN [TB_vpmColis]  ON TB_vpmColis.[vpmNoColis] = a.[vpmNoColis]
WHERE [orderCompleted] IS NOT NULL AND b.[orderTreated] IS NULL

In Flat-File Connection Manager, I selected an existing valid file and delimited the width of each columns. Row Delimited is CR/LF. Output file gives:
              1007540 04235V         S075400221200003520101115                 1007540 09263V         C075400041200001520101115 ....
all the extracted data into a single row....And the preview looks good.

What I'm doing wrong?
0
 

Author Comment

by:Dominic34
ID: 34122470
ok found it. I had to select "Ragged Right" in the Format properties of the Flat-File Connection Manager to have the CR/LF add at the end of each row.

works like a charm now
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

721 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