Data Source passwords in Config Files

Anybody find a sound approach to being able to store DataSource connection information in a configuration and be able to access a Data Source that requires a password for login?

Storing it as a configuraiton in the DB is not an option, as you require the pw to access the DB that would store it.

Can security be applied to an environment variable? Registry?

Anyone successfully use the registry to store configuration values?I'm not sure what your criterion for sound is, so I don't know if this meets it :), but you can store credentials (username & password) in a configuration file, and then make an environment variable which gives its path, and then configure a Package Configuration in the SSIS package to use the environment variable.
Of course you have to configure the environment variable for each machine (and for each user, if two users on one machine will use different credentials -- therefore, different configuration files).
Then, the credentials being now in one or more files, secure access to these files via traditional NTFS ACLs (file pemissions).
|||

I guess by sound, I meant, any way that works... when i store the pw in the config the .xml file represents it like the following:

- <Configuration ConfiguredType="Property" Path="\Package.Connections [MDB].Properties[Password]" ValueType="String">

<ConfiguredValue />

</Configuration>

This results in the error:
Error 1 Validation error. Data Flow Task: OLE DB Source [1]: The AcquireConnection method call to the connection manager "MDB" failed with error code 0xC0202009. Package2.dtsx 0 0

Have you been able to do this?.. am I missing something? I am also storing the connection string, initial catalog, and username in the config file.

|||When the file is generated by Visual Studio, it will not write the pw. I was able to get it to work by modifying the file directly.. duh.|||I'm assuming that you're not on a domain and so cannot use integrated authentication?
K|||Yes, Joe, We've been using the solution I explained above, with different configurations for different developers on the same machine (so they can use different login names, so the processes are distinguishable in SQL Server Enterprise Manager).
But, we've been using SQL Server logons (not integrated), because we're using a SQL Server 2000 backend. What you quoted sounds like you're using an mdb backend, which we've not tried.
To set up a new configuration with a new set of credentials, I first add the environment variable for that user, pointing it to where the config file is going to be. Then I put a config file there -- I take an existing one, and using a text editor, change the logon name & password (stored in plaintext in the file).
The very first time we did this, SSIS generated the config file for us; since then, we've been hand editing it for each machine and/or credential combination.

|||Here is an edited copy of our config file. Everything beginning with Zz is something that I edited before posting (along with the nifty DEADBEEF guid).
Also I added the carriage returns and whitespaces -- the raw config file is a whitespaceless mess.
******************

<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="ZzMachineName\ZzUserName" GeneratedFromPackageName="Zz Our Nifty Package" GeneratedFromPackageID="{DEADBEEF-AAAA-BBBB-CCCC-DEADBEEFBEEF}" GeneratedDate="7/26/2005 4:25:01 PM"/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_CONN_NAME].Properties[Password]" ValueType="String">
<ConfiguredValue>ZzBigSecretPassword</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_CONN_NAME].Properties[UserName]" ValueType="String">
<ConfiguredValue>ZzLogonNameForThisDeveloperHere</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_NOTHER_CONN].Properties[Password]" ValueType="String">
<ConfiguredValue>ZzBigSecretPassword</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_NOTHER_CONN].Properties[UserName]" ValueType="String">
<ConfiguredValue>ZzLogonNameForThisDeveloperHere</ConfiguredValue>
</Configuration>
</DTSConfiguration>

Data source file name has a timestamp

I have 2 years worth of data that are stored in individual .dbf files for each day. Is there a way to 1 quickly import all of these tables into one and 2. move the timestamp from the file name to a date column?

Any help would be greatly appreciated

The For Each Loop has a file enumertor. You end up with the filename in a variable. You can then use this variable in two ways -

1 Use it in an expression for a connection, so that the connection string is changed to pick up the new file. You would use this connection for your source component, in a Data Flow within the loop itself.

2 Add a derived column transform in your data flow, and use the variable again, grab the date part and pass out in a new column.

Data restore help

The database server crashed and i am trying to restore database. I have all the mdf, ldf and bak files. I tried to attach, import and restore but nothing is working. It just gives this error,

TITLE: Microsoft SQL Server Management Studio
An Exception occurred while executing a Transact-SQL statement or batch (Microsoft.Sqlserver.ConnectionInfo)

