July 22, 2009 at 8:37 am
Hi,
Ive a package that it works fine. It loads data from a flat file into my Bulk Table and then it runs a stored procedure to insert the data into my SQL Database Table...
I have to load this kind of daya daily, but my flat file name is different everyday (format file is the same)...
Anyone knows how to indicate in the package the file that needs to read everyday to load the data???
Regards,
July 22, 2009 at 8:49 am
you can use a variable to hold the table name. The new file name can then pass to this variable while running the SSIS package.
July 22, 2009 at 8:55 am
the following article would be a good example for you to understand the varaibles in SSIS package:
http://sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
July 22, 2009 at 9:24 am
Hi,
i did it but my original connection was broken. My OLE DB Destination has the name of the Server, user, password and database specified. When I added the variable that settings for the connection are gone.
Its any other way to get one file everyday?
Regards.
July 22, 2009 at 10:16 am
Hi,
Now its working. But, in the path I have around 1000 files, I just need the process to take the new one to load the data of the day..
How I can do that, instead of loading the 1000 everytime ?
Thank you
July 22, 2009 at 11:17 am
First of all, you don't need a for each loop if you only want to load one file for each package exectution. I would create a data flow with a flat file source adapter pointing to your file. Use the expression builder to set the connection string (or filename) to match whatever naming convention you have for your files to get the most current file.
As a side note, you can bulk load w/o using a staging table/SP combo.
July 22, 2009 at 11:39 am
Hi,
Thank you for your help. Where can I select the expression builder ?
my stored procedure has some join tables (including the staging table) to populate the destination table... Can I still do this with the bulk load ???
July 22, 2009 at 12:19 pm
I found the expression,now I know how to set it up. It takes exactly the file name that I need.... But, I got another issue when I added this expression.
In my OLE DB DESTINATION, I already have my connection, but it failed after the expression value
the loging failed..
Any advice?
July 22, 2009 at 12:41 pm
Did you change something w/ the desintation properties? You didn't by chance change the destination properties expression instead of the source did you?
Can you tell me what object, property, and expression you used to get the file name into your data flow source?
July 22, 2009 at 12:50 pm
I didnt change any destination property:
I just changed the Flat File Connection properties Expression like:
"\\\\prohhreport\\reports\\stbinfo-"
+ (DT_WSTR,4)YEAR(dateadd("dd", -1, GETDATE()))
+ RIGHT("0" + (DT_WSTR,2)MONTH(dateadd("dd", -1, GETDATE())), 2)
+ RIGHT("0" + (DT_WSTR,2) DAY(dateadd("dd", -1, GETDATE())) , 2)
+ ".tsv.csv"
note: I didnt select any variable, I just added the expression. after the error I created the OLE DB Destination but its the same error:
This is the error:
SSIS package "SSIS.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at SSIS-ASTB, Connection manager "10.124.251.31.DataBase.sa": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.".
Error: 0xC020801C at Data Flow Task, OLE DB Destination [824]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "10.124.251.31.DataBase.sa" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "OLE DB Destination" (824) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "SSIS-ASTB.dtsx" finished: Failure.
July 22, 2009 at 1:04 pm
Where are you storing the SA password? If you open the connection manager up, type in the sa password, and press the 'Test Connection' button, does it connect?
July 22, 2009 at 1:09 pm
The connection is configured. The password worked.
I found the problem, My package was with PROTECTION LEVEL "Dont Save Sensitive". I changed it to "EncryptSensitiveWithUserKey" and it works...
I found SSIS kind of sensitive with the changes... How I can run the package out of that server or in a SQL Agent Job.. Im getting errors..
Do you have any experience running packages out of the original server /??
note: my package is save on a file system.
Thank you 🙂
July 22, 2009 at 1:28 pm
Ah, now that's another story! Depending on your protection level settings, the setup is different. I prefer to store the packages in SQL Server, use configuration files to pass in connection information, and use Windows authentication. It get's a bit trickier when you use SQL Server authentication as you sometimes need a proxy account. Here's a couple good resources on setup:
July 22, 2009 at 1:47 pm
Hey this setup is for packages on file system right?
I found an interesting way to run the package in a Job. Instead to select the SSIS Package on the Job Properties. I selected the Operating System (CmdExec) and my command looks like:
DTEXEC /FILE "\\10.124.251.31\e$\SSIS.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI
Actually, this one works from the command prompt, but still having problems with the Agent Job.
Can you just guid me on how to save my package on the Server and use the configuration file ?
Thank you for helping me
July 22, 2009 at 2:02 pm
I found it.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply