Hi, I have the following problem
In a flat file I have 250000 rows when I them go on to the DB only 249995 come, 5 got lost 
Not where it can spend the mistake the loggind does not say anything of mistake
Not which can be the reason of the problem
If someone save for that it can be spending this?
helps please.
If you execute the package in BIDS you see how many rows are output from each component. This should make it very easy to see where the rows are being lost from.
-Jamie
I am losing data from time to time and can not figure out where the rows are vanishing to. In essence, I have 5 files that I process. The processing occurs on a daily basis by one SQLAgent job that calls 5 individual packages, each doing a different piece of work.
I stage each file to a temp table, then perform some minor transformations, then union the rowsets from each file before landing to another temporary table. A subsequent package performs additional transformations that apply to the entire dataset, before inserting them to their final destination. Along the way, I reject some records based on the business rules applied. Each package in the entire Job is logging(OnError, TaskFailed, Pre and Post execute). Their are no errors being generated. No rows are being rejected to my reject tables either.
Without getting into the specific transforms, etc. being used in this complex process, has anyone seen similar unexplained behaviour? I've not been able to identify any pattern, except that it is usually only 1 or 2 of the record types (specific to a source file), that will ever not be loaded. No patterns around volumes for specific source files. There are some lookups and dedupes, however I've seen the records 'drop off' before reaching these transforms. I have noticed I had my final desination load not using fast load. However sometimes the records disappear before even getting to my final staging table which is inserting using fast load. I am going to turn on logging the pipelinerowssent event. Any other suggestions for troubleshooting/tracking down these disappearing records?
Thanks
Couple more clarifications:
I have run the same files thru manually in debug mode to find that I can watch all the rows thru the entire process.
We have seen strange behaviour in running packages as scheduled jobs thru SQLAgent
Utilizes unions which seem a bit klunky.
|||Joe,
We have seen similar problems on my current project so yesterday we turned on OnPipelineRowsSent logging.
Another thing we have done is output the data from each transform component to a file for later examination. The MULTICAST transform is invaluable in order to do this.
as yet we haven't found out what is going on. Its strange.
-Jamie
|||I'm not sure I would use the adjective strange, but..
In trying to troubleshoot this process, I first changed the union tranform that was taking 6 input streams, and busted it out to 5 individual waterfalling unions each with 2 input streams. No change in behaviour.
I then changed the package that moves this data, by adding in multicasts to output to a file after every transform along the way up to the final destination, after the 5 unions. Just by adding the multicasts into the flow has resulted in no rows vanishing for the daily loads for the past week. Unfortunately, I don't have time to really troubleshoot further, but I think that this demonstrates that there is indeed a serious bug here. I still suspect it has to do with the union transform. I am quite scared for anyone else's shop that has decided to standardize ETL to this tool, as we have. As developers, we have only time to test our code, not testing that the native tool functionality is behaving as expected. In addition ,to have to monitor on a regular basis that it is performing properly, is not acceptable.
Hoping this problem magically went away with SP1....
JH
I have a parent package that uses Execute Package Task against three child packages. They are all setup to use the same configuration file, and the packages are all located in the same folder. Purpose of the configuation is to use the same connection strings for each child package.
I removed the path from the config. file specification to facilitate deployment. During debugging, the child packages cannot find the config file when they are run via the parent unless the full path is specified. If run standalone, a child package can find the config. file without the path. As I said, all dtsx filaes and the config file is in the same folder.
Also, the password-removal issue is killing me in development. Everytime I touch the config. file, it removes the password from the connection string. I have "protection level" set to "don't save sensitive" - I'd appreciate help with this too.
thanks
Mark Challen wrote:
I have a parent package that uses Execute Package Task against three child packages. They are all setup to use the same configuration file, and the packages are all located in the same folder. Purpose of the configuation is to use the same connection strings for each child package.
I removed the path from the config. file specification to facilitate deployment. During debugging, the child packages cannot find the config file when they are run via the parent unless the full path is specified. If run standalone, a child package can find the config. file without the path. As I said, all dtsx filaes and the config file is in the same folder.
Also, the password-removal issue is killing me in development. Everytime I touch the config. file, it removes the password from the connection string. I have "protection level" set to "don't save sensitive" - I'd appreciate help with this too.
thanks
When you have it set to "don't save sensitive" that's what's supposed to happen. It's not saving sensitive information.|||As far as your paths go, you have to use absolute paths. So, you might be better off using an environment variable or something to store the path to the config file.
Relative paths have been requested: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=253232|||
When you have it set to "don't save sensitive" that's what's supposed to happen. It's not saving sensitive information.
Do you happen to know which setting will allow the saving of a password so I can work on the packages in development without having to reset the password everytime I touch the configuration? I can understand removing the password when building the solution for deployment, having it auto-magically disappear while staying in the designer it makes no sense.
|||http://msdn2.microsoft.com/en-us/library/ms141747.aspx|||I removed the path from the config. file specification to facilitate deployment. During debugging, the child packages cannot find the config file when they are run via the parent unless the full path is specified. If run standalone, a child package can find the config. file without the path. As I said, all dtsx filaes and the config file is in the same folder.
In my current project I am using that technique; removing the path of the config file from the package configuration and it has worked fine in both ways when called from the aprent package or standalone....
Config file has to be in same folder than the apckages though.
|||Thanks for the link Phil. Doesn't answer the question, and I'm getting increasingly frustrated by the lack of control with SSIS. Not to get off on a rant, but I personally would rather be responsible for setting security, not having MS dictate that I encrypt everything.
Anyhow, I have found a workaround - once the config. file is established on the parent I set it's "Read-only" property to true in the file system. A low-tech approach, but it seems to work.
The issue of the requirement for an absolute path for the config. file is going to be a showstopper for me (no way am I setting an environment variable; I don't see the thinking behind that - how do I establish the variable in the first place?). I will probably execute my child packages independently from the command line from my own custom wrapper code, passing in my connection strings on the command line. Too bad, I wanted to use the Configuration feature and I spent a lot of time trying to make it work. This seems to be endemic to SSIS (like checkpointing) - a cool feature at first glance, but try to use it and you hit a brick wall. </Rant>
In my current project I am using that technique; removing the path of the config file from the package configuration and it has worked fine in both ways when called from the aprent package or standalone....
Config file has to be in same folder than the apckages though.
Are you sure the package is successfully locating the config file and overriding the connection strings, or is it defaulting back to your original connection manager settings in the child?
|||The issue of the requirement for an absolute path for the config. file is going to be a showstopper for me (no way am I setting an environment variable; I don't see the thinking behind that - how do I establish the variable in the first place?).
You set up the environment variable by way of the system properties in Windows. Once it's set up, you simply reference it thereby making your configurations portable. This is a good way, in my opinion to handle changes across environments that have different configurations (file paths, etc...)
For me, I use SQL Server based configurations and environment variables. I've pretty much got everything setup so that I never have to make changes to a package when promoting it from development into QA and finally into production. Even though file paths may change (commonly C: drive to D: drive, perhaps), the packages continue to work with little interaction. This helps given that we can't promote to production -- another group does that. (Thank you Sarbanes-Oxley!)|||
Are you sure the package is successfully locating the config file and overriding the connection strings, or is it defaulting back to your original connection manager settings in the child?
I am 100% sure that it works; actually it is very easy to see if it is not; each time a configuration does not take place; a warning is generated and can be seen in the progress tab of BIDS or in the logging table/file when logging is enable. I rather prefer using indirect configurations; but I have gotten a client where Env variable are not an option....
When loading a table in a data flow from a text file that contains non-null float values, I am seeing erratic and inconsistent results. I am presently using SQL Server Destination in a data flow.
- With low volumnes of data, less that 50,000 rows, no problems
- But with higher volumnes, 2,000,000+ rows, I get different results depending on how I run the package. If I run is directly (right-click and click on Execute), I get the expected result.
But if I use SQL Server Agent to run the package, half of the values are lost and nulls are loaded instead. I have inspected the into text file and there are few rows with null for the column.
Any help would be appreciated!
Greg
>>>- With low volumnes of data, less that 50,000 rows, no problems
Did you have success using both BIDS and SQL Agent for the 50K load?
>>>- But with higher volumnes, 2,000,000+ rows, I get different results depending on how I run the package.
While using SQL Agent did you chose the Command subsystem and use dtexec or the SSIS subsystem?
I have a DTS PACKAGE IN SQL 2000 where I need to use vbscript to loop thru the files. I have the package detecting the directory - and the file, BUT the file itself has an interval number from 1- 48 set within it. So in one day I am getting 48 different files.
I can not delete previous files or move them - so I need to - each day - loop thru the day for each interval.
Any thoughts on how I can do this?
Interval 1 = 12:00 AM
Interval 2 = 12:30 AM
Interval 3 = 1:00 AM
etc. etc.
Thanks!
M
I am not clear exactly what you're after. If you want to translate intervals into respective times for the 48 intervals, you can do like so:
Code Snippet
--create dummy #digits table
select top 48 i=identity(int,1,1)
into #digits
from syscolumns
--the query
select i, right(convert(varchar,dateadd(minute,(i-1)*30,0),100),7) [time]
from #digits
--drop #digits
drop table #digits
Well not exactly taking the intervals and transposing them into a time. Each interval means it is a certain time frame (a 30 min time frame within the 24 hours - giving me a total of 48 intervals in a day).
The file that I get - I get 48 times a day. So I have to loop thru the 48 files in activex for the dts. And I am a bit stuck on that part.
|||Moved to the SSIS forum, since you are looking for a solution with no T-SQL code. This is more likely to have someone to answer it here. Thanks
|||Are you trying to do this in DTS or SSIS? If it's DTS, try this newsgroup - microsoft.public.sqlserver.dts.|||Forums site is screwy - finally was able to get in and edit/reply... this is for DTS in SQL 2000...I have a DTS PACKAGE IN SQL 2000 where I need to use vbscript to loop thru the files. I have the package detecting the directory - and the file, BUT the file itself has an interval number from 1- 48 set within it. So in one day I am getting 48 different files.
I can not delete previous files or move them - so I need to - each day - loop thru the day for each interval.
Any thoughts on how I can do this?
Interval 1 = 12:00 AM
Interval 2 = 12:30 AM
Interval 3 = 1:00 AM
etc. etc.
Thanks!
M
I am not clear exactly what you're after. If you want to translate intervals into respective times for the 48 intervals, you can do like so:
Code Snippet
--create dummy #digits table
select top 48 i=identity(int,1,1)
into #digits
from syscolumns
--the query
select i, right(convert(varchar,dateadd(minute,(i-1)*30,0),100),7) [time]
from #digits
--drop #digits
drop table #digits
Well not exactly taking the intervals and transposing them into a time. Each interval means it is a certain time frame (a 30 min time frame within the 24 hours - giving me a total of 48 intervals in a day).
The file that I get - I get 48 times a day. So I have to loop thru the 48 files in activex for the dts. And I am a bit stuck on that part.
|||Moved to the SSIS forum, since you are looking for a solution with no T-SQL code. This is more likely to have someone to answer it here. Thanks
|||Are you trying to do this in DTS or SSIS? If it's DTS, try this newsgroup - microsoft.public.sqlserver.dts.|||Forums site is screwy - finally was able to get in and edit/reply... this is for DTS in SQL 2000...Header1
Header2
Header3
Data Record
Data Record
... etc. ...
Data Record
Trailer1
Trailer2
I want to loop through each data record. I want to take a column value (string) from each row, and call a stored procedure passing in that value. The s/p will return another string which I want to use to update the original column value from the flat file. If I can't update the original column value I'm happy to add a derived column.
Once I've done this "lookup" for all records, I want to re-create the flat file again with the new column values.
I don't need a detailed solution ... just some pointers on how to go about it.
Greg.
On the surface it looks like you should be able to do this (i.e. execute a sproc) using the OLE DB Command transform however I don't think this will work because you want the return value from the sproc put into the pipeline - which you cannot do with the OLE DB Command transform.
Can you not use the LOOKUP transform to get the new values? If all the sproc does is lookup the input value in a mapping table then LOOKUP transform will do the job.
If not - this will be a little harder. But let's eliminate (or otherwise) the option of using the LOOKUP transform first.
-Jamie
|||The sproc parses the incoming string to attempt to extract two distinct values from it. Each of these values are primary key columns in separate tables, both of which are needed to obtain the a pk value from another table, so it's not a straighfoward lookup.
I guess I could do the lookup using custom SQL in the Lookup transform? and re-write the parsing of the input column as a Script Component (I've already done it in C# so should be simple to port to VB.NET)
Apologies as it's a bit of a muddle, but just trying to get my head around a decent solution.
(I've already done this in BizTalk but am looking to use SSIS instead - it's a pity SSIS support for the Web Service tasks is so poor otherwise it would be simpler).
Thanks,
Greg.
|||Did you think about using of staging table? In this scenario you would load the file to a temp (staging table) 1 to 1. Then apply a stored procedure to a temp table that wouold transfer only needed rows to the production table...|||
Custom SQL in the LOOKUP is a good way to go. In fact, best practice states that this is the best thing to do because if you simply select a table you will be selecting unused data into your LOOKUP cache - VERY BAD.
You could probably do the parsing in a derived column component - no need to resort to code (unless you really want to of course). If you need to derive some values in order to do the LOOKUP then there is nothing at all wrong with doing this in the pipeline - that's what the pipeline is for.
Anyway...anything that enables you to use the LOOKUP is definately the way to go otherwise you'll have to hack around it and that isn't nice.
Incidentally, I agree that the Web Services Task is basically useless. I am currently working on a project where we are consuming web services in SSIS left right and centre and we haven't used the Web Services Task once - it is all done in script. Donald Farmer's book contains an example of how to do this.
-Jamie
|||I'd like to avoid a staging table here as these files really only "pass" the database on their way somewhere else, doing a lookup on the way.
Thanks Jamie. I'll have a look at derived columns. Last question for the moment is,
If I have a Script Component and define an InputColumn with Usage Type of Read/Write ... how do I write to it? Do I need to do it in script using Row.ColumnName = "whatever value"?
Greg.
|||
Jamie,
you can get values from a sproc back to the pipeline using OUTPUT parameters... That shouldn't be any problem...
However I aggree that using lookups (perhaps even some lookups behing eachother) will be the better solution...
|||Thomas Pagel wrote:
Jamie,
you can get values from a sproc back to the pipeline using OUTPUT parameters... That shouldn't be any problem...
However I aggree that using lookups (perhaps even some lookups behing eachother) will be the better solution...
Ah, thanks Thomas. Care to post a demo on your blog? :)
|||
If I have a Script Component and define an InputColumn with Usage Type of Read/Write ... how do I write to it? Do I need to do it in script using Row.ColumnName = "whatever value"?
Yeah, basically. There's loads of demo code about for doing this. Here's some from my own page - you'll find stuff on Kirk's blog, Ash's blog, SQLIS.com amongst others as well I'm sure:
http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx
http://blogs.conchango.com/jamiethomson/archive/2005/04/09/1265.aspx
-Jamie
|||
Jamie,
here you are: http://sqljunkies.com/WebLog/tpagel/archive/2006/01/03/17744.aspx
|||
BTW, in SP1, the Web Services task gets dynamic. :)
K
|||Thanks for all the help so far folks. In my data flow, I've got my flat file coming in. This goes into a script component which takes one column (string), parses it (complicated) and sets two variables in the PostExecute method. This then goes into a lookup.How can I use those variables in the custom SQL window? I know I can build a custom SQL query in a variable as an expression and use that in the OLE DB Command, but this doesn't seem possible in the Lookup
FWIW, my lookup is a three table join along the lines of
SELECT FieldB FROM A, B, C
WHERE A.FieldA = Variable1
AND C.FieldC = Variable2
AND A.PK = B.FK1
AND C.PK = B.FK2
Greg.
|||
Greg,
I guess you didn't understand the concept of the Lookup, yet. The lookup takes a query (when the package starts) and caches the result of that query (by default, you can change that...). In your dataflow you define one field (or many) from the pipeline to be matched with the same number of fields in the cache. If there is a match you'll get the other fields of the found record in return.
So you don't have to pass the variables of each record to the lookup. What you have to do is that you store the results of the script not in variables but in new fields in the pipeline. Then you have to match these fields with the result of the complete query in the lookup (so the lookup doesn't have one record but all possible records but you get only the fields of the matching record back to the pipeline).
HTH
|||I was just in the process of realising that when you posted Thomas. It's much clearer now.SSIS is a pretty steep learning curve in terms of understanding what it's capable of but it's a pretty cool tool to use.
Plenty more questions to come.
Greg.
I have the following problem.
I wrote a batch file that runs a sql script on SQLServer 2000. This
script must be executed on several databases. This works fine.
The problem is that in my database is a table that holds a databases i
have to update with this script. What i want is run a query with osql
in my batchfile to retrieve this these records so I can loop through
them an run the script for those databases.
I managed to execute the query that return the records and write them
to a textfile.
What i want is store the results in some kind of a resultset so i can
loop through them in my batchfile.
Anyone have the solution
Thanks in advance
Patrickpatrick (pveechoud@.xs4all.nl) writes:
> I have the following problem.
> I wrote a batch file that runs a sql script on SQLServer 2000. This
> script must be executed on several databases. This works fine.
> The problem is that in my database is a table that holds a databases i
> have to update with this script. What i want is run a query with osql
> in my batchfile to retrieve this these records so I can loop through
> them an run the script for those databases.
> I managed to execute the query that return the records and write them
> to a textfile.
> What i want is store the results in some kind of a resultset so i can
> loop through them in my batchfile.
A simple-minded approach is to write the query so that it generates
a complete OSQL command and you write that to the file. Then you
execute the file.
But it would probably better to write this in some script language
like VBscript or Perl from which you can submit queries.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql
Hello All,
I have a problem and i wish i can get the answers or advices to solve it.
i have like 20 excel files and in each file there is 1 sheet (Planning) . What i need to do is to loop on the on the 20 files (actually this is the easy part and i already done it) the hard part is while looping i need to open each excel file and loop on the 256 columns in it and extract the data from it to a SQL server Database.
Any help will be alot appreciated.
Does each sheet have the same number of columns and the same column names?
|||Hi, thank you for ur reply, yes the sheets are all the same, same columns name and same column data and same number of columns.
|||Perfect, then inside your loop you'll need a data flow task. Set the source excel connection manager through a variable and send the data to your destination.
|||thats if i wanted to extract the whole excel sheet, wht i want to do is looping on the columns(256), loop on each column and extract it where i want.
|||I don't quite understand. There is an interface to map your source columns to your destination columns.
|||i found it thank you alot 
I have an XML object (sent as a string, received as an XML datatype) that's in a Stored Procedure.
Each row in the XML file will have 1 value from it inserted into one of three tables. The tables are depended upon the other value from the XML file.
The XML File is layed out as:
<Values>
<value>
<value>1</value>
<key>My_Field</key>
</value>
<value>
<value>3523.2</value>
<key>My_other_Field</key>
</value>
</Values
I basically need to go through it row by row, find out what table I need to insert the value into using thekeyfield.
Any help with this would rock. I'm using SQL 2005.
Tried a bit more but couldn't figure it out.
Basically I need to do something like this (logic):
Foreach row in @.xml
switch(SELECT FieldType FROM fields WHERE Name = @.Xml[key])
case :TextBox:
INSERT INTO TextFields(Value) VALUES (@.Xml[Value])
break;
case: listBox
INSERT INTO ListFields(Values) VALUES)@.Xml[Value])
... etc.
I have to check each row in the XML object sent in to see what table I need to insert the corresponding value to. I know this is possible, i'm just not sure how.
|||
Hi,
Actually you can use XMLDocument to get the data from XML file and store it into your database. See the sample below.
XmlDocument xd =new XmlDocument();xd.Load(inputurl);/// inputurl is the path of the xml file. XmlNodeList xnl = xd.GetElementsByTagName("value");for (int i = 0; i < xnl.Count; i++) { XmlNode xn0 = xnl.Item(i).ChildNodes[1]; XmlNode xn1 = xnl.Item(i).ChildNodes[0];string xn0_str = xn0.InnerText;string xn1_str = xn1.InnerText;// xn0_str stands for the table name // xn1_str stands for the value you want to insert. }Hope that helps. Thanks.|||
That would work, however that means I will be hitting the Database with many calls. I'd rather execute 1 call to the DataBase. Sometimes that XML file might have 100 rows in it. I don't want to hit the DB 100 times. I would rather send everything in at once, and then have the Database do all the work. It's less expensive that way.
Thanks :) I found a way to work it though using temp tables :)
|||Hi,
Well, I know your needs. The main idea is to loop the xml file first, and take down each node's value in an array. And then build your insert statement dynamically by looping the array value.
Thanks.
Hi,
Hopefully someone can assist me with what appears to be a simple issue - and hopefully I am just looking in the wrong location - because I am almost going nuts trying to work this out!!
I am wanting to pickup a file from a given folder, do some transformations on the data within the file, and then dump each row within the original file as a seperate txt file into a new folder.
I have managed to get it working - well all except having each row as a seperate txt file. Currently all the rows are outputed into the same txt file. argh
As it stands I have a For Each Loop Container, within this i have a Data Flow Container; which in itself, just has the source, some derived columns, and then an output.
How can I get this to pull each row from the source and put it as a seperate txt file. If someone can just nudge me in the right direction it would be much appreciated.
Thanks in advance
Troy
This might help:
Getting a value out of a file to use it in our package
(http://blogs.conchango.com/jamiethomson/archive/2005/06/15/SSIS_3A00_-Getting-a-value-out-of-a-file-to-use-it-in-our-package.aspx)
Its not exactly what you want to do but it uses the same concept (i.e. loop over the contents of a file and proces each record individually)
-Jamie
|||Here's an example of using a script destination to output multiple destination files:
http://agilebi.com/cs/blogs/jwelch/archive/2007/06/03/multi-file-output-destination-script-component.aspx
and here's one that doesn't use script.
http://agilebi.com/cs/blogs/jwelch/archive/2007/06/03/multi-file-outputs-part-2.aspx
For your purposes, I would probably use the script based one, as it will be easier to modify.
|||Hi Troy
I am also facing the same scenario.
Pls look into my form http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2085862&SiteID=17
But till date i didn't succeded.
Please update. Thanks
|||Hi John/Jamie - thanks for the links; they were both pretty similar (considering the non-script one), so had a play and seemed to make sense.
Have run into another error now - when trying to make the file connection properties variable (error = External table is not in the expected format); but will post that in another post.
Antony - suggest you have a look at the posts above; they may be of assistance.
Thanks again
Troy
Hi,
Hopefully someone can assist me with what appears to be a simple issue - and hopefully I am just looking in the wrong location - because I am almost going nuts trying to work this out!!
I am wanting to pickup a file from a given folder, do some transformations on the data within the file, and then dump each row within the original file as a seperate txt file into a new folder.
I have managed to get it working - well all except having each row as a seperate txt file. Currently all the rows are outputed into the same txt file. argh
As it stands I have a For Each Loop Container, within this i have a Data Flow Container; which in itself, just has the source, some derived columns, and then an output.
How can I get this to pull each row from the source and put it as a seperate txt file. If someone can just nudge me in the right direction it would be much appreciated.
Thanks in advance
Troy
This might help:
Getting a value out of a file to use it in our package
(http://blogs.conchango.com/jamiethomson/archive/2005/06/15/SSIS_3A00_-Getting-a-value-out-of-a-file-to-use-it-in-our-package.aspx)
Its not exactly what you want to do but it uses the same concept (i.e. loop over the contents of a file and proces each record individually)
-Jamie
|||Here's an example of using a script destination to output multiple destination files:
http://agilebi.com/cs/blogs/jwelch/archive/2007/06/03/multi-file-output-destination-script-component.aspx
and here's one that doesn't use script.
http://agilebi.com/cs/blogs/jwelch/archive/2007/06/03/multi-file-outputs-part-2.aspx
For your purposes, I would probably use the script based one, as it will be easier to modify.
|||Hi Troy
I am also facing the same scenario.
Pls look into my form http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2085862&SiteID=17
But till date i didn't succeded.
Please update. Thanks
|||Hi John/Jamie - thanks for the links; they were both pretty similar (considering the non-script one), so had a play and seemed to make sense.
Have run into another error now - when trying to make the file connection properties variable (error = External table is not in the expected format); but will post that in another post.
Antony - suggest you have a look at the posts above; they may be of assistance.
Thanks again
Troy
I'm downloading zip'd files and would like to loop through each file that was downloaded. I'd also like to unizip each file and append all of them to one file. I have a dos batch that is fairly simple and would like to emulate it using ssis. Here is what the dos batch file looks like.
DATE /T >%TEMP%\D.txt
FOR /F "usebackq tokens=2,3,4 delims=/, " %%i IN ("%TEMP%\D.txt") DO SET fname=TAMF_162%%i%%j%%k-%%k.zip
ECHO xxx>zzzzz
ECHO xxxxx>>zzzzz
ECHO BINARY>>zzzzz
ECHO GET %fname%>>zzzzzz
ECHO QUIT>>zzzzz
FTP -s:zzzzzzz ftp.aaaaa.com
PKUNZIP -o -xxxxxxx downloadedfile_1~1.ZIP
DEL TAMF_1~1.ZIP
DEL zzzzzzzz
EXIT
I would just run my DOS batch file as an external process if the batch file does what you want it to do.|||We could do that but then each time that this needs to be edited its two places instead of one. I'd rather convert it over to ssis.
Big problem with SSIS now. My looping container does not have the option to loop over files. Seems as if others are having the same problem http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=416437&SiteID=1&mode=1
|||Not sure why it would make for something that has to be edited in two places. You would only have the code that does the unzip in one bat file so if things needed to be edited you would only have to edit the bat file. Must be something I'm missing in what you are doing.|||Swells - I just want to convert the dos file to SSIS and schedule it in one package and I'd like to learn how to unzip and parse the files. As you can see from my other posts my ssis is somehow broken in that I do not have the option to loop through files. Seems like this may be an issue with the SP1.|||Regarding missing ForEach File enumerator - I've just had a chance to investigate a machine that has this problem. It turned out this is another symptom of the problem discussed in this KB:
http://support.microsoft.com/kb/913817|||
I'm coming up with FlashProp Class. How do I keep the dos window open. I'm not a console app programmer, sorry. And how do we know that this is the problem?
thanks
|||I'm coming up with FlashProp Class. How do I keep the dos window open. I'm not a console app programmer, sorry. And how do we know that this is the problem?
thanks
The application does not fix the registry settings, it just lists CLSIDs with incorrect permissions. Once they are identified, open RegEdit and find these CLSIDs under HKEY_LOCAL_MACHINE\Software\Classes\CLSID, and grant everyone read permissions to these keys. You may then close the application, and reboot. The problem should go away.
|||Thats seems to not of worked for me. Is there a fix for this? I cannot have my developers run regedit and fix keys to get this working. Can MS provide a tool that will run and fix the keys if this is the problem? Anyone else running into this problem?
thanks Michael and keep me updated. We'd really like to use this on one of our projects here at the University.
|||The problem is caused by third-party installation (the cases I've seen were caused by Lexmark driver and Flash, there might be other applications incorrectly setting the permissions), so we don't know what keys these applications create and what are the appropriate permissions for them. So it would be risky to write an automated program to fix them. The program in KB article identifies the problematic keys, but you currently need to fix them in RegEdit.I have a stored procedure that processes an individual file from a
directory and archives it in a subdirectory.Now, the problem is, when i
execute it , it will only process one file. What i want to do is to check
to see if there are any files in the folder, and if there are , process
them all, and once done, go to the next part in a DTS package, if there are
no files, simply go to the next part in the DTS package. I tried an activex
script that would get the filecount in the folder, and if there were more
than 0 files in the folder, then DTS-sUCCESS and on "success" workflow , it
would run the stored procedure, and thus it woould process one file, then
"on completion" the workflow connected it back to the activeX script(thus
looping), which would count the files again. Now if there were 0 files, it
would report DTS_FAILIURE, and I had it set up ,"on failiure" to go to the
next step in the package, but it wouldn't run.
Someone mind showing me a ray of light?What you can do is create another SP (Parent SP)which is a wrapper on
the current SP .
Read the files one by one in the main SP and call your SP .
In DTS flow replace the current SP with Parent SP.
Srinivas
Alex wrote:
Quote:
Originally Posted by
Hello,
>
I have a stored procedure that processes an individual file from a
directory and archives it in a subdirectory.Now, the problem is, when i
execute it , it will only process one file. What i want to do is to check
to see if there are any files in the folder, and if there are , process
them all, and once done, go to the next part in a DTS package, if there are
no files, simply go to the next part in the DTS package. I tried an activex
script that would get the filecount in the folder, and if there were more
than 0 files in the folder, then DTS-sUCCESS and on "success" workflow , it
would run the stored procedure, and thus it woould process one file, then
"on completion" the workflow connected it back to the activeX script(thus
looping), which would count the files again. Now if there were 0 files, it
would report DTS_FAILIURE, and I had it set up ,"on failiure" to go to the
next step in the package, but it wouldn't run.
>
Someone mind showing me a ray of light?
Hello,
I currently have a For Each File container that loops through all files from a specific directory. The files have a naming convention that looks like this;
CDNSC.CDNSC.SC00015.01012007
The last segment of the file name is the date of the data in the file (mmddyyyy). The create date for these files is always a day later than indicated in the file name.
What I would like to do is to have more control over the 'range' of files that are looped through by using the date portion of the file name to define what group of files should be looped through. Ideally, I would like to have a 'StartDate' variable and an 'EndDate' variable that I could define at run time for the package, and the package would loop through all of the files where the date portion of the file name fell between 'StartDate' and 'EndDate'.
Any ideas on this?
Thank you for your help!
cdun2
One way is to move the files to an archive directory when you're done with them. That way you only pick up the files you need to process. Just throwing that idea out there.|||Thanks for your response. One thing that I have to be prepared for is that I may have to 'reload' data. The date range for any reloading could be anything.|||Well, you could (and perhaps should?) stage the data from the flat files in a staging table. Then, once in the staging table you can keep n number of loads around, all marked by either the load date, or a unique load number. After n number of loads, you can delete the old data. Each row in the staging table would also contain the source file name.Or, you could simply load a table with the load date and a filename. That way, you've got a table that tells you which files to grab for a given load.
Load File
1 xyz.txt
1 fhs.txt
2 jfb.txt
The above still assumes that you "archive" the files into a subdirectory or somewhere.|||The data from the flat files will wind up in one ore more sql server tables. One idea I was considering was to stored the file names in a table, and maybe parse the date portion of the file name into a column. Then somehow use that as a lookup table to determine which files would be processed.|||
The data from the flat files will wind up in one ore more sql server tables. One idea I was considering was to stored the file names in a table, and maybe parse the date portion of the file name into a column. Then somehow use that as a lookup table to determine which files would be processed.
Right, but the point is that your first data flow simply loads the data from the files AS IS into a table. Then in the data flow that you use today, you point them to the staging table instead of the flat file(s).|||Oh, I see. The data that I need to process is a subset, and will be aggregated for reporting. The concern that I have is that somewhere down the road, the reporting requirements may change, and the users may need another column from the data. I'm thinking that if that happens, I'll need to add the additional column to the subset table, and reload everything.|||
Oh, I see. The data that I need to process is a subset, and will be aggregated for reporting. The concern that I have is that somewhere down the road, the reporting requirements may change, and the users may need another column from the data. I'm thinking that if that happens, I'll need to add the additional column to the subset table, and reload everything.
You're going to have to add the column in the flat file source anyway, would be one argument.
Regardless, this is just one way to tackle this problem. There are other ways I'm sure.|||
Actually, the destination table will have just a subset of the columns that are found in the flat files. If a need is found for an addional column of data, it will already be in the flat files. I'll just need to add it to the destination table.
What kind of component could I use in a DataFlow Task to read filenames?
Thanks again.
|||Actually, the destination table will have just a subset of the columns that are found in the flat files. If a need is found for an addional column of data, it will already be in the flat files. I'll just need to add it to the destination table.
What kind of component could I use in a DataFlow Task to read filenames?
Thanks again.
If this is the case, then the staging table example still works. In your CONTROL flow, you'd use a foreach loop to spin through the files. Each filename can be put in a variable, which can be used in a data flow inside the foreach loop. Then, using a derived column in the data flow, you'd have access to the variable that contains the current filename.|||
Hello,I currently have a For Each File container that loops through all files from a specific directory. The files have a naming convention that looks like this;
CDNSC.CDNSC.SC00015.01012007
The last segment of the file name is the date of the data in the file (mmddyyyy). The create date for these files is always a day later than indicated in the file name.
What I would like to do is to have more control over the 'range' of files that are looped through by using the date portion of the file name to define what group of files should be looped through. Ideally, I would like to have a 'StartDate' variable and an 'EndDate' variable that I could define at run time for the package, and the package would loop through all of the files where the date portion of the file name fell between 'StartDate' and 'EndDate'.
Any ideas on this?
Thank you for your help!
cdun2
Cdun2,
This is an alternative approach to the issue of processing only the files that are within a start/end date range. It uses Expression and precedence constraints:
http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html
BTW,
Just to save extra work down the road; i would recomment to import all the columns of the files from the begining.
|||Thanks again for your input. I'll dig a little deeper into this next week. As far as bringing in all of the columns of data, the attempt is to aggregate so that we can meet a reporting need, and reduce the number of rows of data that we need to store. Its a resource/performance issue for us. I can't bring in any more than I need.|||I can't bring in any more than I need.
But you can. To Rafael's point, you can at least have ALL of the flat file columns mapped in the connection manager. You don't have to do anything with them in the data flow, but at least they are there if you need to pick them up later; you won't have to redefine the connection manager.|||
cdun2 wrote: I can't bring in any more than I need.
But you can. To Rafael's point, you can at least have ALL of the flat file columns mapped in the connection manager. You don't have to do anything with them in the data flow, but at least they are there if you need to pick them up later; you won't have to redefine the connection manager.
Yes, that makes sense. Thanks again.
|||I took a look at the example here; http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html
Thank you for providing this example. I am having a difficult time making the bridge between the precendent constraint, and the Data Flow Task where the content of the qualifying flat files are inserted into the SQL Server table.