Additional Information:
The media family on device 'D:\Databases\inv.bak' is incorrectly formed. SQL Server cannont process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

Anyone, please help.

Thanks

Subasis

Do you get the same error when you try to attach the database files (mdf & ldf)?|||The first thing I would do is run a RESTORE HEADERONLY and a RESTORE VERIFYONLY combination to check whether you have a valid backup|||

Hi TimDBA,

When i tried to attach file, it gave error 5171 (The given file is not a primary database file). As far as I know, it is the primary database file that was restored from data directory of MS SQL server directory.

Hope to get your response. Thanks

Subasis

|||

Hi bass_player

I tried as you've said using restore headeronly but it gave "The file is incorrectly formed and SQL server cannot process this media family'.

Thanks

Subasis

Data recovery problems

I restored a backup and latest trasnsactions files for one
of my db (hrMasterData), now I have a messages that says
HrMasterData (loading).
It is a long time I am waiting for, what is happening, and
what can I do?If you restored all your transaction log files already, maybe you just
forgot to use the WITH RECOVERY in your last tlog. Use the following command
to solve this problem:
RESTORE DATABASE dbname WITH RECOVERY
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"FREDY COREA" <anonymous@.discussions.microsoft.com> wrote in message
news:105bb01c40df9$d8bd9f20$a401280a@.phx
.gbl...
> I restored a backup and latest trasnsactions files for one
> of my db (hrMasterData), now I have a messages that says
> HrMasterData (loading).
> It is a long time I am waiting for, what is happening, and
> what can I do?
>

Data migration into SQL server 2005 from xml files

Friends

Any one of you please provide step by step info for migrating sql server 2005 from xml files.

cheers :)

satish

Try to provide as much detail as you can so that someone can help you.

Data Mapping Database Report

I am working on a data mapping project (i.e. mapping tables from a SQL server database to VSAM files on a mainframe). I know that's bass-ackwards .. but that's what "the man" wants. And what "the man" wants he gets :)

What i am trying to do is to produce one report for each table (in the SQL server database). Each report would contain one column per field-name. Along with the field-name, i would like to also include the 10 most popular values of that field, a count for each, and one key per value (to refer-back).

If anyone is following along, do you have any suggestions ?? I'm assuming Crystal MUST have this functionality, but i'm banging my head against a wall trying to figure out even where to begin.

Thanks in advance !!The first part is easy - I presume you can use the report expert to get the raw data from the SQL table and show that as columns in the report. Am I right?

If you can do that, then you want to produce an individual count for each field - do you mean a count of unique elements for each field, and then the topN for each field.

Here's some sample data, by way of clarification:
ID Name City Salary
1 John A 20000
2 Fred A 20000
3 Mary A 50000
4 Kim B 35000
5 **** C 33000

A = 3 90000
B = 1 35000
C = 1 33000

If that is a very simple example, then you need to create a group for each field that you want to summarise, in this case City, and then create Count field in groop footer for City, and Sum field for Salary.

There's a start. TopN side of things should be able to be achieved using the TopN/Group Sort expert.

If you havew lots of fields, you'll have lots of groups.......my example groups on only City, so is simple, but your could end up looking rather ugly, I think!

Dave|||Hey Dave,

You're on the right track, but what i wanna do is "flip" the report around a little. Let's say i have a Customer table ... with SSN, Name, Addr.

I want my report to contain ONE ROW for each field-value ... not one column. My columns would be the Field-Name, Most Popular Occurence, Refer-Back key, and Count.

I would want my report to look like ...

Field Most popular value 1 Key (to refer back) Count (for that value)

SSN 111111111 111111111 1
222222222 222222222 1
333333333 333333333 1
...
123459699 123459699 1

Name John Smith 243050630 24
Dave Smith 294848372 22
Bob Jones 249858382 21
...
Tom Brown 385838375 11

Addr 123 Main St 948473859 6
123 Main Dr 938584894 6
222 Main St 983583959 4
...
123 Broadway 958347583 2

Thanks for your input !

I know that's ugly. My fields are lined up in my message text, but all of the spacing is ignored when the message is saved.

I want one row per field-name, and then 10 Values, refer-backs, and counts for each field name.

Data Integration Tools Recommendation

Hi all

In our company we do a lot of data imports from various files/data stores.

I was wondering what are the good Data Integration software out there which can provide "industrial-strength" to this process. I heard one of them is Informatica (www.informatica.com) but that seems more for Data Warehousing.

Our DB is SQL Server. Maybe we should change that too?

Comments/Suggestions, anyone?

Much Appreciated,
ManikWhy can't you use DTS from SQL server?|||Hi Satya

We use DTS right now. However, the problem is that DTS is not giving us very good performance.

What we really need is a software that can help us model the data import process and then run the import and provide logging etc.

DTS works ok for now but we have cases where we have to update several thousand records against a > 1000 row Excel file and we have to do this very often (and also on demand). This is very slow.

Also, the various imports are getting bigger and more cumbersome to manage, hence the need for an integrated tool that can create, manage and execute these imports in a systematic manner.

Regards
Manik|||Not aware of such tools but you can search under SQL Performance (http://www.sql-server-performance.com/) website for any recommendations and information.

HTH

Data integration challenge. Will SSIS help?

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

One possible option is creating Stored Procedures using the managed code ( thats created by mapforce using c#). And using SSIS to execute those SPs and exact data.

Performance you can achive using SSIS will be much better than that with Biztalk.|||

JGP wrote:

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

This is script source component or custom source adapter territory. I recommend Don Farmer's book "Rational Guide To Scripting" which contains a step-by-step guide to building a script source component.

-Jamie|||Anyone have any real-world experiences doing something like this? Any links or samples you can share?

Thanks,
JGP|||Are you using biztalk 2002 or 2004

Given the complexity of EDI files and the parsing of these, I don't think SSIS is the mechanism.
You would need a custom adapter, and it would be complicated.
EDI generally feeds a business process which is what Biztalk fits.

My view of SSIS is that is the process of flows of data not of transactions of data which is how I view EDI.

There is of course a blur between the 2 and so it depends on what your process is, and what you are doing with the EDI files.

Personally I would stick with biztalk. Not sure if there is anything new/improved in 2006 that might help|||Its actually one of our clients...they're currently using Biztalk 2000.

Couldn't the complexity be reduced by using Mapforce 2005 which now supports EDI? What are your thoughts on using that with SSIS?

The process is basically doing ETL of EDI flat files into relational schema with little bit of validation and code cleansing. In Biztalk they use vbscript functiods which are a real hit on performance...especially since the EDI flat files are fairly large.

Thanks,
JGP|||I am sorry but I have no experience of Mapforce. I would suggest looking at Biztalk 2004 or even 2006, Biztalk 2000 was awful at handling large files.

I always rate using the same toolset, Biztalk, SSIS all use Visual Studio. It might be over kill but using the Biztalk as the mapper. However looking at mapforce it might also be suitable, not sure what the interface is to it.

It does depend on what the vbscript is doing, it might be easier doing it in Biztalk. You also need to be outputing data that can be consumed by SSIS, a flat file or an XML file with data at one level i.e. not hiearchical.|||Did you have any luck with this? I'm trying to use SSIS for the same thing, now, and could sure use an example.

Data integration challenge. Will SSIS help?

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

One possible option is creating Stored Procedures using the managed code ( thats created by mapforce using c#). And using SSIS to execute those SPs and exact data.

Performance you can achive using SSIS will be much better than that with Biztalk.|||

JGP wrote:

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

This is script source component or custom source adapter territory. I recommend Don Farmer's book "Rational Guide To Scripting" which contains a step-by-step guide to building a script source component.

-Jamie|||Anyone have any real-world experiences doing something like this? Any links or samples you can share?

Thanks,
JGP|||Are you using biztalk 2002 or 2004

Given the complexity of EDI files and the parsing of these, I don't think SSIS is the mechanism.
You would need a custom adapter, and it would be complicated.
EDI generally feeds a business process which is what Biztalk fits.

My view of SSIS is that is the process of flows of data not of transactions of data which is how I view EDI.

There is of course a blur between the 2 and so it depends on what your process is, and what you are doing with the EDI files.

Personally I would stick with biztalk. Not sure if there is anything new/improved in 2006 that might help|||Its actually one of our clients...they're currently using Biztalk 2000.

Couldn't the complexity be reduced by using Mapforce 2005 which now supports EDI? What are your thoughts on using that with SSIS?

The process is basically doing ETL of EDI flat files into relational schema with little bit of validation and code cleansing. In Biztalk they use vbscript functiods which are a real hit on performance...especially since the EDI flat files are fairly large.

Thanks,
JGP|||I am sorry but I have no experience of Mapforce. I would suggest looking at Biztalk 2004 or even 2006, Biztalk 2000 was awful at handling large files.

I always rate using the same toolset, Biztalk, SSIS all use Visual Studio. It might be over kill but using the Biztalk as the mapper. However looking at mapforce it might also be suitable, not sure what the interface is to it.

It does depend on what the vbscript is doing, it might be easier doing it in Biztalk. You also need to be outputing data that can be consumed by SSIS, a flat file or an XML file with data at one level i.e. not hiearchical.|||Did you have any luck with this? I'm trying to use SSIS for the same thing, now, and could sure use an example.

Data integration challenge. Will SSIS help?

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

One possible option is creating Stored Procedures using the managed code ( thats created by mapforce using c#). And using SSIS to execute those SPs and exact data.

Performance you can achive using SSIS will be much better than that with Biztalk.|||

JGP wrote:

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

This is script source component or custom source adapter territory. I recommend Don Farmer's book "Rational Guide To Scripting" which contains a step-by-step guide to building a script source component.

-Jamie|||Anyone have any real-world experiences doing something like this? Any links or samples you can share?

Thanks,
JGP|||Are you using biztalk 2002 or 2004

Given the complexity of EDI files and the parsing of these, I don't think SSIS is the mechanism.
You would need a custom adapter, and it would be complicated.
EDI generally feeds a business process which is what Biztalk fits.

My view of SSIS is that is the process of flows of data not of transactions of data which is how I view EDI.

There is of course a blur between the 2 and so it depends on what your process is, and what you are doing with the EDI files.

Personally I would stick with biztalk. Not sure if there is anything new/improved in 2006 that might help|||Its actually one of our clients...they're currently using Biztalk 2000.

Couldn't the complexity be reduced by using Mapforce 2005 which now supports EDI? What are your thoughts on using that with SSIS?

The process is basically doing ETL of EDI flat files into relational schema with little bit of validation and code cleansing. In Biztalk they use vbscript functiods which are a real hit on performance...especially since the EDI flat files are fairly large.

Thanks,
JGP|||I am sorry but I have no experience of Mapforce. I would suggest looking at Biztalk 2004 or even 2006, Biztalk 2000 was awful at handling large files.

I always rate using the same toolset, Biztalk, SSIS all use Visual Studio. It might be over kill but using the Biztalk as the mapper. However looking at mapforce it might also be suitable, not sure what the interface is to it.

It does depend on what the vbscript is doing, it might be easier doing it in Biztalk. You also need to be outputing data that can be consumed by SSIS, a flat file or an XML file with data at one level i.e. not hiearchical.|||Did you have any luck with this? I'm trying to use SSIS for the same thing, now, and could sure use an example.

Data import

I am importing data from text files nightly and I am wondering if there is a way to have SQL recognize whether the text file has been updated before the job runs. The text files come over from our Unix box and every once in a while the transfer fails, so when this happens I don't want the SQL jobs to run because I just get duplicate data and not updated data. I don't know if this is possible, but I am just wondering. Thanks.Are you saying that if the process fails, the old files still exist. Do the filenames change - and what about date/time for the files ? Also, what would happen if you deleted or moved the files when you had successfully imported the data ? And what are you using now to import the data ?|||Yes, the files are overwritten nightly. I am using a DTS package to import from the textfile. I thought about just deleting the files after the import, then the jobs would just fail if no files were there, but I just wondered if there was a way for SQL to recognize the date on the file before running the job.|||You can do either - run the dts based on file existence or on file date/time. I would add a file existence step in your dts package and a file move/delete step after the import was successful.|||How would I word the existence step based on date/time? Thanks|||You can create an activex script task using the filesystemobject.|||Have you worked with the filesystemobject - if not I can show you an example.|||Just created it and it seems to be working, but I have it setup only to see if the file exists, if it doesn't then the package fails, which is what I want. Is there a way to have it check for file date and fail if it is not correct instead of just if it exists?|||You can use the File object and read the DateCreated property. You can create the File object using the GetFile method of the filesystemobject.